Tricky Sheet Reference of Death. part I

[Maclear]

Hey Y'all,

I am currently facing a problem which, I think, will require a cunnin
VB-ist to solve. Anyway, here goes.

I am working on a database of raw materials gathered from all over th
US. Each Item has a characteristic which I called "State of Origin"
Values can be anything like LA, AZ, NY...and so forth.

A second characteristic is the amount we pay for that item. This amoun
depends on the values of column X, AF and of course the State of Origi
Column. So for Item 25 Excel will lookup in a Table on a differen
sheet the corresponding price Given the value of X and AF.

So here's what I have so far:
=INDEX('Prices *LA*.2004'!$C$6:$Y$30;List!X25;List!AF25)

Now I want Excel to check my State of origin Column and insert th
State value within the formula so that Excel will automatically looku
for the amount to pay in the corresponding table.
For instance, if State of origin is AZ the formula will change to

=INDEX('Prices *AZ*.2004'!$C$6:$Y$30;List!X25;List!AF25)

Well, anyone who can help me with this has my full gratitude an
maximum respect.

Cheers,

:

 

[Maclear]

Kay,

I checked out what those address and offset functions do. And I'm no
sure that they do what I want. I apologize if my first post wa
unclear.

Basically, I buy raw materials, the prices depend on the quality of th
material, the weight bought at once, and the state of origin. My price
are arranged in a 2D table. Weights are on the y-axis and quality o
the product is on the x-axis. For each unique combination of a give
weight and level of quality comes a unique price.

But given weights and quality are held constant, the price will stil
vary if the product comes from a different place. So I have severa
worksheets with a 2D price table on each of them. All worksheets hav
the exact same shape and structure. So I just need the program to pic
the right table according to the "state of origin value".

Okay, that was just to make sure I explained what I'm trying to d
properly...

Now, as I said, I m not quite sure what those address and offse
functions do, rather I know what they do separately cuz' I checked i
out, but I don' t understand how they will work together. Could yo
please develop a little bit further on the mecanism of your formula.

Thanks for your help by the way, I appreciate

 

[Harlan Grove]

...
...

I am working on a database of raw materials gathered from all over the
US. Each Item has a characteristic which I called "State of Origin".
Values can be anything like LA, AZ, NY...and so forth.

A second characteristic is the amount we pay for that item. This amount
depends on the values of column X, AF and of course the State of Origin
Column. So for Item 25 Excel will lookup in a Table on a different
sheet the corresponding price Given the value of X and AF.

So here's what I have so far:
=INDEX('Prices *LA*.2004'!$C$6:$Y$30;List!X25;List!AF25)

This is an error. Worksheet names can't contain asterisks. So what's your
*ACTUAL* formula? Or have you just sprinkled extraneous asterisks to set off the
state abbreviation?

Now I want Excel to check my State of origin Column and insert the
State value within the formula so that Excel will automatically lookup
for the amount to pay in the corresponding table.
For instance, if State of origin is AZ the formula will change to

=INDEX('Prices *AZ*.2004'!$C$6:$Y$30;List!X25;List!AF25)

Way too short on details. Are your different state of origin sources different
worksheets or different workbooks? If different worksheets, and assuming there
really aren't any asterisks,

=INDEX(INDIRECT("'Prices "&StateCodeEntry&".2004'!C6:Y30");List!X25;List!AF25)

 

[Harlan Grove]

...
...

something like:

OFFSET(ADDRESS(6,3,1,TRUE,"Prices *"&[Reference returning state of origin]
&"*.2004"),0,0,30,23)

...

Not close! ADDRESS returns a string. OFFSET requires range reference as 1st
argument. Your OFFSET call only returns #REF!.

 

[Maclear]

Oh my god,

It actually works!
Well that's just great, many thanks. You are officially a cunnin
VB-ist, HG.

Just for the sake of answering your question: There was no asteris
in my original code I intended to *Bold* the State in the formula t
underline the fact it changes with the state of origin value... and fo
some reasons it came out as a double asterisk. Go figure.

And yep you were right in assuming I placed the prices on differen
worksheets, not workbooks.

Again, it worked like a charm.

Full-scaled Gratitude and extreme Regards .:

 

[Guest]

Whoops. Yes you would need the INDIRECT function to convert the string into a reference. But the formula is starting to look really inwieldy.

An alternative (also using INDIRECT), would be to NAME your state of origin lookup tables in a systematic way - "Price_LA", "Price_NY" or whatever. Then your lookup function can be pretty simple. The range element would simply be:

INDIRECT("Price_"&[State of origin])

...
...

something like:

OFFSET(ADDRESS(6,3,1,TRUE,"Prices *"&[Reference returning state of origin]
&"*.2004"),0,0,30,23)

...

Not close! ADDRESS returns a string. OFFSET requires range reference as 1st
argument. Your OFFSET call only returns #REF!.