Dynamic Named Ranges, blank cells and cell formatting.

4rcanine

New Member
Joined
Sep 2, 2014
Messages
25
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:
2ev9g7q.jpg


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!
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
You can setup a dynamic named range:
In the formula of the named range you will put: =OFFSET(X5,0,0,counta(X5:X99),1)
this will make the range change as the number of rows increases or decreases.

Then you use the named range as the source for the data validation list.

Let me know if you need more help with that!
 
Last edited:
Upvote 0
You can setup a dynamic named range:
In the formula of the named range you will put: =OFFSET(X5,0,0,counta(X5:X99),1)
this will make the range change as the number of rows increases or decreases.

Then you use the named range as the source for the data validation list.

Let me know if you need more help with that!

Hey Mate,

Tried that :) I assume he problem is those cells contain formula, so it's counting them. They appear blank in the list, but really they contain the formula in my first post.

=OFFSET(Data!AD10,0,0,COUNTA(Data!AD10:AD50),1)

Still gives me blanks at the bottom of the list.
 
Upvote 0
What is the value being returned for the blank formulas? is it "" ?
=OFFSET(Data!AD10,0,0, (COUNTA(Data!AD10:AD50) - COUNTIF(Data!AD10:AD50,"")) ,1)

=OFFSET(Data!$P$9,0,0, (COUNTA(Data!$P$9:$P$50) - COUNTIF(Data!$P$9:$P$50,"")),1)</SPAN>

Perfect! Awesome. Thanks heaps mate, greatly appreciated.
 
Upvote 0
=OFFSET(Data!$P$9,0,0, (COUNTA(Data!$P$9:$P$50) - COUNTIF(Data!$P$9:$P$50,"")),1)</SPAN>

Perfect! Awesome. Thanks heaps mate, greatly appreciated.

Oh no, another issue ><
On another worksheet I have a table used for creating vehicle convoys. When I input my first vehicle, let's say the Vehicle's type is Car.
Another column then uses data validation to reference a list called Car to give me a list of drivers qualified to drive that type of vehicle.

D6: Car
J6 Data Validation: =INDIRECT($D$6)
Named List Car: =OFFSET(Data!$R$9,0,0, (COUNTA(Data!$R$9:$R$50) - COUNTIF(Data!$R$9:$R$50,"")),1)

Which gives us our list of names, without any blanks in the list. If set up Data Validation in a new cell and point it to =Car, the list appears fine - but having it reference a cell called Car doesn't seem to work anymore.

Thoughts?
 
Upvote 0
Put the INDIRECT function inside of the data validation, and point it at the cell named car...
as opposed to putting the INDIRECT function and then referencing it with the data validation.

Example. Cell A1 = "CAR"
In the Data Validation, List, =INDIRECT(A1)

I think I understand your problem, so I hope that helps you.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top