# Indirect Sum Formulae

#### chris-evans

##### Board Regular
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?

Chris

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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

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)))

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

Thanks a lot for your help.

Chris

Replies
6
Views
296
Replies
4
Views
152
Replies
3
Views
289
Replies
5
Views
72
Replies
1
Views
150

1,219,694
Messages
6,149,769
Members
450,913
Latest member
mdsuther

### 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.

### Which adblocker are you using?

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

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