Formula printing in cell
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
"Doehead" wrote in message
...
Excellent, thanks T works beautifully with no #NUM! showing in blank
cells.
Have a great day!!
"T. Valko" wrote:
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 ?
--
Biff
Microsoft Excel MVP
"Doehead" wrote in message
...
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
"Bernie Deitrick" wrote:
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
"Doehead" wrote in message
...
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!
|