i have change some formulars along the lines of this:

=LOOKUP(G13,MLS!B$2:B$112,MLS!C$2:C$112)


change to:

=LOOKUP(G13,paynumber,staffnames)


only problem now is i end up with rows and rows of #N/A's until data is
input, anyway to hide these like you can with zero values?

Many thanks,
Steve

Would...

=IF((G13="")+(G13<INDEX(paynumber,1),"",LOOKUP(G13 ,paynumber,staffnames))

suffice?

"Steven" wrote in message
news:K85oc.7912$7S2.2711@newsfe1-win...
i have change some formulars along the lines of this:

=LOOKUP(G13,MLS!B$2:B$112,MLS!C$2:C$112)


change to:

=LOOKUP(G13,paynumber,staffnames)


only problem now is i end up with rows and rows of #N/A's until data is
input, anyway to hide these like you can with zero values?

Many thanks,
Steve

try
=if(isna(LOOKUP(G13,paynumber,staffnames),"",LOOKU P(G13,paynumber,staffnames
)


--
Don Guillett
SalesAid Software

"Steven" wrote in message
news:K85oc.7912$7S2.2711@newsfe1-win...
i have change some formulars along the lines of this:

=LOOKUP(G13,MLS!B$2:B$112,MLS!C$2:C$112)


change to:

=LOOKUP(G13,paynumber,staffnames)


only problem now is i end up with rows and rows of #N/A's until data is
input, anyway to hide these like you can with zero values?

Many thanks,
Steve

Another way would be to hide them using conditional formatting, by choosing
white for your font color when the cell's value equals #N/A.

Assuming that your range of cells begins in A5:

Select your range of cells
Format Conditional Formatting
Change "Cell Value Is" to "Formula Is"
=ISNA(A5)
Choose white as your font color
Click Ok

On 5/11/04 10:09 AM, in article K85oc.7912$7S2.2711@newsfe1-win, "Steven"
wrote:

i have change some formulars along the lines of this:

=LOOKUP(G13,MLS!B$2:B$112,MLS!C$2:C$112)


change to:

=LOOKUP(G13,paynumber,staffnames)


only problem now is i end up with rows and rows of #N/A's until data is
input, anyway to hide these like you can with zero values?

Many thanks,
Steve

so it would seem my attempt at simplifying a formula has ended up making it
even worse! thanks for the help guys, but i think it would be easier if i
used the original formula format, unless there's a simpler solution?

Many thanks,
Steve

"why does MS make such simple things so god damn complicated"

"Steven" wrote in message
news:K85oc.7912$7S2.2711@newsfe1-win...
i have change some formulars along the lines of this:

=LOOKUP(G13,MLS!B$2:B$112,MLS!C$2:C$112)


change to:

=LOOKUP(G13,paynumber,staffnames)


only problem now is i end up with rows and rows of #N/A's until data is
input, anyway to hide these like you can with zero values?

Many thanks,
Steve

Just use what I sent and you will be happy
=IF(ISNA(LOOKUP(N3,$H$1:$H$8,$I$1:$I$8)),"",LOOKUP (N3,$H$1:$H$8,$I$1:$I$8))

--
Don Guillett
SalesAid Software

"Steven" wrote in message
...
so it would seem my attempt at simplifying a formula has ended up making
it
even worse! thanks for the help guys, but i think it would be easier if i
used the original formula format, unless there's a simpler solution?

Many thanks,
Steve

"why does MS make such simple things so god damn complicated"

"Steven" wrote in message
news:K85oc.7912$7S2.2711@newsfe1-win...
i have change some formulars along the lines of this:

=LOOKUP(G13,MLS!B$2:B$112,MLS!C$2:C$112)


change to:

=LOOKUP(G13,paynumber,staffnames)


only problem now is i end up with rows and rows of #N/A's until data is
input, anyway to hide these like you can with zero values?

Many thanks,
Steve

"Steven" wrote in message
...
so it would seem my attempt at simplifying a formula has ended up making
it
even worse! thanks for the help guys, but i think it would be easier if i
used the original formula format, unless there's a simpler solution?

Many thanks,
Steve

"why does MS make such simple things so god damn complicated"

Actually, I should have brought up this before making a reply...

What does "until data is input" mean -- until something is in G13 or
something is in MLS!B$2:B$112 (paynumber), MLS!C$2:C$112 (staffnames)?

If the former and using LOOKUP is justified, then what I proposed will do
job.
All this depends of course MLS!B$2:C$112 is sorted in ascending order on
column B (and
covers all possible lookup values). If invoking LOOKUP is justified, there
is no need for:
=IF(ISNA(LOOKUP(),"",LOOKUP())


"Steven" wrote in message
news:K85oc.7912$7S2.2711@newsfe1-win...
i have change some formulars along the lines of this:

=LOOKUP(G13,MLS!B$2:B$112,MLS!C$2:C$112)


change to:

=LOOKUP(G13,paynumber,staffnames)


only problem now is i end up with rows and rows of #N/A's until data is
input, anyway to hide these like you can with zero values?

Many thanks,
Steve

until something is in G13, and the lists are sorted in ascending order

Steve

"Aladin Akyurek" wrote in message
...

"Steven" wrote in message
...
so it would seem my attempt at simplifying a formula has ended up making
it
even worse! thanks for the help guys, but i think it would be easier if
i
used the original formula format, unless there's a simpler solution?

Many thanks,
Steve

"why does MS make such simple things so god damn complicated"

Actually, I should have brought up this before making a reply...

What does "until data is input" mean -- until something is in G13 or
something is in MLS!B$2:B$112 (paynumber), MLS!C$2:C$112 (staffnames)?

If the former and using LOOKUP is justified, then what I proposed will do
job.
All this depends of course MLS!B$2:C$112 is sorted in ascending order on
column B (and
covers all possible lookup values). If invoking LOOKUP is justified, there
is no need for:
=IF(ISNA(LOOKUP(),"",LOOKUP())


"Steven" wrote in message
news:K85oc.7912$7S2.2711@newsfe1-win...
i have change some formulars along the lines of this:

=LOOKUP(G13,MLS!B$2:B$112,MLS!C$2:C$112)


change to:

=LOOKUP(G13,paynumber,staffnames)


only problem now is i end up with rows and rows of #N/A's until data
is
input, anyway to hide these like you can with zero values?

Many thanks,
Steve

=IF((G13="")+(G13<INDEX(paynumber,1)),"",LOOKUP(G1 3,paynumber,staffnames))

should work (I had a paren missing in the condition of IF in my initial
reply.).

"Steven" wrote in message
...
until something is in G13, and the lists are sorted in ascending order

Steve

"Aladin Akyurek" wrote in message
...

"Steven" wrote in message
...
so it would seem my attempt at simplifying a formula has ended up
making
it
even worse! thanks for the help guys, but i think it would be easier
if
i
used the original formula format, unless there's a simpler solution?

Many thanks,
Steve

"why does MS make such simple things so god damn complicated"

Actually, I should have brought up this before making a reply...

What does "until data is input" mean -- until something is in G13 or
something is in MLS!B$2:B$112 (paynumber), MLS!C$2:C$112 (staffnames)?

If the former and using LOOKUP is justified, then what I proposed will
do
job.
All this depends of course MLS!B$2:C$112 is sorted in ascending order on
column B (and
covers all possible lookup values). If invoking LOOKUP is justified,
there
is no need for:
=IF(ISNA(LOOKUP(),"",LOOKUP())


"Steven" wrote in message
news:K85oc.7912$7S2.2711@newsfe1-win...
i have change some formulars along the lines of this:

=LOOKUP(G13,MLS!B$2:B$112,MLS!C$2:C$112)


change to:

=LOOKUP(G13,paynumber,staffnames)


only problem now is i end up with rows and rows of #N/A's until data
is
input, anyway to hide these like you can with zero values?

Many thanks,
Steve

Ideally, there would be a formula which worked a little like:

=NNA(LOOKUP(N3,$H$1:$H$8,$I$1:$I$8), "")

Where if the lookup formula returned #N/A then NNA returns "", else return
result of lookup.

But there's nothing which works that way, so repeating the formula is the
only option here.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Don Guillett" wrote in message
...
Just use what I sent and you will be happy

=IF(ISNA(LOOKUP(N3,$H$1:$H$8,$I$1:$I$8)),"",LOOKUP (N3,$H$1:$H$8,$I$1:$I$8))

--
Don Guillett
SalesAid Software

"Steven" wrote in message
...
so it would seem my attempt at simplifying a formula has ended up making
it
even worse! thanks for the help guys, but i think it would be easier if
i
used the original formula format, unless there's a simpler solution?

Many thanks,
Steve

"why does MS make such simple things so god damn complicated"

"Steven" wrote in message
news:K85oc.7912$7S2.2711@newsfe1-win...
i have change some formulars along the lines of this:

=LOOKUP(G13,MLS!B$2:B$112,MLS!C$2:C$112)


change to:

=LOOKUP(G13,paynumber,staffnames)


only problem now is i end up with rows and rows of #N/A's until data
is
input, anyway to hide these like you can with zero values?

Many thanks,
Steve

It would require a bit of coding in a UDF but probably could be done.

--
Don Guillett
SalesAid Software

"Rob van Gelder" wrote in message
...
Ideally, there would be a formula which worked a little like:

=NNA(LOOKUP(N3,$H$1:$H$8,$I$1:$I$8), "")

Where if the lookup formula returned #N/A then NNA returns "", else return
result of lookup.

But there's nothing which works that way, so repeating the formula is the
only option here.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Don Guillett" wrote in message
...
Just use what I sent and you will be happy


=IF(ISNA(LOOKUP(N3,$H$1:$H$8,$I$1:$I$8)),"",LOOKUP (N3,$H$1:$H$8,$I$1:$I$8))

--
Don Guillett
SalesAid Software

"Steven" wrote in message
...
so it would seem my attempt at simplifying a formula has ended up
making
it
even worse! thanks for the help guys, but i think it would be easier
if
i
used the original formula format, unless there's a simpler solution?

Many thanks,
Steve

"why does MS make such simple things so god damn complicated"

"Steven" wrote in message
news:K85oc.7912$7S2.2711@newsfe1-win...
i have change some formulars along the lines of this:

=LOOKUP(G13,MLS!B$2:B$112,MLS!C$2:C$112)


change to:

=LOOKUP(G13,paynumber,staffnames)


only problem now is i end up with rows and rows of #N/A's until data
is
input, anyway to hide these like you can with zero values?

Many thanks,
Steve