|
|
| |
 Question |
|
| |
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)) |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
| |
 Answer |
|
| |
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.
Ms. P wrote:
> 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))
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
Notify me of replies