Dynamic Table References for Data Validation Lists

Roybzer

New Member
Joined
Apr 30, 2013
Messages
20
Office Version
  1. 365
Platform
  1. MacOS
I've been tinkering for a while, but just can't seem to get this to work as desired.

I have multiple tables in a workbook:
TblFuel
TblVehicles
TblPlant_Machinery
TblMaterials
TblLabour

I'm creating a quote page where the initial selection is for one of these item types(Fuel, Vehicle, Plant_Machinery, Materials, Labour), then there is a dropdown list in the next column which uses data validation to determine which table to draw the list information from. The list is always the 1st column in each of the tables, the tables are all formatted as tables(so structured references are an option). Just to add some additional complexity, I want the solution to also work should a user move a table within a sheet, just to avoid a possible breakage after this is handed over.

e.g. if 'Fuel' is selected in cell B6, C6 should populate with all of the items in the first column of TblFuel.

I had hoped to use something along the lines of '=INDIRECT("INDEX(Tbl" & B6 & ", 0, 1)"). This works as a formula on the worksheet, but not in data validation.

Any ideas would be greatly appreciated.
 

Attachments

  • E4D2DB35-3851-4397-B116-C75BBBA045EF.jpeg
    E4D2DB35-3851-4397-B116-C75BBBA045EF.jpeg
    100.9 KB · Views: 5

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows
You said:
e.g. if 'Fuel' is selected in cell B6, C6 should populate with all of the items in the first column of TblFuel.

So you want C6 populated with all the values in first column of TblFuel.

So C6 would have more then one value.

What would C6 look like in your example

Alpha
Bravo
Charlie

Or AlphaBravoCharlie

I'm surprised you want a large amount of values all in one cell
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,590
Office Version
  1. 365
Platform
  1. Windows
=INDIRECT("INDEX(Tbl" & B6 & ", 0, 1)"). This works as a formula on the worksheet, but not in data validation.
It doesn't work for me in either circumstance. Did you mean this?

=INDEX(INDIRECT("Tbl"&B6), 0, 1)

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

1616472309545.png
 
Solution

Roybzer

New Member
Joined
Apr 30, 2013
Messages
20
Office Version
  1. 365
Platform
  1. MacOS
You said:
e.g. if 'Fuel' is selected in cell B6, C6 should populate with all of the items in the first column of TblFuel.

So you want C6 populated with all the values in first column of TblFuel.

So C6 would have more then one value.

What would C6 look like in your example

Alpha
Bravo
Charlie

Or AlphaBravoCharlie

I'm surprised you want a large amount of values all in one cell
For the example of Fuel, it would be:

Diesel
Red Diesel
Unleaded

Once the user has selected the Item Type and then specific item(B6 & C6), then enter the Unit Value and quantity, the unit cost is found using a switch statement in F6.

I want to use lists to avoid spelling errors, and therefore mismatches, especially with some of the equipment which has names like "6X4 Tractor Unit Man-TGX 26.540 6X4 BBS XM", so a list allows for these selections.

Thanks
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,597
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

For the example of Fuel, it would be:

Diesel
Red Diesel
Unleaded

Once the user has selected the Item Type and then specific item(B6 & C6), then enter the Unit Value and quantity, the unit cost is found using a switch statement in F6.

I want to use lists to avoid spelling errors, and therefore mismatches, especially with some of the equipment which has names like "6X4 Tractor Unit Man-TGX 26.540 6X4 BBS XM", so a list allows for these selections.

Thanks
Now that Peter is helping you I will move on to help someone else. I'm sure he will be able to help you.
 

Roybzer

New Member
Joined
Apr 30, 2013
Messages
20
Office Version
  1. 365
Platform
  1. MacOS
It doesn't work for me in either circumstance. Did you mean this?

=INDEX(INDIRECT("Tbl"&B6), 0, 1)

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

View attachment 35052
Hi Peter,

Yes, that is exactly it!!!

Thank you for saving the other half of the hair on my head that I hadn't yet torn out. The reshuffle worked a charm.


Thanks
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,590
Office Version
  1. 365
Platform
  1. Windows
Hi Peter,

Yes, that is exactly it!!!

Thank you for saving the other half of the hair on my head that I hadn't yet torn out. The reshuffle worked a charm.
You're welcome. Thanks for the follow-up. :)
.. and for updating your profile details. (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

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