Can I shorten this any?

G

Gregory Day

I am looking for a whay to shorten/improve/optimize this formula. I just
dont' want it to be such a pain to update if needed. It is larely repetitive.

=IF(ISERROR(SUM(VLOOKUP(INDIRECT("L"&ROW()),Data!$J$2:$O$7,6,FALSE),(VLOOKUP(INDIRECT("M"&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("N"&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("O"&ROW()),Data!$L$2:$O$7,4,FALSE)),(VLOOKUP(INDIRECT("P"&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("Q"&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("R"&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("S"&ROW()),Data!$I$2:$O$7,7,FALSE)))),"",SUM(VLOOKUP(INDIRECT("L"&ROW()),Data!$J$2:$O$7,6,FALSE),(VLOOKUP(INDIRECT("M"&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("N"&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("O"&ROW()),Data!$L$2:$O$7,4,FALSE)),(VLOOKUP(INDIRECT("P"&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("Q"&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("R"&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("S"&ROW()),Data!$I$2:$O$7,7,FALSE))))

Thank you!
 
B

Bernie Deitrick

Gregory,

Describe, using as many words and example data tables as you want, what it is that you want to
calculate, and the conditions required to do so.

HTH,
Bernie
MS Excel MVP
 
Top