Hide Zero Row with VLOOKUP

[cmagnus]

Hello All,

I am trying to figure out a scenario specific way to hide rows with zero values, I have spent a bit of time trying to figure this out on my own but I have not had much luck in this scenario.

Below is the scenario that I am trying to achieve. "C9" is a dropdown of all of the rows in the first table (aa,bb,cc,dd,ee), using VLOOKUP I have set it up so that it displays the corresponding column value in the second table.

How do I set it up so that when there is a zero value in the second table the row disappears.

Thanks guys!

CM

 

[sheetspread]

the filter does this

 

[cmagnus]

what filter sorry

 

[sheetspread]

Highlight C10:C14, go to the data ribbon and press the filter button, uncheck the 0 box in the dropdown (along with any others you wish to hide).

 

[cmagnus]

Highlight C10:C14, go to the data ribbon and press the filter button, uncheck the 0 box in the dropdown (along with any others you wish to hide).

That does work, however, every time that I change the value in C9 I have to go back into the filter and reset it. Is there a way that this can be done automatically using VBA?

 

[Aladin Akyurek]

Care to post the VLOOKUP formula that you have?

 

[cmagnus]

Care to post the VLOOKUP formula that you have?

Sorry I accidentally deleted the original file so the boarders and colours are not the same, however, here is the VLOOKUP formula.
The column index number goes in ascending order from cells C10 to C14

 

[Aladin Akyurek]

Images are useless in the sense that you cannot read into Excel and manipulate...

Try the following:

=IF(VLOOKUP($C$9,$B$3:$G$7,2,0)=0,"",VLOOKUP($C$9,$B$3:$G$7,2,0))

 

[cmagnus]

Images are useless in the sense that you cannot read into Excel and manipulate...

Try the following:

=IF(VLOOKUP($C$9,$B$3:$G$7,2,0)=0,"",VLOOKUP($C$9,$B$3:$G$7,2,0))

That works to get rid of the zero values, however, I want the rows in the second table to automatically disappear if there is a zero value, and then when the value in the drop down menu (C9) is changed for the rows to automatically adjust (disappear or reappear).

Similar to what is done in this video.

https://www.youtube.com/watch?v=SoCz4LA8eKM