Excel Complex Lookups

[Kathy Goodwin]

I am working on a spreadsheet for pricing in excel XP. I have a 5 sheet
workbook. On sheet 5 there are multiple customers listed with pricing
information for multiple thicknesses of product. Each product type has
attributes that are applied (such as a hole), for each thickness the
price of such a attribute changes. I then have vlookup formulas in the
other three sheet that allow me to have all of the prices per each
thickness listed on their own sheet by customer. This brings me to my
problem. From a data entry form I need to lookup cell J21 and evaluate
the thickness that the user entered. Then I need to go to the sheet
that is related to that particular thickness and lookup the customer
that the user entered. Based on that information I need to return the
prices from the chosen sheet to the entry sheet and calculate the price.
How do I tell my entry sheet which worksheet to use based on the user
input value. I tried vlookup and if functions but they are much too
lengthy and I have been unable to figure out how or if you can return
name ranges to a formula. Seems like VBA might be the only way to go.
Can anyone help me?

Thanks

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Niek Otten]

You should be able to select the right sheet using the INDIRECT() function.
Look in Help for details.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Kevin Stecyk]

Kathy,

I am willing to have a look at your spreadsheet. If you are interested, you
can send it to me at (e-mail address removed).

Best regards,
Kevin

 

[Kathy Goodwin]

Kevin,

I would appreciate that. What I am looking for is to update the lookup
name in the formulas that are finding costs. The lookup value needs to
be based on column J in the information sheet. Right now it is just set
to a default sheet to get it to populate correctly.

Thanks,

Kathy

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[Kevin Stecyk]

Kathy Goodwin wrote...

Kevin,

I would appreciate that. What I am looking for is to update the lookup
name in the formulas that are finding costs. The lookup value needs to
be based on column J in the information sheet. Right now it is just set
to a default sheet to get it to populate correctly.

Thanks,

Kathy and I managed to solve her question. We used a nested vlookup
statement that required the Indirect function.

Best regards,
Kevin