Formula printing in cell

D

Doehead

Hi all,
I am trying to copy a complete row of data to another sheet when it meets
the criteria:

Source data is in sheet: Master, cols A to AU,
data from row 2 down, with key col X & criteria value: Closed

On new sheet: Closed
Placed in A2:
=IF(Master!X2="","",IF(OR(Master!X2=â€Closedâ€),ROW(),""))
Left A1 blank for header row

In B2:
=IF(ROWS($1:1)>COUNT($A:$A),"",INDEX(x!A:A,SMALL($A:$A,ROWS($1:1))))

When I type these formulas in the function box they also show in the
respective cell.
Why? Any help appreciated!
 
B

Bernard Liengme

Are the cells formatted as Text?
Not sure about that OR with only one condition?
The formulas work for me
 
B

Bernie Deitrick

Doehead,

1) You may have formatted the cells for text prior to entering the forumula:
Change the formatting to General, select the cell, press F2, and the press
Enter to re-enter the formulas.

2) Tools / Options... "View" Tab, un-check "Formulas"

HTH,
Bernie
MS Excel MVP
 
D

Doehead

Thank you both, that was the problem. So recreated for a fresh start and then
copied down formulas for 10 rows. Why do I see #NUM! in the cells on the
Closed sheet where there should be no data?. Hope this makes sense. Doehead
 
T

T. Valko

The formulas work but I would tweak them.

No need for the OR function:

=IF(Master!X2="","",IF(Master!X2="Closed",ROW(),""))

=IF(ROWS(B$2:B2)>COUNT($A:$A),"",INDEX(x!A:A,SMALL($A:$A,ROWS(B$2:B2))))

In the INDEX function, are you sure you're referencing the correct sheet:
x!A:A ?
 
D

Doehead

Excellent, thanks T works beautifully with no #NUM! showing in blank cells.
Have a great day!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top