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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top