I am using the sumif function to summarize data from one worksheet on another
worksheet in the same workbook. The worksheet I am referencing contains a
pivot table which automatically refreshes each time I open the workbook. My
formula looks like this SUMIF(Hours!A10:A75,"12/31/07",Hours!U10:U75). It
works fine and returns the right number. My problem is that because the
Hours worksheet contains pivot table data, when it is refreshed the column I
need to have summed might be "AA" instead of "U". Is there anyway to direct
Excel to have the column reference in my formula float with the column
heading "Total Amount"? Or is there some other formula I should be using?

I hope that makes sense.

Thanks,
Dawn

direct Excel to have the column reference in my formula
float with the column heading "Total Amount"?

OFFSET is one way to get it done

Assuming the pivot headers are in row 9
this expression should grab it for you:
=SUMIF(Hours!A10:A75,"12/31/07",OFFSET(Hours!A10:A75,,MATCH("Total
Amount",Hours!$9:$9,0)-1))

Adapt to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dawn" wrote:
I am using the sumif function to summarize data from one worksheet on another
worksheet in the same workbook. The worksheet I am referencing contains a
pivot table which automatically refreshes each time I open the workbook. My
formula looks like this SUMIF(Hours!A10:A75,"12/31/07",Hours!U10:U75). It
works fine and returns the right number. My problem is that because the
Hours worksheet contains pivot table data, when it is refreshed the column I
need to have summed might be "AA" instead of "U". Is there anyway to direct
Excel to have the column reference in my formula float with the column
heading "Total Amount"? Or is there some other formula I should be using?

I hope that makes sense.

Thanks,
Dawn

Untested...

Should this:
=SUMIF(Hours!A10:A75,"12/31/07", ...
be more like:
=SUMIF(Hours!A10:A75,""&date(2007,12,31), ....



Max wrote:

direct Excel to have the column reference in my formula
float with the column heading "Total Amount"?

OFFSET is one way to get it done

Assuming the pivot headers are in row 9
this expression should grab it for you:
=SUMIF(Hours!A10:A75,"12/31/07",OFFSET(Hours!A10:A75,,MATCH("Total
Amount",Hours!$9:$9,0)-1))

Adapt to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dawn" wrote:
I am using the sumif function to summarize data from one worksheet on another
worksheet in the same workbook. The worksheet I am referencing contains a
pivot table which automatically refreshes each time I open the workbook. My
formula looks like this SUMIF(Hours!A10:A75,"12/31/07",Hours!U10:U75). It
works fine and returns the right number. My problem is that because the
Hours worksheet contains pivot table data, when it is refreshed the column I
need to have summed might be "AA" instead of "U". Is there anyway to direct
Excel to have the column reference in my formula float with the column
heading "Total Amount"? Or is there some other formula I should be using?

I hope that makes sense.

Thanks,
Dawn

--

Dave Peterson

Dave,

Curiously the original version worked (as the OP mentioned in the post) when
I plugged it in here, so I didn't pay that part of it further attention
since it wasn't the focus.

But I agree that your version -- which works, too -- is the better,
unambiguous way to go.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Peterson" wrote in message
...
Untested...

Should this:
=SUMIF(Hours!A10:A75,"12/31/07", ...
be more like:
=SUMIF(Hours!A10:A75,""&date(2007,12,31), ....

I would look carefully and test thoroughly, if I were you, as the formulae
do not give identical results.
--
David Biddulph

"Max" wrote in message
...
Dave,

Curiously the original version worked (as the OP mentioned in the post)
when I plugged it in here, so I didn't pay that part of it further
attention since it wasn't the focus.

But I agree that your version -- which works, too -- is the better,
unambiguous way to go.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Peterson" wrote in message
...
Untested...

Should this:
=SUMIF(Hours!A10:A75,"12/31/07", ...
be more like:
=SUMIF(Hours!A10:A75,""&date(2007,12,31), ....

I would look carefully and test thoroughly, if I were you, as the formulae
do not give identical results.

well, it does return identical (Dave's & the OP's version) in my tests here.
perhaps you could help by stating which source dates won't work ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

THANKS! You guys rock! That seems to be working for me!

"Max" wrote:

direct Excel to have the column reference in my formula
float with the column heading "Total Amount"?

OFFSET is one way to get it done

Assuming the pivot headers are in row 9
this expression should grab it for you:
=SUMIF(Hours!A10:A75,"12/31/07",OFFSET(Hours!A10:A75,,MATCH("Total
Amount",Hours!$9:$9,0)-1))

Adapt to suit
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dawn" wrote:
I am using the sumif function to summarize data from one worksheet on another
worksheet in the same workbook. The worksheet I am referencing contains a
pivot table which automatically refreshes each time I open the workbook. My
formula looks like this SUMIF(Hours!A10:A75,"12/31/07",Hours!U10:U75). It
works fine and returns the right number. My problem is that because the
Hours worksheet contains pivot table data, when it is refreshed the column I
need to have summed might be "AA" instead of "U". Is there anyway to direct
Excel to have the column reference in my formula float with the column
heading "Total Amount"? Or is there some other formula I should be using?

I hope that makes sense.

Thanks,
Dawn

welcome, Dawn. glad to hear that.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dawn" wrote in message
...
THANKS! You guys rock! That seems to be working for me!