Is there any easier way to do this formula?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am using the following formula in a worksheet, it works perfectly, however,
it is so long that it hides the first 8 or 9 lines of my spreadsheet when I
click on the cell containing it. Is there a shorter way to do the same thing?

=INDEX('[Weekly Sales by Department
2005.xls]PRODUCE'!$N$6:$N$428,MATCH(B2&C2,'[Weekly Sales by Department
2005.xls]PRODUCE'!$C$6:$C$429&'[Weekly Sales by Department
2005.xls]PRODUCE'!$B$6:$B$429,0))+INDEX('[Weekly Sales by Department
2005.xls]PRODUCE'!$N$6:$N$428,MATCH(B2&D2,'[Weekly Sales by Department
2005.xls]PRODUCE'!$C$6:$C$429&'[Weekly Sales by Department
2005.xls]PRODUCE'!$B$6:$B$429,0))+INDEX('[Weekly Sales by Department
2005.xls]PRODUCE'!$N$6:$N$428,MATCH(B2&E2,'[Weekly Sales by Department
2005.xls]PRODUCE'!$C$6:$C$429&'[Weekly Sales by Department
2005.xls]PRODUCE'!$B$6:$B$429,0))
 
Perhaps...

=SUMPRODUCT(--('[Weekly Sales by Department
2005.xls]PRODUCE'!$C$6:$C$429=B2),--ISNUMBER(MATCH('[Weekly Sales by
Department 2005.xls]PRODUCE'!$B$6:$B$429,C2:E2,0)),'[Weekly Sales by
Department 2005.xls]PRODUCE'!$N$6:$N$428)

You could try to shorten up the file name too.
 
Back
Top