=IF(Master!R7=2, ROW(),"")

  • Thread starter Thread starter Steved
  • Start date Start date
S

Steved

Hello from Steved

I have this formula in Col A:A =IF(Master!R6=2, ROW(),"")
Starting in Row 6 R6 is Row 6 and =2 is Sheet2

I have the below formula in col's B6 to Q30

=IF(ISNUMBER(SMALL(IF($B$6:$B$30="","",ROW
($B$6:$B$30)),ROW(6:6))),INDIRECT("Master!"&CHAR(COLUMN()
+64)&SMALL(IF($B$6:$B$30="","",ROW($B$6:$B$30)),ROW
(6:6))),"")

Ok I am in Sheet2 Row 6 have the above formula as a Array
{}, my understanding is that Row (6:6) is the lookup
this has got me stumped, yes the above formula is working
on every cell. I would like to know please, What do I
have to do for it to reconize Sheet2 Row 6 and be blank
in Rows 7 to 30.

Thankyou.
 
Not sure I get you, but if you want rows 7 - 30 to be blank, why add the
formula there?

What are you trying to do?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hello from Steved

You may or may not remember but you gave me this Formula.

It came with 3 formulas
1.=VALUE(LEFT(F6,1))
2.=IF(Master!R6=1, ROW(),"")
3.=IF(ISNUMBER(SMALL(IF($A$6:$A30="","",ROW
($A$6:$A30)),ROW(6:6))),INDIRECT("Master!"&CHAR(COLUMN()
+64)&SMALL(IF($A$6:$A30="","",ROW($A$6:$A30)),ROW
(6:6))),"")

Ok Bob What I am doing is first understand the formula
and what it does.

Ok In the master I have Rows of Information Starting in
A:6 to Q:30, and have 9 sheets to put information
in based upon the first numeric number in Col F of the
Master Sheet.

This is what I have acheived so far In The Master Sheet I
put =VALUE(LEFT(F6,1))in Col R6:R30, Then put =IF(Master!
R1=1, ROW(),"")in Sheet1 to Sheet9 in Col A6:A30

If I goto Sheet1 I see in Col A11 I see 11, Col A16 I see
16 and Col A21 is 21.
which means the first numeric number on the "Master F16"
is "1" another example is "Master F17" and "Master F18"
is the numeric 2 which I goes to sheet2 "Row A17:A18 is
the Row numbers 17 and 18 =.=IF(Master!R6=2, ROW(),"")

Sorry about the long winded way but to answer your
Question.
I want this formula =IF(ISNUMBER(SMALL(IF
($A$6:$A30="","",ROW($A$6:$A30)),ROW(6:6))),INDIRECT
("Master!"&CHAR(COLUMN()+64)&SMALL(IF($A$6:$A30="","",ROW
($A$6:$A30)),ROW(6:6))),"") to display the information
in Sheet1 based on Row 11,16,21 information and all other
rows to be blank.

So using the three formulas I need it to work for Row 11,
16 and 21 please.

Thanks for taking timeout on this issue of mine.
 
Hi SteveD!

This has been unresolved for a while now and has got my
curiosity in an uproar!

I tested the formula and it works for me. In A11, I
entered 11. In A16, I entered 16. and in A21,I entered 21.

I created a sheet named Master and made a table of random
numbers in A6:Q30.

The formula returns the correct values and returns "" when
it is supposed to.

Biff
 
Hello Biff from Steved

Biff let me understand, you are putting in 16 in Row A16
lets say in sheet5 and it is inputing values from row 16
of the Master Sheet.

The formula works in sheet5 but in every row, hence in my
case I know I only want it showing values in row 16 and
all other rows blank.

Dam I starting to feel kind of foolish as I believe it is
some I am doing and for the life of me I have been on
this for a quiet a few hours but have no results.

Cheers
 
Back
Top