I am trying to create a formula that will allow me to sum all the values in a
chart for a certain date. So across 5 sheets, I have a total sheet, and I
want the "January 31st" value in the "completion" column to automatically
update based on the values in "January 31st" under the "completion" column
for the other worksheets. I know this is possible if the cells were the same
- but they are not. Is there a way to create a formula based on searching for
the date name?

Thank you!

Is the "completion" column in the same location on each sheet? What column
is it? What column contians the dates? What are the sheet names? Are they
unique or do they follow some sort of pattern like: Week1, Week2, Week3 etc.
?

--
Biff
Microsoft Excel MVP


"Kgoodman" wrote in message
...
I am trying to create a formula that will allow me to sum all the values in
a
chart for a certain date. So across 5 sheets, I have a total sheet, and I
want the "January 31st" value in the "completion" column to automatically
update based on the values in "January 31st" under the "completion"
column
for the other worksheets. I know this is possible if the cells were the
same
- but they are not. Is there a way to create a formula based on searching
for
the date name?

Thank you!

yes, the completion column is in the same location on all sheets. Column C.
The dates are in Column A. Sheet names are "Total, S1, S2, S3"

"T. Valko" wrote:

Is the "completion" column in the same location on each sheet? What column
is it? What column contians the dates? What are the sheet names? Are they
unique or do they follow some sort of pattern like: Week1, Week2, Week3 etc.
?

--
Biff
Microsoft Excel MVP


"Kgoodman" wrote in message
...
I am trying to create a formula that will allow me to sum all the values in
a
chart for a certain date. So across 5 sheets, I have a total sheet, and I
want the "January 31st" value in the "completion" column to automatically
update based on the values in "January 31st" under the "completion"
column
for the other worksheets. I know this is possible if the cells were the
same
- but they are not. Is there a way to create a formula based on searching
for
the date name?

Thank you!

Ok, try this:

A2 = 1/31/2009

Based on your sheet names being: S1, S2 and S3.

Dates in the range A1:A10
Values to sum in the range C1:C10.

=SUMPRODUCT(SUMIF(INDIRECT("S"&{1,2,3}&"!A1:A10"), A2,INDIRECT("S"&{1,2,3}&"!C1:C10")))

--
Biff
Microsoft Excel MVP


"Kgoodman" wrote in message
...
yes, the completion column is in the same location on all sheets. Column
C.
The dates are in Column A. Sheet names are "Total, S1, S2, S3"

"T. Valko" wrote:

Is the "completion" column in the same location on each sheet? What
column
is it? What column contians the dates? What are the sheet names? Are they
unique or do they follow some sort of pattern like: Week1, Week2, Week3
etc.
?

--
Biff
Microsoft Excel MVP


"Kgoodman" wrote in message
...
I am trying to create a formula that will allow me to sum all the values
in
a
chart for a certain date. So across 5 sheets, I have a total sheet, and
I
want the "January 31st" value in the "completion" column to
automatically
update based on the values in "January 31st" under the "completion"
column
for the other worksheets. I know this is possible if the cells were the
same
- but they are not. Is there a way to create a formula based on
searching
for
the date name?

Thank you!

Hi,

I would suggest that you consolidate all the data in one worksheet first and
then use the SUM() function. Since the cell is not the same on all the
worksheets, please make use of Data Consolidate to bring all info on one
worksheet.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Kgoodman" wrote in message
...
I am trying to create a formula that will allow me to sum all the values
in a
chart for a certain date. So across 5 sheets, I have a total sheet, and I
want the "January 31st" value in the "completion" column to automatically
update based on the values in "January 31st" under the "completion"
column
for the other worksheets. I know this is possible if the cells were the
same
- but they are not. Is there a way to create a formula based on searching
for
the date name?

Thank you!