Advanced Filter

[mbsalim]

Hi Guys,

New to the site, really stuck with this excel filter problem.

here goes;

I have a spreadsheet which is essentially a table, table headers are in C7:I7 and the table cells are C8:I71.

C8:I71 are all filled with a vlookup formula that pulls in information from other sheets. When the data is blank it returns the value 0, which is fine but is there a way (I'm sure advanced filter is the solution) to have the table automatically make the cells with 0 disappear (the condition would be set on column C only as other columns need have zero within text where as C column does not)

So basically where C8:I71 >0 it will show, when they are = 0 then not-shown.

THANK YOU!!!

 

[mbsalim]

Just to add more information to this,

The Cells C7:CI71 are 'controlled' via another vlookup in Cell D3 which itself is a lookup based on the value entered in D2, so as the user changes the value in D2, D3 and the rest of the cells are changed accordingly. this sheet is reflecting different countries and information on them. (D2 user input of numbers which are assigned to a country)

 

[RoryA]

Do you mean you just want to hide the 0 values, or do you actually want to effectively shift the other cells up when a 0 value occurs?

If it's the former, you can just change the VLOOKUP to return "" instead of 0.

 

[mbsalim]

Do you mean you just want to hide the 0 values, or do you actually want to effectively shift the other cells up when a 0 value occurs?

If it's the former, you can just change the VLOOKUP to return "" instead of 0.

Rory A, Thank you for your reply,

But changing the vlookup to end with "", just displays #VALUE, i actually want excel to physically hide the cells, the data is set up so the first occurrence of 0 in the C column would be mean all further cells are 0 as there is no further data left.

thank you sooo much for your help!

 

[RoryA]

What's the formula? You shouldn't get a #VALUE error if you do it properly.

If you use a filter you'll have to refresh it every time your data changes.

 

[mbsalim]

the formula is entered as ;

=VLOOKUP(D$2,'sheetname1'!A:VF,9,0)

the last cell in column C is entered =VLOOKUP(D$2,'sheetname1'!A:VF,576,0)

 

[RoryA]

If that formula should return text, you can just use:
=VLOOKUP(D$2,'sheetname1'!A:VF,9,0)&""

If it might return numbers that you need for calculations, you can use:
=IF(VLOOKUP(D$2,'sheetname1'!A:VF,9,0)="","",VLOOKUP(D$2,'sheetname1'!A:VF,9,0))

If your column numbers are sequential as you go down column C, it would probably make sense to use a ROWS() formula rather than hardcoding the numbers.

 

[mbsalim]

RoryA,

the &"", gets rid of the 0 but not the cell, is there anyway for excel to hide the cell(Row) completely

 

[RoryA]

Only using filters and code. Why do you need to hide the rows if the cells appear blank anyway?

 

[mbsalim]

Presentation purposes, I.e. some countries rows go down to 71 others finish at 12, it would look much 'better' if the rows get hidden automatically once the country number is changed.

Thank you for your help!