Dynamic Table References for Data Validation Lists

[Roybzer]

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.

 

[My Aswer Is This]

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]

=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’)

 

[Roybzer]

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]

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]

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]

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.