I am using Excel 2003. I have a few sheets in one workbook each containing
monthly data. Data from each month needs to go on a year to date sheet. Say
in my monthly sheets A10:A20 contain a "Position" and B10:B20 contains
numbers based on that "Position". Each month may or may not contain the same
"Positions" in A10:A20. I need a function to add up the numbers for each
"Position" if it appears in any given month. For example, "Position" AR-01
may appear in July and September and in different cells in those months in
the A column. I need my YTD sheet containing all possible "Positions" to
find the number in the B culumn associated with the "Position" in the A
column and add the year's data up.
--
Mike

One easy set-up using SUMIF, with INDIRECT providing some extra flexibility

In your YTD summary sheet,
List the monthly sheetnames in B1 across, eg: July, August, etc
List the "Positions" in A2 down, eg: AR-01
(Sheetnames listed must match exactly with what's on the tabs, except for
case)

Then you could place this in B2:
=SUMIF(INDIRECT("'"&C$1&"'!A:A"),$A2,INDIRECT("'"& C$1&"'!B:B"))
Copy B2 across/fill down to populate
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400 Files:361 Subscribers:58
xdemechanik
---
"Mike" wrote:
I am using Excel 2003. I have a few sheets in one workbook each containing
monthly data. Data from each month needs to go on a year to date sheet. Say
in my monthly sheets A10:A20 contain a "Position" and B10:B20 contains
numbers based on that "Position". Each month may or may not contain the same
"Positions" in A10:A20. I need a function to add up the numbers for each
"Position" if it appears in any given month. For example, "Position" AR-01
may appear in July and September and in different cells in those months in
the A column. I need my YTD sheet containing all possible "Positions" to
find the number in the B culumn associated with the "Position" in the A
column and add the year's data up.
--
Mike

Errata, formula should read:
... in B2:
=SUMIF(INDIRECT("'"&B$1&"'!A:A"),$A2,INDIRECT("'"& B$1&"'!B:B"))
Copy B2 across/fill down to populate
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400 Files:361 Subscribers:58
xdemechanik
---

Thank you very much for your help. I am getting #REF! when I put in the
formula. A9 has my heading "Position", B9 is heading "Lenses", C9 is heading
Cost. I have separate departments listed on the same sheet. Each department
may have a couple "Position" names that are the same, but have different data
from each other. Example - Position AR-01 may be in 2 or 3 departments
depending on the month but each have different values in the Lenses and Cost
columns. A9:A30 contains department XYZ, A37:A32 contains department
ABC....for 7 departments. Each have the same headings for the columns.
--
Mike


"Max" wrote:

Errata, formula should read:
.. in B2:
=SUMIF(INDIRECT("'"&B$1&"'!A:A"),$A2,INDIRECT("'"& B$1&"'!B:B"))
Copy B2 across/fill down to populate
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,400 Files:361 Subscribers:58
xdemechanik
---

.. I am getting #REF! when I put in the formula.
That usually means your sheetnames listed in B1 across
somehow doesn't match exactly with what's on the tabs

Easier to see things if you could upload your sample
using a free filehost, then post a link to it here

For example, you could use this free filehost to upload:
http://www.freefilehosting.net/

Copy the "direct link" which is generated after you upload,
then paste it here

(Desensitize your sample as appropriate)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,500 Files:361 Subscribers:58
xdemechanik
---
"Mike" wrote:
Thank you very much for your help. I am getting #REF! when I put in the
formula. A9 has my heading "Position", B9 is heading "Lenses", C9 is heading
Cost. I have separate departments listed on the same sheet. Each department
may have a couple "Position" names that are the same, but have different data
from each other. Example - Position AR-01 may be in 2 or 3 departments
depending on the month but each have different values in the Lenses and Cost
columns. A9:A30 contains department XYZ, A37:A32 contains department
ABC....for 7 departments. Each have the same headings for the columns.
--
Mike