OK. I have a database of steel section properties (area, width, etc.). All of the properties are located within named ranges. In the database table the first column is the group name range -- wshape. All of the cells to the right are different properties of each section listed in the first column (area, width, depth, weight, etc.) They are all contained in appropriate name ranges based on the property -- e.g. "wshape.a" for area, "wshape.w" for weight.
My goal is to use a lookup value to lookup a specific property for a specific shape using the result of a function as the named range.
1 -- I have a column that is data validated with a list = wshape.
2 -- I have a function that returns a text value which for this example = "wshape"
3 -- I have this function =CONCATENATE(wshape !the value from line 2 above!,".a") which yields "wshape.a"
Now I want to use =LOOKUP(!any value from the list in line 1 above!,wshape !from line 2 above!, wshape.a !from line 3 above!)...so =LOOKUP(value,wshape,wshape.a)
There seems to be a breakdown with excel understanding that the results from the functions in line 2 and line 3 above are named ranges to be referenced in the LOOKUP function.
Any help is appreciated. I can post some pics or give more detail if that would help.
Cheers.
Hi hinkle.j.s
Welcome to the forum
Rather thanYou would do best to post a sample workbook showing your Sheet Layout and perhaps Before and After examples.....post some pics...
It should clearly illustrate your problem and not contain any sensitive data.
I take it you have based what you have on AISC Section Properties i.e. Steel Tables.
What field are you working in?
Cheers
How your various Named ranges are defined will be of critical importance regards the approaches you may adopt in your final formula.
If the Names are Fixed references [=Database!$A$1:$A100] then you can use INDIRECT to convert the "range strings" to actual range references.
(note INDIRECT is Volatile - see link in sig. for more info.)
However, if they Names are dynamic [=OFFSET(Database!$A$1,0,0,COUNTA(Database!$A:$A),1)] you can't and will need to use a workaround method (Evaluate, Choose etc...)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
OK. Here is a screenshot of what I'm trying to do. It's a very basic version as I ultimately want to be able to scan the entire database, work in both English and SI units, etc. For the example database the title of each range is in bold and the range begins below that cell for each respective name/color.
I feel like this should be simple, but I haven't ever been trained in excel and I only recently started using dedicated ranges and lookup functions.
Marcol, I am a structural engineer.
DonkeyOte, the named ranges are fixed references.
In which case try:Originally Posted by hinkle.j.s
though you might prefer to use an INDEX/MATCH [exact] just incase...=LOOKUP(B2,INDIRECT(B3),INDIRECT(B4))
=INDEX(INDIRECT(B4),MATCH(B2,INDIRECT(B3),0))Give it time... you will find few who have been "trained" in the [dark] arts of Excel ... 99% are self taught - the result of trial and much error.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Thanks, DonkeyOte! It works like I thought it should have all along!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks