PDA

View Full Version : Going over formula length limit



icthus123
06-25-2007, 08:33 AM
Hi chaps I have used the following formula to return a value based on the values of a table.

=IF(D4="Building",IF(Sheet1!H4="Normal",VLOOKUP(Sheet1!F4,BuildingWorkTable,2,FALSE),IF(Sheet1!H4="Early Evening",VLOOKUP(Sheet1!F4,BuildingWorkTable,3,FALSE),IF(Sheet1!H4="Late Evening",VLOOKUP(Sheet1!F4,BuildingWorkTable,4,FALSE),IF(Sheet1!H4="Saturday",VLOOKUP(Sheet1!F4,BuildingWorkTable,5,FALSE),IF(Sheet1!H4="Sunday",VLOOKUP(Sheet1!F4,BuildingWorkTable,6,FALSE),IF(Sheet1!H4="Bank Holiday",VLOOKUP(Sheet1!F4,BuildingWorkTable,6,FALSE)," ")))))))

The problem is that the value of D4 could also be "Mechanical" Or "Electrical" and I need to basically repeat the same conditions for these two options but getting the VLOOKUP values from tables "MechanicalWorkTable" and "ElectricalWorkTable" respectively. The problem is this means I go over my formula length limit. Is there any way I can get around this?

lucas
06-25-2007, 08:35 AM
Try using named ranges instead of sheet references.

icthus123
06-25-2007, 08:38 AM
I'm not quite sure how you mean?

lucas
06-25-2007, 08:49 AM
I'm not sure I'm giving you the best advice so be patient until someone else weighs in...but I think if you took your ranges like:
Sheet1!H4="Normal"

If you define a range name for Sheet1!H4 like R1 it would shorten your formula.
IF(R1="Normal",etc.

Surely there is a better solution though.

icthus123
06-25-2007, 08:58 AM
Right I see what you mean. But if I defined three parts as named formulae i.e. call

=IF(D4="Building",IF(Sheet1!H4="Normal",VLOOKUP(Sheet1!F4,BuildingWorkTable ,2,FALSE),IF(Sheet1!H4="Early Evening",VLOOKUP(Sheet1!F4,BuildingWorkTable,3,FALSE),IF(Sheet1!H4="Late Evening",VLOOKUP(Sheet1!F4,BuildingWorkTable,4,FALSE),IF(Sheet1!H4="Saturda y",VLOOKUP(Sheet1!F4,BuildingWorkTable,5,FALSE),IF(Sheet1!H4="Sunday",VLOOK UP(Sheet1!F4,BuildingWorkTable,6,FALSE),IF(Sheet1!H4="Bank Holiday",VLOOKUP(Sheet1!F4,BuildingWorkTable,6,FALSE)," ")))))))

forBuilding

and called the other parts forMechanical and forElectrical would I be able to combine these into one formula? Obviously in order to do this I'd have to take your advice and Name all my ranges as well.

Is what I've suggested possible?

lucas
06-25-2007, 09:22 AM
It's a long and complicated formula(to me at least) so I think I will wait for some of the better formula folks here to look at this with us instead of taking you in the wrong direction. I don't see why it would not be possible though.

Paul_Hossler
06-25-2007, 05:23 PM
1. FWIW, I think a user defined function work better. That way you could have (what seems to me) complicated formula logic defined using VBA which has capabilities to make it more maintainable (If-Then-Else, Select Case, etc.)

2. Or staying in WS formulas, you could use columns to hold intermediate results and then one for the final answer, i.e. 3-4 smaller formulas, instead of one long one.

geekgirlau
06-25-2007, 05:49 PM
I can think of a couple of options here to get rid of the IFs:

Have a lookup table to capture the column number. The lookup table would consist of the values you are testing ("Early Evening", etc.) and a second column showing the column number for your VLOOKUP formula. You would then end up with something like this:

=IF(D4="Building",VLOOKUP(Sheet1!F4, BuildingWorkTable, VLOOKUP(Sheet1!H4, MyLookupRange, 2, FALSE),FALSE)," ")



Depending on what headings are in the range BuildingWorkTable, you can use MATCH to determine which column the heading resides in. For example, if the heading in column 3 is "Early Evening", you could use MATCH(Sheet1!H4, Row1OfBuildingWorkTable, FALSE) in place of the "3" in the VLOOKUP formula. The only exception in your list is that column 6 is used for two separate values.

mikerickson
06-25-2007, 11:03 PM
Another option is to use Named functions instead of the VLOOKUPS

Name=VL3
RefersTo=VLOOKUP(Sheet1!F4,BuildingWorkTable,3,FALSE)

Name=VL4
RefersTo=VLOOKUP(Sheet1!F4,BuildingWorkTable,4,FALSE)

etc.

Using those names would shorten the formula.

Since names follow absolute/relative addressing, you should be in the proper cell when entering those names, and you might need to set the column absolute.

icthus123
06-26-2007, 03:36 AM
Okay thanks guys I've used this.

=IF(D4="Building",VLOOKUP(F4,BuildingWorkTable,MATCH(H4,BuildingHeads,0),FALSE),IF(D4="Electrical",VLOOKUP(F4,ElectricalWorkTable,MATCH(H4,ElectricalHeads,0),FALSE),IF(D4="Mechanical",VLOOKUP(F4,MechanicalWorkTable,MATCH(H4,MechanicalHeads,0),FALSE)," ")))