Can I shorten this any?
 

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()),Dat a!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("N"&ROW()) ,Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("O"&RO W()),Data!$L$2:$O$7,4,FALSE)),(VLOOKUP(INDIRECT("P "&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIREC T("Q"&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(IND IRECT("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()),Da ta!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("Q"&ROW() ),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("R"&R OW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT(" S"&ROW()),Data!$I$2:$O$7,7,FALSE))))

Thank you!
--
Thank you,

Gregory

Can I shorten this any?
 

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


"Gregory Day" wrote in message
...
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()),Dat a!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("N"&ROW()) ,Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("O"&RO W()),Data!$L$2:$O$7,4,FALSE)),(VLOOKUP(INDIRECT("P "&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIREC T("Q"&ROW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(IND IRECT("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()),Da ta!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("Q"&ROW() ),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT("R"&R OW()),Data!$I$2:$O$7,7,FALSE)),(VLOOKUP(INDIRECT(" S"&ROW()),Data!$I$2:$O$7,7,FALSE))))

Thank you!
--
Thank you,

Gregory