Nesting function or other suggestions

[Ruth]

I have a worksheet that is making me crazy. Basically its a sales order form
with drop down menus for each set of items based on category. I want the MSRP
field to autofill with the prices when an item is selected and for this I am
using the "if" with nesting. But it only allows me to nest 8 and I need 14.

Any suggestions on other functions I can use to accomplish this?

 

[Gary''s Student]

Make a small table of item in one column and price in the column next to it.
Then use =VLOOKUP() to get the price once an item is selected.

 

[Ron Coderre]

See Debra Dalgleish's website....she has a great
Order Form example and instructions:
http://www.contextures.com/xlOrderForm01.html

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ruth]

Ron that worked like a charm! Thanks

See Debra Dalgleish's website....she has a great
Order Form example and instructions:
http://www.contextures.com/xlOrderForm01.html

Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Ron Coderre]

You're welcome.....I'm glad that helped.

Regards,

Ron
Microsoft MVP (Excel)

 

[Shane Devenshire]

Hi Ruth,

Excel 2003 and earlier allows 7 levels of nesting. Excel 2007 allows 64
levels.

You can beat the 2003 limit by 1. Employing range names for portions of the
formulas, 2. Concatenation formula components, 3. Creating the formulas in
Lotus 1-2-3 or Quattro Pro and then import the file.

In my experience deep nesting is often the result of using an inefficient
approach to solving a problem - for example using a nested IF when one
should use a VLOOKUP.

Without seeing an example of your formula we will be limited on how we can
help.

Cheers,
Shane Devenshire
Microsoft Excel MVP