Referencing Variables 4

[dik]

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

 

[dik]

Further to my above query, is it possible to rename a variable in Excel?

Dik

 

[SkipVought]

Hi,

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!}

 

[IDS]

As Skip says, it would be good to see the actual application, but the answer to your question is yes.

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/

 

[Agent666]

Put an if statement in the data validation to direct to either list in the second drop down (I'm assume here you are using data validation for the lists). Use a named reference for the lists, this makes it easy and will enable you to locate the list on a separate sheet if required.
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.

 

[dik]

I can do it with nested 'if' statements... I've attached a copy of the spreadsheet... The W and C data are on sheets W and C respectively... It would be nice if I could use Temp_Lst and Temp_Tbl in the calculation sheet and by using the dropdown for section type set Temp_Lat := W_Lst and so on...

Dik

 

[SkipVought]

on your Calculations sheet, cell E60 (merged cell)
[tt]
=VLOOKUP(C60,[highlight #FCE94F]INDIRECT(C58&"_Tbl")[/highlight],2)
[/tt]
...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!}

 

[SkipVought]

Hmmmmm, you haven't upgraded to Excel 2007⁺?



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[dik]

Thanks Skip. I'll have to look into using the indirect function. I've never used it...

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

 

[SkipVought]

INDIRECT() function:

Returns a reference given a text string.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[IDS]

There is a good tutorial on using Indirect with dropdown boxes here:
Dependent dropdown lists

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[dik]

Thanks Doug... used the Indirect function for the variable in determining the list in the dropdown function and it worked well. I can also copy the beam information to below the upper beam and paste it and use it for designing the next beam using a W or a C section. Only problem is you get a ref error if you change from a W to a C or vice versa. The ref errors disappear once you select a new section.

Dik

 

[SkipVought]

#REF error?

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[dik]

It occurs with the transition from one section type to another and before the new section is selected. It's just temporary. All VLookups have been modified... and the sheet works well. Thanks, Dik

 

[SkipVought]

Don't know what the first two sentences mean.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[dik]

If you change section type, for the time between selecting a new section, a REF error occurs. This disappears after a new section is selected.

Two new sentences <G>

Thanks, Dik

 

[SkipVought]

I'm not sure where Section 'Type' is, but when I change Section (the DropDown in yellow) I get NO ERROR AT ALL. I see several cells immediately recalculate.

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,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[dik]

The section type is either the W or the C presently. The version of Excel I use at the office generates a REF until a member is selected if you change the section type. Don't know why...

Dik

 

[SkipVought]

Well in your worksheet, you identify the W/C/H selection cell as Member Type in C58 and Section in C60.

Perhaps you could upload your current workbook and I'll take a look.



Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

Okay, here's my copy of your original workbook.

Please determine if this workbook behaves with the #REF as your described.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}