Hide cells that equal a certain value

Diabeticus · Mar 12th, 2002, 10:20 AM

I am using a
=VLOOKUP(D2,Sheet1!A6:B12,2,FALSE)

to look up stuff in another worksheet. i only want to see those values that are not equal to "#N/A"

Is there a way to hide these cells? MAybe using conditional formatting?

Thanks!

Dave Hawley · Mar 12th, 2002, 10:25 AM

..Or use:

=IF(COUNTIF(A1:B16,D2)=0,"",VLOOKUP(D2,Sheet1!A6:B12,2,FALSE))

Diabeticus · Mar 12th, 2002, 10:46 AM

Hmmm.

I don't think that is exactly what I was looking for. thanks for the suggestion. It's still displaying "#N/A" when it doesn't find something that meets my criteria. These cells are what are showing up.

I basically want to hide the entire ROW if a value of #N/A is returned. I think it is proabably beyind the scope of excel, though I could be wrong.

Chris Davison · Mar 12th, 2002, 04:00 PM

how about auto filter on anything that does not equal "#N/A" ?

_________________
Hope this helps,
Chris

[ This Message was edited by: Chris Davison on 2002-03-12 15:16 ]

[ This Message was edited by: Chris Davison on 2002-03-12 15:16 ]

Aladin Akyurek · Mar 12th, 2002, 04:28 PM

Quote:

On 2002-03-12 09:46, Diabeticus wrote:
Hmmm.

I don't think that is exactly what I was looking for. thanks for the suggestion. It's still displaying "#N/A" when it doesn't find something that meets my criteria. These cells are what are showing up.

I basically want to hide the entire ROW if a value of #N/A is returned. I think it is proabably beyind the scope of excel, though I could be wrong.

Just curious about how you get #N/A when you use:

=IF(COUNTIF(A6:A12,D2),VLOOKUP(D2,Sheet1!A6:B12,2,0),"")

where 0 as the 4th arg of VLOOKUP is the same as FALSE?

[ This Message was edited by: Aladin Akyurek on 2002-03-12 15:29 ]

Diabeticus · Mar 13th, 2002, 02:39 PM

I don't know Aladin. It just did. Either way, it didn't help me with my original problem. I wish I knew why #N/A was showing up.

It is possible I implemented teh formual incorrectly. Who knows. Incidentally, I am looking into the AutoFilter possibility....looks like I may need to use a "Pivot Table" whatever that is. Reading HELP THOGUH! thanks.

Aladin Akyurek · Mar 13th, 2002, 02:45 PM

Quote:

On 2002-03-13 13:39, Diabeticus wrote:
I don't know Aladin. It just did. Either way, it didn't help me with my original problem. I wish I knew why #N/A was showing up.

It is possible I implemented teh formual incorrectly. Who knows. Incidentally, I am looking into the AutoFilter possibility....looks like I may need to use a "Pivot Table" whatever that is. Reading HELP THOGUH! thanks.

You still get #N/A's. Hmmm. Maybe the lookup table itself has #N/A's? I'd like too see that WB.

Aladin

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 12th, 2002, 10:20 AM	#1
Diabeticus New Member Join Date: Mar 2002 Posts: 7	I am using a =VLOOKUP(D2,Sheet1!A6:B12,2,FALSE) to look up stuff in another worksheet. i only want to see those values that are not equal to "#N/A" Is there a way to hide these cells? MAybe using conditional formatting? Thanks!

Mar 12th, 2002, 10:25 AM	#2
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	..Or use: =IF(COUNTIF(A1:B16,D2)=0,"",VLOOKUP(D2,Sheet1!A6:B12,2,FALSE))

Mar 12th, 2002, 10:46 AM	#3
Diabeticus New Member Join Date: Mar 2002 Posts: 7	Hmmm. I don't think that is exactly what I was looking for. thanks for the suggestion. It's still displaying "#N/A" when it doesn't find something that meets my criteria. These cells are what are showing up. I basically want to hide the entire ROW if a value of #N/A is returned. I think it is proabably beyind the scope of excel, though I could be wrong.

Mar 12th, 2002, 04:00 PM	#4
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	how about auto filter on anything that does not equal "#N/A" ? _________________ Hope this helps, Chris [ This Message was edited by: Chris Davison on 2002-03-12 15:16 ] [ This Message was edited by: Chris Davison on 2002-03-12 15:16 ]

Mar 13th, 2002, 02:39 PM	#6
Diabeticus New Member Join Date: Mar 2002 Posts: 7	I don't know Aladin. It just did. Either way, it didn't help me with my original problem. I wish I knew why #N/A was showing up. It is possible I implemented teh formual incorrectly. Who knows. Incidentally, I am looking into the AutoFilter possibility....looks like I may need to use a "Pivot Table" whatever that is. Reading HELP THOGUH! thanks.