Cell value determines where to look

  • Thread starter Thread starter Victorinox
  • Start date Start date
V

Victorinox

I want to have an alternative solution, to avoid to have multiple IF,IF,IF
clauses.

My situation:
I have one overview sheet for the end user.
I have per brand - in total 10 brands - a different calculation sheet, named
"calculation brand X" (X = brandname)

My question:
Can I make a code that if the enduser selects brand X Excel picks a cell
from sheet" Calculation brand X". I know I can do it with mulitple IF
clauses, but I would rather have an more clear alternative. I assume this is
rather easy but I do not find it. Can some one help me?

Tx,
 
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
 
Use the INDIRECT function. For example, suppose your brands are A, B,
C, .... Give the user a method to pick a brand (data validation
dropdown, combobox, etc), and then use the brand as part of a
worksheet name that gets passed to INDIRECT. E.g., if the user has
selected "B", and that value is in cell A1,

=INDIRECT("'Calculation Brand "&A1"'!K20")

This will return the result in cell K20 on the worksheet named
"calculation brand X" where X is the value in cell A1.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]
 
Why not just use a vlookup table?
Make the selection using a data validation drop-down.
 
Back
Top