Indirect Sum Formulae

[chris-evans]

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

 

[GlennUK]

Have a look at Fairwinds answer here:

http://www.mrexcel.com/board2/viewtopic.php?t=232008

 

[chris-evans]

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]

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]

I don't have a clue how that formula works but it does the job perfectly!

Thanks a lot for your help.

Chris