VLookup...returning zeros when cells are blank, how to have them appear blank.

[treym]

I have a file I'm using at work to identify skill levels. The file has been designed using a drop down box (that selects a skill set). A Vlookup then returns the text information in the appropriate cells. If the cells have nothing in them, then the vlookup returns a '0'. How can I adjust the code so it returns just a blank cell instead?

 

[treym]

Thank-you!

 

[T. Valko]

Thank-you!

You're welcome!

 

[treym]

New Update....and more help needed....
Instead of removing the zeros, which leaves the fields blank, I'd like to have the page adjust to the number of rows (columns are static) that are filled.
Essentially, one of my pages of skills may have 12 rows and all filled. Then another choice may only have 5 rows, but all the blank cells are sill shown based on the max shown of 12. Is there a way to have the view adjust when you pick the dropdown skill?

 

[T. Valko]

New Update....and more help needed....
Instead of removing the zeros, which leaves the fields blank, I'd like to have the page adjust to the number of rows (columns are static) that are filled.
Essentially, one of my pages of skills may have 12 rows and all filled. Then another choice may only have 5 rows, but all the blank cells are sill shown based on the max shown of 12. Is there a way to have the view adjust when you pick the dropdown skill?

Sorry, I don't understand!

 

[treym]

My table is 5 columns by a maximum of 12 rows. The 5 columns are static and never change. However, depend which 'skillset' you choose it may have 3 rows up to 12. If you're familiar with what a grading 'rubric' looks like, this will give you an idea. We've hidden the 20+ skill worksheets and have them accessed by a 'dropdown'. Choose the skillset from the dropdown and the sheet shows up. Change the skillset and a new one replaces the previous. The maximum of 12 rows always show up, even if the sheet you select only has 3 active rows. The other rows used to show '0' (my previous question), but we corrected them to just be 'blank'. What I'd like to do is have the page adjust to eliminate the blank rows altogether when a sheet with less rows is present. Is this possible? That way when we print it sizes to the sheet and does have all the blank rows.

 

[T. Valko]

My table is 5 columns by a maximum of 12 rows. The 5 columns are static and never change. However, depend which 'skillset' you choose it may have 3 rows up to 12. If you're familiar with what a grading 'rubric' looks like, this will give you an idea. We've hidden the 20+ skill worksheets and have them accessed by a 'dropdown'. Choose the skillset from the dropdown and the sheet shows up. Change the skillset and a new one replaces the previous. The maximum of 12 rows always show up, even if the sheet you select only has 3 active rows. The other rows used to show '0' (my previous question), but we corrected them to just be 'blank'. What I'd like to do is have the page adjust to eliminate the blank rows altogether when a sheet with less rows is present. Is this possible? That way when we print it sizes to the sheet and does have all the blank rows.

I think I understand what you want to do.

It sounds like what you'd need to do is hide the unused rows of each sheet.

For example, if row 20 is the last row with data on it then you'd want to hide all rows from row 21 to the last row.

To do that:

• Select row 21 by clicking in the row header
• Use the key combination of Shift+CTRL+down arrow to select all the rows from row 21 to the last row
• Right click>Hide

 

[treym]

Hiding is fine, but can it be done automatically when the sheet is selected?

 

[T. Valko]

Hiding is fine, but can it be done automatically when the sheet is selected?

Yes, but that would take VBA programming to accomplish. I'm not much of a programmer so I can't be of much help with that.

I suggest you start a new thread for that.

 

[treym]

Thanks, I'll start a new thread.