# Thread: Using Indirect Function in Sum Formula

1. ## Using Indirect Function in Sum Formula

I have a sum formula referring to a cells to add data on various sheets for eg =sum(Oct:Mar!B6) ,=sum(Oct:Mar!B7) etc

I would like to use an indirect formula to refer to a month name from a drop down list so that the formula to be changed immediate for eg If I want to calculate a value from Oct to Dec , I would select Dec from the Drop down list and the formula would change to = sum(Oct:Dec!B6)
The starting point would always be Oct for eg = sum(Oct: ....)

2. Howard,

The Indirect function does NOT support 3D (multi-sheet range) references.

I've tried a UDF w/o success. What you may want to try is adding a hidden sheet at the end and then just insert new month before the hidden sheet. Then use a formula with that sheet name as the end and you don't even need the dropdown
=SUM(Oct:EndSht!B6)

Test File: HowardC3dSum.xlsx

HTH

3. Hi RG

I cannot open up your attachment. I comes up with Invalid attachment specified.

4. Howard,

The following code will take the selected value of a combobox and place it in the formula as you described. The formula is in cell A5.

HTH,
Maud

Code:
```Private Sub ComboBox1_Change()
Range("A5").Formula = "=sum(Oct:" & ComboBox1 & "!B7)"
End Sub```
HC2.png

5. Hi Maud

This is perfect

I have not set up a combobox for a very long time so a bit rusty. Please explain to me how to set up the combobox in your example and linking to the months

Howard

6. Howard,

The months are on a hidden spreadsheet (xlSheetVeryHidden) called "settings" in the range A2:A12. Note the list range in the properties window for the combobox

HC3.png

7. Thanks Maud

It has now come back to me on how to set this up. I have set up the months on the same spreadsheet (called months) and linked it to the combo box and changed the properties in listfillrange to months!A1:A12

I have amended your code to account for other formulas for eg

Code:
``` Private Sub ComboBox1_Change()
Range("A5").Formula = "=sum(Oct:" & ComboBox1 & "!B7)"
Range("A6").Formula = "=sum(Oct:" & ComboBox1 & "!B8)"
End Sub```

8. Hi Maud

I have set up code to change the value based on the month selection which works fine, but have had to manually amend the cell references-see below

Is there a way to incement the cell reference by 1 in the code?

Code:
``` Private Sub ComboBox1_Change()
Range("B4").Formula = "=sum(Oct:" & ComboBox1 & "!B4)"
Range("B5").Formula = "=sum(Oct:" & ComboBox1 & "!B5)"
Range("B6").Formula = "=sum(Oct:" & ComboBox1 & "!B6)"
Range("B7").Formula = "=sum(Oct:" & ComboBox1 & "!B7)"
Range("B8").Formula = "=sum(Oct:" & ComboBox1 & "!B8)"
Range("B9").Formula = "=sum(Oct:" & ComboBox1 & "!B9)"
Range("B10").Formula = "=sum(Oct:" & ComboBox1 & "!B10)"
Range("B11").Formula = "=sum(Oct:" & ComboBox1 & "!B11)"
Range("B12").Formula = "=sum(Oct:" & ComboBox1 & "!B12)"
Range("B13").Formula = "=sum(Oct:" & ComboBox1 & "!B13)"
Range("B12").Formula = "=sum(Oct:" & ComboBox1 & "!B12)"
Range("B13").Formula = "=sum(Oct:" & ComboBox1 & "!B13)"
Range("B14").Formula = "=sum(Oct:" & ComboBox1 & "!B14)"
Range("B15").Formula = "=sum(Oct:" & ComboBox1 & "!B15)"
Range("B16").Formula = "=sum(Oct:" & ComboBox1 & "!B16)"
Range("B17").Formula = "=sum(Oct:" & ComboBox1 & "!B17)"

Range("B18").Formula = "=sum(Oct:" & ComboBox1 & "!B18)"
Range("B19").Formula = "=sum(Oct:" & ComboBox1 & "!B19)"
Range("B20").Formula = "=sum(Oct:" & ComboBox1 & "!B20)"

End Sub```

9. HI Howard

..you have repeated it twice for B12 and B13!

Try this shorter method:
Code:
```Private Sub ComboBox1_Change()
[b4] = "=sum(Oct:" & ComboBox1 & "!B4)"
temp = "b5:b20"
[b4].Copy Range(temp)
ActiveCell.Select
End Sub```
zeddy

10. Howard,

Do you may want the formulas in column A rather than column B as your formulas suggest? Aside from Zeddy's code, this will also work.

Code:
```Private Sub ComboBox1_Change()
For I = 4 To 20
Cells(I, 1).Formula = "=sum(Oct:" & ComboBox1.Value & "!B" & I & ")"
Next I
End Sub```
Change to Cells(I, 2).Formula = "=sum(Oct:" & ComboBox1.Value & "!B" & I & ")" if you want the formulas placed in column B instead

11. Howard,

I've fixed the attachment in post #2, sorry about that! HTH

Te code works perfectly

Maud the formulas must be in Column B.

13. Hi RG

No problem

14. FWIW - and note I do not recommend this as a solution, it's just for information - you can make INDIRECT work with a 3D reference in this situation. See attached.

15. ## The Following User Says Thank You to rory For This Useful Post:

RetiredGeek (2015-04-30)

16. Rory,

Tricky...Very Tricky!

You da' MAN!

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•