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!
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

easy2understandexcel

Active Member
Joined
Dec 26, 2012
Messages
299
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:

4rcanine

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

easy2understandexcel

Active Member
Joined
Dec 26, 2012
Messages
299
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)
 

4rcanine

New Member
Joined
Sep 2, 2014
Messages
25

ADVERTISEMENT

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.
 

4rcanine

New Member
Joined
Sep 2, 2014
Messages
25
=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?
 

easy2understandexcel

Active Member
Joined
Dec 26, 2012
Messages
299
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,158
Messages
5,600,054
Members
414,357
Latest member
Gemma_R

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
Top