INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

RE: Referencing Variables

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

Dik

RE: Referencing Variables

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Referencing Variables

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/

RE: Referencing Variables

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.

RE: Referencing Variables

(OP)
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

RE: Referencing Variables

on your Calculations sheet, cell E60 (merged cell)

=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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Referencing Variables

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Referencing Variables

(OP)
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

RE: Referencing Variables

INDIRECT() function:

Returns a reference given a text string.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Referencing Variables

(OP)
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

RE: Referencing Variables

#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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Referencing Variables

(OP)
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

RE: Referencing Variables

Don't know what the first two sentences mean.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Referencing Variables

(OP)
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

RE: Referencing Variables

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Referencing Variables

(OP)
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

RE: Referencing Variables

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Referencing Variables

The only time I get a #REF error is when H is selected for Skip's workbook, but that's only because there isn't any data on the H page.

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

=INDIRECT(Member_Type&"_Lst")

...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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Referencing Variables

(OP)
I used the indirect function in the data validation to link to the Section type. This is by reference to the cell C60 and not to a variable name. I want to be able to copy the information to blank space below so I can have maybe 20 different beam designs using either W or C Sections.

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

Is this some new issue that needs a reply?

Quote:

I want to be able to copy the information to blank space below so I can have maybe 20 different beam designs using either W or C Sections.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Referencing Variables

I still cannot determine if you have an unaddressed issue for our attention and if so, what might be the question. Was your last upload simply FYI or something related to a question?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Referencing Variables

(OP)
works fine with the little tweak... only have to set tabs to various section types and have the first column as the unsorted list and the second column as the sorted list for the data table and that the property columns are the same for each type of section.

Dik

RE: Referencing Variables

Glad you got it all sorted out.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Referencing Variables

(OP)
Skip, IDS and others... been a great help, just looking for more options to use the Indirect Function...

Thanks, Dik

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close