Sumif choose worksheet for range and using indirect sum range

[justinhmann]

Hi,

I am having issues with formula I am developing.

I have two worksheets with fiscal year data:
1) "Fiscal2010Data"
2) "Fiscal2011Data"

There are accounts down column C (which appear multiple times) and then 5 data columns for each month:
e.g. "January2010.Actual", "January2010.Budget", "January2010.ForecastQ1",...Q2, Q3, etc. Each of these columns is a named range.

I then have a sheet that shows 12 months. The logic is setup so that a row updates with the proper named range for the month/data depending on criteria selected in data validation. In addition, the row below updates with the name of the worksheet.

We are on a May 31 fiscal year so if LTM (last 12 months) June Fiscal 2011 is selected and "Actual" is selected then:

Cell AG5 = May2010.Actual (which is a named range on worksheet "Fiscal2010Data")
Cell AG6 = Fiscal2010Data

Cell AE5 = June2010.Actual (which is a named range on worksheet "Fiscal2011Data")
Cell AE6 = Fiscal2011Data

The columns on both fiscal worksheets are in exactly the same placement so all the accounts by department are in column C:C. On my 12 month spread tab (with all the date logic) each account is only listed 1 time (column Z).

I need to do a sumif where the lookup range is $C:$C but the sheet it is looking at depends on what has populated into row 6 (e.g. AG6="Fiscal2010Data") and sum range is the named range (e.g. AG5="May2010.Actual").

My formula is not pulling the correct data. It is as follows:

=SUMIF(INDIRECT("'"&AG$6&"'!$C:$C"),$Z62,INDIRECT(AG$5))

In words:

=SUMIF(INDIRECT('Fiscal2010Data'!worksheet account column),Account,May2010.Actual)

Am I on the right track? Is it even possible? Any help would be greatly appreciated.

Thanks.

Justin

 

[T. Valko]

Hi,

I am having issues with formula I am developing.

I have two worksheets with fiscal year data:
1) "Fiscal2010Data"
2) "Fiscal2011Data"

There are accounts down column C (which appear multiple times) and then 5 data columns for each month:
e.g. "January2010.Actual", "January2010.Budget", "January2010.ForecastQ1",...Q2, Q3, etc. Each of these columns is a named range.

I then have a sheet that shows 12 months. The logic is setup so that a row updates with the proper named range for the month/data depending on criteria selected in data validation. In addition, the row below updates with the name of the worksheet.

We are on a May 31 fiscal year so if LTM (last 12 months) June Fiscal 2011 is selected and "Actual" is selected then:

Cell AG5 = May2010.Actual (which is a named range on worksheet "Fiscal2010Data")
Cell AG6 = Fiscal2010Data

Cell AE5 = June2010.Actual (which is a named range on worksheet "Fiscal2011Data")
Cell AE6 = Fiscal2011Data

The columns on both fiscal worksheets are in exactly the same placement so all the accounts by department are in column C:C. On my 12 month spread tab (with all the date logic) each account is only listed 1 time (column Z).

I need to do a sumif where the lookup range is $C:$C but the sheet it is looking at depends on what has populated into row 6 (e.g. AG6="Fiscal2010Data") and sum range is the named range (e.g. AG5="May2010.Actual").

My formula is not pulling the correct data. It is as follows:

=SUMIF(INDIRECT("'"&AG$6&"'!$C:$C"),$Z62,INDIRECT(AG$5))

In words:

=SUMIF(INDIRECT('Fiscal2010Data'!worksheet account column),Account,May2010.Actual)

Am I on the right track? Is it even possible? Any help would be greatly appreciated.

Thanks.

Justin

There's nothing wrong with the syntax of your formula:

=SUMIF(INDIRECT("'"&AG$6&"'!$C:$C"),$Z62,INDIRECT(AG$5))

You said:

My formula is not pulling the correct data.

So, does that mean you're getting some result, just not the result you expect?

Is the named range in AG5 the same size as "Sheet_Name!C:C" ?

 

[justinhmann]

Thanks so much for the quick confirmation. I was trying to figure out the problem for 3 hours last night. I had never done a sumif that changed worksheets so I thought it was something wrong with my formula. Your confirmation (and sleep) made me think to check the report that I am tying back to and I now see that that report is the one that is wrong. My numbers were correct all along! Errrrrr!!!! Thanks again.

 

[T. Valko]

Thanks so much for the quick confirmation. I was trying to figure out the problem for 3 hours last night. I had never done a sumif that changed worksheets so I thought it was something wrong with my formula. Your confirmation (and sleep) made me think to check the report that I am tying back to and I now see that that report is the one that is wrong. My numbers were correct all along! Errrrrr!!!! Thanks again.

Good deal. Thanks for the feedback!