# Dynamic Named Ranges, blank cells and cell formatting.

#### 4rcanine

##### New Member
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!

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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:
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.

Still gives me blanks at the bottom of the list.

What is the value being returned for the blank formulas? is it "" ?

What is the value being returned for the blank formulas? is it "" ?

=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.

=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?

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.

@4rcanine

Data on a picture require retyping in order to be of effective use.

Attachments lists a few methods for posting sample exhibits which do not require retyping and the add-in https://app.box.com/s/soezox25h3w0q5s4rcyl
is also a method for posting an exhibit.<strike></strike>

Replies
11
Views
300
Replies
0
Views
208
Replies
1
Views
323
Replies
13
Views
520
Replies
4
Views
215

1,216,517
Messages
6,131,118
Members
449,622
Latest member
lejohnson93

### 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.

### Which adblocker are you using?

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

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