Nested Formulas v Named Formulas Any other solutions?

  • Thread starter Thread starter Code Numpty
  • Start date Start date
C

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?
 
Code said:
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?
Click to expand...


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...
 
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
 
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.


Pete_UK said:
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?
Click to expand...
Click to expand...
 
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

Glenn said:
Code said:
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?
Click to expand...


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...
.
Click to expand...
 
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)
 
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.
 
With all of the prices in one table, this becomes a simple VLOOKUP:

=VLOOKUP( B26 , price_table , which_price , false )
 
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.
 
Back
Top