I'm using Excel 2007 on WinXP.
I have a sheet ("Summary") that has formulas pulling in a column of IDs from a pivot table on another sheet (i.e. =pivot!a1, =pivot!a2, etc). The length of the list of IDs in the pivot table changes each month. I use this list of ID numbers on the "Summary" sheet to perform vlookups/calculations on sets of data from other worksheets in the workbook.
I am trying to devise a way where I do not have to pull down or erase the formulas at the bottom of the list on the "Summary" sheet each month, and where that list will be sortable without displaying blank cells at the top of the list after sorting. I also have cell borders and one column has highlighting, so this formatting needs to be hidden/erased for blank cells as well.
I have tried setting all the formulas to show zero when the ID column is zero, setting the sheet to hide zero values, then using conditional formatting to get rid of the borders and highlighting, but it doesn't get around the sorting issue. When I sort by any column, it shows the blank columns at the top of the list.
I've only used the "if" formula to try to accomplish this. Have tried setting the blank cell using "", [nothing for the TRUE value], and 0. None of that gets around the sorting issue. I don't want to use VBA/macros to accomplish this. I want to stick to just formulas and conditional formatting.
Any suggestions? Thanks!
-Scott
I have a sheet ("Summary") that has formulas pulling in a column of IDs from a pivot table on another sheet (i.e. =pivot!a1, =pivot!a2, etc). The length of the list of IDs in the pivot table changes each month. I use this list of ID numbers on the "Summary" sheet to perform vlookups/calculations on sets of data from other worksheets in the workbook.
I am trying to devise a way where I do not have to pull down or erase the formulas at the bottom of the list on the "Summary" sheet each month, and where that list will be sortable without displaying blank cells at the top of the list after sorting. I also have cell borders and one column has highlighting, so this formatting needs to be hidden/erased for blank cells as well.
I have tried setting all the formulas to show zero when the ID column is zero, setting the sheet to hide zero values, then using conditional formatting to get rid of the borders and highlighting, but it doesn't get around the sorting issue. When I sort by any column, it shows the blank columns at the top of the list.
I've only used the "if" formula to try to accomplish this. Have tried setting the blank cell using "", [nothing for the TRUE value], and 0. None of that gets around the sorting issue. I don't want to use VBA/macros to accomplish this. I want to stick to just formulas and conditional formatting.
Any suggestions? Thanks!
-Scott