Lookups across different spreadsheets

  • Thread starter Thread starter wmjenner
  • Start date Start date
W

wmjenner

I want to do vlookups for a particular expense account across multipl
worksheets, each of which represents a different cost center. So o
my summary main worksheet, I have column A as shown below. 8600 is a
account number. In column B I want to add up all the occurrences o
8600 in worksheet B, C, D, E etc., each of which is a different cos
center. So column B will ultimately be the total of expense accoun
8600 across all cost centers, which in turn will be the total shown i
the P&L for that cost.

Acct. No.
8600
8610
8620
etc.

I've done this with simple vlookups but the formulas are very long an
when I try to change something later, Excel tells me the formula is to
long (there are 14 cost centers). Is there a slicker way to do thi
using SUMIF, INDIRECT or a combination thereof? Many thanks i
advance
 
Don't know if I follow you exactly, but if you have the totals for 8600 in
*exactly* the same place (say cell E5) on each of your sheets B, C, and D,
you could perhaps use a formula similar to this in Column B:

=SUM(SheetB:SheetD!E5)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



I want to do vlookups for a particular expense account across multiple
worksheets, each of which represents a different cost center. So on
my summary main worksheet, I have column A as shown below. 8600 is an
account number. In column B I want to add up all the occurrences of
8600 in worksheet B, C, D, E etc., each of which is a different cost
center. So column B will ultimately be the total of expense account
8600 across all cost centers, which in turn will be the total shown in
the P&L for that cost.

Acct. No.
8600
8610
8620
etc.

I've done this with simple vlookups but the formulas are very long and
when I try to change something later, Excel tells me the formula is too
long (there are 14 cost centers). Is there a slicker way to do this
using SUMIF, INDIRECT or a combination thereof? Many thanks in
advance!
 
You can use SUMPRODUCT assuming you meant across worksheets in the same
workbook

hard coded it would look something like

=SUMPRODUCT(SUMIF(INDIRECT("'"&{"B";"C";"D";"E"}&"'!$A$2:$A$200"),A2,INDIREC
T("'"&{"B";"C";"D";"E"}&"'!$B$2:$B$200")))

better would be to put the names of ALL worksheet that you want to calculate
in a named range and refer to the range


=SUMPRODUCT(SUMIF(INDIRECT("'"&MyLst&"'!$A$2:$A$200"),A2,INDIRECT("'"&MyLst&
"'!$B$2:$B$200")))

I assumed the accounts would be in column A and the amounts in B, adapt to
fit accordingly

Regards,

Peo Sjoblom
 
Back
Top