Formula printing in cell
 

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!

Formula printing in cell
 

Are the cells formatted as Text?
Not sure about that OR with only one condition?
The formulas work for me
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"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!

Formula printing in cell
 

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!

Formula printing in cell
 

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!

Formula printing in cell
 

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!

Formula printing in cell
 

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!

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!