Nested Formulas v Named Formulas Any other solutions?

[Code Numpty]

Using Excel 2003:
I have a complicated quote template in Excel that does everything I need at
present. This has grown from simple beginnings and I think may now have gone
beyond the capabilities of Excel but the bosses want to stick with Excel.

My problem now is nested If formulas. I currently have the following formula
to look up a price against a part number in one of 5 separate price list
files.
---------------------------------------------------------------
=IF(ISBLANK(B26)=TRUE,"",IF(which_price=2,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[Export_Prices_Sterling.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=5,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[UK+Ire_Prices_Sterling.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=1,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[Export_Prices_Euros.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=4,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[UK+Ire_Prices_Euros.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=3,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[Export_Prices_US_Dollars.xls]Prices'!$A:$C,3,FALSE),2),))))))
---------------------------------------------------------------

The bosses now want to have more price lists (up to 10!) which means I have
exhausted the nested IF formula possibilities and I don't seem to be able to
use named formulas as the formula entry box truncates after too few
characters entered.

Am I going beyond the possibilities of Excel or can anyone suggest a way to
tackle this before I spend hours and hours going round in circles?

 

[Jarek Kujawa]

http://www.cpearson.com/Excel/nested.htm

 

[Code Numpty]

Thanks Jarek, I'd already checked out
http://www.cpearson.com/Excel/nested.htm, hence my comment about the formula
entry box truncating. I can't get the formula I need entered into the box.

 

[Glenn]

Using Excel 2003:
I have a complicated quote template in Excel that does everything I need at
present. This has grown from simple beginnings and I think may now have gone
beyond the capabilities of Excel but the bosses want to stick with Excel.

My problem now is nested If formulas. I currently have the following formula
to look up a price against a part number in one of 5 separate price list
files.
---------------------------------------------------------------
=IF(ISBLANK(B26)=TRUE,"",IF(which_price=2,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[Export_Prices_Sterling.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=5,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[UK+Ire_Prices_Sterling.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=1,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[Export_Prices_Euros.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=4,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[UK+Ire_Prices_Euros.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=3,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[Export_Prices_US_Dollars.xls]Prices'!$A:$C,3,FALSE),2),))))))
---------------------------------------------------------------

The bosses now want to have more price lists (up to 10!) which means I have
exhausted the nested IF formula possibilities and I don't seem to be able to
use named formulas as the formula entry box truncates after too few
characters entered.

Am I going beyond the possibilities of Excel or can anyone suggest a way to
tackle this before I spend hours and hours going round in circles?

Create a list of the price lists that looks something like this:

\\???srv\shared\Price_Lists\[Export_Prices_Sterling.xls]Prices'!$A:$C
\\???srv\shared\Price_Lists\[UK+Ire_Prices_Sterling.xls]Prices'!$A:$C
\\???srv\shared\Price_Lists\[Export_Prices_Euros.xls]Prices'!$A:$C

Add as many as "the bosses" want. Name the range price_list_list, then use this
as your formula:

=IF(ISBLANK(B26),"",ROUNDUP(VLOOKUP(B26,INDIRECT("'"&INDEX(price_list_list,which_price)),3,FALSE),2))

Only semi-tested, so you may have to do some tweaking...

 

[Pete_UK]

You can avoid many of your nested IFs by using a construct like this:

IF(which_price=1,ROUNDUP(VLOOKUP( .... ),2),0) +
IF(which_price=2,ROUNDUP(VLOOKUP( .... ),2),0) +
IF(which_price=3,ROUNDUP(VLOOKUP( .... ),2),0) ...

and so on.

Note that these are not nested - each IF will either return 0 or the
value from the VLOOKUP, and you can keep adding to them.

Of course, you will still need the:

=IF(B26="","", composite_IF_formula_from_above )

to surround them all.

I don't think Glenn's suggestion to use INDIRECT will work, unless you
have all those price lists open at the same time.

Hope this helps.

Pete

 

[Glenn]

You are right, they need to be opened.

If it were me I would put all of the price lists in one workbook on separate
sheets. Better yet, just add columns to a single price list.

I don't think Glenn's suggestion to use INDIRECT will work, unless you
have all those price lists open at the same time.

Hope this helps.

Pete

Using Excel 2003:
I have a complicated quote template in Excel that does everything I need at
present. This has grown from simple beginnings and I think may now have gone
beyond the capabilities of Excel but the bosses want to stick with Excel.

My problem now is nested If formulas. I currently have the following formula
to look up a price against a part number in one of 5 separate price list
files.
---------------------------------------------------------------
=IF(ISBLANK(B26)=TRUE,"",IF(which_price=2,ROUNDUP(VLOOKUP(B26,'\\???srv\sha­red\Price_Lists\[Export_Prices_Sterling.xls]Prices'!$A:$C,3,FALSE),2),IF(wh­ich_price=5,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[UK+Ire_Prices­_Sterling.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=1,ROUNDUP(VLOOKUP(B2­6,'\\???srv\shared\Price_Lists\[Export_Prices_Euros.xls]Prices'!$A:$C,3,FAL­SE),2),IF(which_price=4,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[U­K+Ire_Prices_Euros.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=3,ROUNDUP(V­LOOKUP(B26,'\\???srv\shared\Price_Lists\[Export_Prices_US_Dollars.xls]Price­s'!$A:$C,3,FALSE),2),))))))
---------------------------------------------------------------

The bosses now want to have more price lists (up to 10!) which means I have
exhausted the nested IF formula possibilities and I don't seem to be able to
use named formulas as the formula entry box truncates after too few
characters entered.

Am I going beyond the possibilities of Excel or can anyone suggest a way to
tackle this before I spend hours and hours going round in circles?

 

[Code Numpty]

Glenn,

I'll get on the case tomorrow and let you know how this works out. If it
does I won't be able to thank you enough!!

Thanks again
Sharon

Using Excel 2003:
I have a complicated quote template in Excel that does everything I need at
present. This has grown from simple beginnings and I think may now have gone
beyond the capabilities of Excel but the bosses want to stick with Excel.

My problem now is nested If formulas. I currently have the following formula
to look up a price against a part number in one of 5 separate price list
files.
---------------------------------------------------------------
=IF(ISBLANK(B26)=TRUE,"",IF(which_price=2,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[Export_Prices_Sterling.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=5,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[UK+Ire_Prices_Sterling.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=1,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[Export_Prices_Euros.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=4,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[UK+Ire_Prices_Euros.xls]Prices'!$A:$C,3,FALSE),2),IF(which_price=3,ROUNDUP(VLOOKUP(B26,'\\???srv\shared\Price_Lists\[Export_Prices_US_Dollars.xls]Prices'!$A:$C,3,FALSE),2),))))))
---------------------------------------------------------------

The bosses now want to have more price lists (up to 10!) which means I have
exhausted the nested IF formula possibilities and I don't seem to be able to
use named formulas as the formula entry box truncates after too few
characters entered.

Am I going beyond the possibilities of Excel or can anyone suggest a way to
tackle this before I spend hours and hours going round in circles?

Create a list of the price lists that looks something like this:

\\???srv\shared\Price_Lists\[Export_Prices_Sterling.xls]Prices'!$A:$C
\\???srv\shared\Price_Lists\[UK+Ire_Prices_Sterling.xls]Prices'!$A:$C
\\???srv\shared\Price_Lists\[Export_Prices_Euros.xls]Prices'!$A:$C

Add as many as "the bosses" want. Name the range price_list_list, then use this
as your formula:

=IF(ISBLANK(B26),"",ROUNDUP(VLOOKUP(B26,INDIRECT("'"&INDEX(price_list_list,which_price)),3,FALSE),2))

Only semi-tested, so you may have to do some tweaking...
.

 

[Duke Carey]

Assuming your 10 price lists do not run to more than 6,500 rows each, you can
do this in a straightforward way with a single lookup range.

Stack each of your price lists one on top of the other, in columns C:E In
column B r each list have the number corresponding to your which_price
variable. In column A simply concatenate columns A & B, perhaps with a
separator like so:

=A1&" - "&B1

Name the whole range, columns A:E, PriceList.

Now your ENTIRE lookup formula would be

=vlookup(which_price&" - "&B26,PriceList,5,0)

 

[Code Numpty]

Yes, the files need to be open, which is not an option. I am now going to
approach this from a completely different direction.

All prices will be in one file, in different columns.

I'm not sure how many products yet but if less than 6,500 I'll have a look
at Duke's suggestion. If I get stuck again (most likely). I'll start a new
thread.

Thanks for all the valuable advice.

 

[Glenn]

With all of the prices in one table, this becomes a simple VLOOKUP:

=VLOOKUP( B26 , price_table , which_price , false )

 

[Code Numpty]

A big thank you to all who helped me to think about this more clearly.

FYI my final solution was.

I put all prices lists in one file Masterprice_CONFIDENTIAL.xls.
Column A Part Number, B Description, C Tarrif Code , thereafter columns
contain prices.
Columns with prices have a price list code in row A, Row A is named range
'plist_code'.

In my quote template the code for the required price list appears in range
'which_price'.
My part numbers appear in column B.

My pricing formula i
=IF(ISBLANK(B26)=TRUE,"",VLOOKUP(B26,Masterprice_CONFIDENTIAL.xlsprices'!$A:$AC,MATCH(which_price,Masterprice_CONFIDENTIAL.xls!plist_code,0),FALSE))

The formula works well and the next step is to use a dynamic named range in
place of prices'!$A:$AC.