Formula too long

[wammer]

Any ideas on how to shorten this behemoth formula. Is there some typ
of Macro I can create?:

"=if(AND(O$3>=(vlookup($B5,'Master Sheet'!$B$7:$T$2004,'Maste
Sheet'!q$5,FALSE)),o$3<=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Master Sheet'!R$5,FALSE))),concatenate($m5,'Maste
Sheet'!Q$4), (if(AND(O$3>=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Maste
Sheet'!S$5,FALSE)),o$3<=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Master Sheet'!T$5,FALSE))),concatenate($m5,'Maste
Sheet'!s$4), (if(AND(O$3>=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Maste
Sheet'!o$5,FALSE)),o$3<=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Master Sheet'!p$5,FALSE))),concatenate($m5,'Maste
Sheet'!o$4), (if(AND(O$3>=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Maste
Sheet'!c$5,FALSE)),o$3<=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Master Sheet'!d$5,FALSE))),concatenate($m5,'Maste
Sheet'!c$4), (if(AND(O$3>=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Maste
Sheet'!e$5,FALSE)),o$3<=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Master Sheet'!f$5,FALSE))),concatenate($m5,'Maste
Sheet'!e$4), (if(AND(O$3>=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Maste
Sheet'!g$5,FALSE)),o$3<=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Master Sheet'!h$5,FALSE))),concatenate($m5,'Maste
Sheet'!g$4), (if(AND(O$3>=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Maste
Sheet'!i$5,FALSE)),o$3<=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Master Sheet'!j$5,FALSE))),concatenate($m5,'Maste
Sheet'!i$4), (if(AND(O$3>=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Maste
Sheet'!k$5,FALSE)),o$3<=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Master Sheet'!l$5,FALSE))),concatenate($m5,'Maste
Sheet'!k$4), (if(AND(O$3>=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Maste
Sheet'!m$5,FALSE)),o$3<=(vlookup($B5,'Maste
Sheet'!$B$7:$T$2004,'Master Sheet'!n$5,FALSE))),concatenate($m5,'Maste
Sheet'!m$4),0))))))))))))))))

 

[Myrna Larson]

You need to get rid of those sheet names. Assigning a name to the range
'Master Sheet'!$B$7:$T$2004, say, Table, will take care of the references in
the VLOOKUPs. but it won't take care of the other references to that sheet.
Maybe it will be enough, though.

If not, then perhaps you could to put the whole formula on Master Sheet, in an
out of the way place. There it won't need sheet references at all. You would
also need 3 cells that have copies of the information in B5, O3, and M5 on the
sheet where you have the formula now. Then you could get the result with a
single ='Master Sheet'!K89, for example.

Another thing is to remove the CONCATENATE. Instead of

CONCATENATE($M5,'Master Sheet'!$s4)

write

$M5&'Master Sheet'!$s4

which will help a little bit.

BTW, I'm surprised you haven't hit the limit on nested IF statements.

Yes, you can create a macro, but I can't tackle it because I don't really
understand what the formula is doing.