Referencing Variables
Referencing Variables
(OP)
I have a dropdown list, containing 'C' or a 'W'.
Based on the value selected, for example, 'W', I want to populate another dropdown list with the variable 'W_Lst' that creates a dropdown list of all the W Sections. If 'C' is selected, the dropdown list will use the variable 'C_Lst' for all the C Sections.
I want to select a particular W Section from the dropdown list and using the Vlookup function to select the properties from another array called 'W_Tbl' which has all the properties for all the W Sectins. If 'C' is selected the array is called 'C_Tbl' and these properties are accessed.
Alternately, is it possible to create another variable equal to, say, Temp_Tbl and set this equal to either C_Tbl or W_Tbl and use the Temp_Tbl value in the VLookup function.
Clear as mud... Is it possible to do this in Excel?
Dik
Based on the value selected, for example, 'W', I want to populate another dropdown list with the variable 'W_Lst' that creates a dropdown list of all the W Sections. If 'C' is selected, the dropdown list will use the variable 'C_Lst' for all the C Sections.
I want to select a particular W Section from the dropdown list and using the Vlookup function to select the properties from another array called 'W_Tbl' which has all the properties for all the W Sectins. If 'C' is selected the array is called 'C_Tbl' and these properties are accessed.
Alternately, is it possible to create another variable equal to, say, Temp_Tbl and set this equal to either C_Tbl or W_Tbl and use the Temp_Tbl value in the VLookup function.
Clear as mud... Is it possible to do this in Excel?
Dik





RE: Referencing Variables
Dik
RE: Referencing Variables
It would be to your benefit if you were to upload a copy of your workbook containing these lists and ranges. Otherwise, it's a guessing game. There may also be a better way of accomplishing your ultimate results.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Referencing Variables
You can use a combination of named ranges and the Indirect function to do what you describe. Have a look at Indirect and let us know how it goes. You can use Indirect inside VLookup (or Index or Offset), or you can use it to return the whole table, as described in your second option.
If you do the latter, you will also need to know about using array functions. Look at the help on these things, and if anything isn't clear, please ask.
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Referencing Variables
You will need some vba to clear the second drop down to avoid incompatible combinations via a selection change event on the first selection cell.
RE: Referencing Variables
Dik
RE: Referencing Variables
=VLOOKUP(C60,INDIRECT(C58&"_Tbl"),2)
...where C58 is your W/C/H Data > Validation Drop Down.
Just an additional general comment or two:
I'd make the W/C/H sheet tables Structured Tables. Of course, the pretty headings you have in two rows would all need to be in ONE ROW, like any proper table ought to be structured. The column numbers probably need to go too. But the rewards from the features of Structured Tables are many and rich in reward once you know how to use them. Insert > Tables > Table.
I'd also make ALL the referenced cells on your Calculations sheet, Named Ranges. Your formulas will be much more meaningful and easier for understandability and maintainability.
When I get a chance, I'll modify your workbook and upload for you in accordance with these suggested modifications.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Referencing Variables
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Referencing Variables
The original program was written about a decade ago and I should update it for a metric database; it's a pain converting metric data to imperial. Use LibreOffice for my home machines, 2 running Linux and the others (4 + 2 laptops) using Win 7 and 8.1. Too many computers. I have disabled the 'features' of Win10 and do not want it. Other than some formatting features LibreOffice works really well. Again, thanks very much.
Dik
RE: Referencing Variables
Returns a reference given a text string.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Referencing Variables
Dependent dropdown lists
Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
RE: Referencing Variables
Dik
RE: Referencing Variables
I did not get any error using your uploaded workbook toggling between W & C.
The only #REF error I got was for H, which had not yet been defined in your workbook.
Make sure that all four VLOOKUP formulas in your sheet get properly modified.
If you have modified your workbook in some way, you might consider uploading that copy so we can take a look.
Skip,
for a NUance!
RE: Referencing Variables
RE: Referencing Variables
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Referencing Variables
Two new sentences <G>
Thanks, Dik
RE: Referencing Variables
So where is Section Type? Can't find it searching for 'type'
Just to cover all the bases, when I also change Member Type, I get no error. I see several cells immediately recalculate.
Hmmmmmmm????
Is this #REF simply a result of a gross delay in the recalculation of the sheet???
Skip,
for a NUance!
RE: Referencing Variables
Dik
RE: Referencing Variables
Perhaps you could upload your current workbook and I'll take a look.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Referencing Variables
Please determine if this workbook behaves with the #REF as your described.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Referencing Variables
Also, if you are wanting to see only the sections available for the Member Type Selected in cell C58, change the Data Validation in C60 to the following:
=INDIRECT(Member_Type&"_Lst")
RE: Referencing Variables
...will only work properly, if C58 is Named Member_Type, which I named using Formulas > Defined Names > Create from selection > Create names from values in the: Left Column having previously selected B58:C58.
dik may have an unusually slow processing machine.
Skip,
for a NUance!
RE: Referencing Variables
I left my usb at the office so, I'll post it tomorrow. My office machine is extremely slow, about a Core -4 <G> and runs on a network (everything is routed through a city, half a country away). My desktop at home is at the other end of the scale with a ASUS Maximus VII Impact MB, water cooled i7 4790 4G processor, 16G RAM, 512 M.2 SSD on PCIe and a 980 GTX video card.
I use MS Office at work and LibreOffice at home...
Dik
RE: Referencing Variables
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Referencing Variables
Dik
RE: Referencing Variables
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Referencing Variables
Dik
RE: Referencing Variables
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Referencing Variables
Thanks, Dik