G'day guys!
Having an issue with a book I'm working on at the moment.
I've got a long list of people in the first column and then subsequent vehicle 'qualifications' in each column.
I place their name into the first column for each 'qualification' they have, and then a second hidden column sorts those names so that there are no blank cells in-between each name.
On another worksheet, there's calculation that's run so that when I select 'Trailer', for example - it will give me a list of people who are qualified to drive with the trailer.
It all works fine as is, however there's two things I'd like to change. The first, is that under each qualification column, I need to use their name as opposed to just a Y/N entry, which would be neater. The second is that the bottom of the list contains all of the blank cells in the range. I can't seem to get these to filter out - I'm assuming because they contain cell formula and they aren't seen as empty.
Picture:
Formula used in the second 'filtered' column:
Any guidance would be greatly appreciated!
Having an issue with a book I'm working on at the moment.
I've got a long list of people in the first column and then subsequent vehicle 'qualifications' in each column.
I place their name into the first column for each 'qualification' they have, and then a second hidden column sorts those names so that there are no blank cells in-between each name.
On another worksheet, there's calculation that's run so that when I select 'Trailer', for example - it will give me a list of people who are qualified to drive with the trailer.
It all works fine as is, however there's two things I'd like to change. The first, is that under each qualification column, I need to use their name as opposed to just a Y/N entry, which would be neater. The second is that the bottom of the list contains all of the blank cells in the range. I can't seem to get these to filter out - I'm assuming because they contain cell formula and they aren't seen as empty.
Picture:
Formula used in the second 'filtered' column:
=IF(ROW()-ROW(TRAILER)+1>ROWS(TRAILERBLANKS)-COUNTBLANK(TRAILERBLANKS),"",INDIRECT(ADDRESS(SMALL((IF(TRAILERBLANKS <>"",ROW(TRAILERBLANKS),ROW()+ROWS(TRAILERBLANKS))),ROW()-ROW(TRAILER)+1),COLUMN(TRAILERBLANKS),4)))
Any guidance would be greatly appreciated!