|
how to hide #N/A ?
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 |
how to hide #N/A ?
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 |
how to hide #N/A ?
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 |
how to hide #N/A ?
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 |
how to hide #N/A ?
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 |
how to hide #N/A ?
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 |
how to hide #N/A ?
"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 |
how to hide #N/A ?
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 |
how to hide #N/A ?
=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 |
how to hide #N/A ?
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 |
| All times are GMT +1. The time now is 12:14 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com