Indirect Sum Formulae

chris-evans

Board Regular
Joined
Feb 19, 2002
Messages
73
Hi I was hoping someone could help me. I'm trying to set up a formulae that is driven by a drop down box so it will give me an update each month.

I have one sheet for each month (named Jan, Feb, Mar, etc). I then have an analysis sheet on which there is a drop down box that gives me the sheet names which i've named month. I then have a column to return the current period result (i.e. month) in which i've placed the formula =INDIRECT(Month&"!c14") this works fine.

I now want to do one for YTD which needs to sum from sheet Jan to whatever month i've selected. This is the formulae i'm trying but failing with:

=SUM(INDIRECT("Jan:"&Month&"!c14"))

Any ideas why?

Many thanks in advance

Chris
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

chris-evans

Board Regular
Joined
Feb 19, 2002
Messages
73
Hi Glenn

This doesn't seem to work. Reason being, I need the end of the array to be dynamic i.e. driven by the drop down box and the first sheet it fixed i.e. Jan.

By using that formula, as I understand it I will need to specify each sheet, which isn't the purpose of what i'm trying to achieve.

Kind regards

Chris
 

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
Hi,

Try:

=SUM(N(INDIRECT({"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}&"!C14"))*({1,2,3,4,5,6,7,8,9,10,11,12}<=MATCH(Month,{"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"},0)))
 

chris-evans

Board Regular
Joined
Feb 19, 2002
Messages
73
I don't have a clue how that formula works but it does the job perfectly!

Thanks a lot for your help.

Chris
 

Forum statistics

Threads
1,136,518
Messages
5,676,324
Members
419,619
Latest member
jalme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top