Results 1 to 15 of 20

20150428, 13:55 #1
 Join Date
 Feb 2008
 Posts
 1,515
 Thanks
 137
 Thanked 8 Times in 8 Posts
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: ....)Last edited by RetiredGeek; 20150428 at 15:02. Reason: Added NoParse tags

20150428, 15:30 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,838
 Thanks
 411
 Thanked 1,570 Times in 1,421 Posts
Howard,
The Indirect function does NOT support 3D (multisheet 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
HTHLast edited by RetiredGeek; 20150429 at 08:07.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150428, 20:57 #3
 Join Date
 Feb 2008
 Posts
 1,515
 Thanks
 137
 Thanked 8 Times in 8 Posts
Hi RG
Thanks for your input
I cannot open up your attachment. I comes up with Invalid attachment specified.

20150428, 21:08 #4
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,852
 Thanks
 146
 Thanked 724 Times in 658 Posts
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

20150428, 21:43 #5
 Join Date
 Feb 2008
 Posts
 1,515
 Thanks
 137
 Thanked 8 Times in 8 Posts
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

20150428, 22:44 #6
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,852
 Thanks
 146
 Thanked 724 Times in 658 Posts
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

20150428, 23:34 #7
 Join Date
 Feb 2008
 Posts
 1,515
 Thanks
 137
 Thanked 8 Times in 8 Posts
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

20150429, 01:40 #8
 Join Date
 Feb 2008
 Posts
 1,515
 Thanks
 137
 Thanked 8 Times in 8 Posts
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 referencessee 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

20150429, 03:28 #9
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,233
 Thanks
 154
 Thanked 609 Times in 579 Posts
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

20150429, 04:40 #10
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,852
 Thanks
 146
 Thanked 724 Times in 658 Posts
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
Last edited by Maudibe; 20150429 at 04:44.

20150429, 08:07 #11
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,838
 Thanks
 411
 Thanked 1,570 Times in 1,421 Posts
Howard,
I've fixed the attachment in post #2, sorry about that! HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150429, 08:09 #12
 Join Date
 Feb 2008
 Posts
 1,515
 Thanks
 137
 Thanked 8 Times in 8 Posts
Thanks Zeddy & Madibe for your input
Te code works perfectly
Maud the formulas must be in Column B.

20150429, 08:11 #13
 Join Date
 Feb 2008
 Posts
 1,515
 Thanks
 137
 Thanked 8 Times in 8 Posts
Hi RG
No problem

20150430, 05:57 #14
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,322
 Thanks
 3
 Thanked 214 Times in 197 Posts
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.
Regards,
Rory
Microsoft MVP  Excel

The Following User Says Thank You to rory For This Useful Post:
RetiredGeek (20150430)

20150430, 08:41 #15
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,838
 Thanks
 411
 Thanked 1,570 Times in 1,421 Posts
Rory,
Tricky...Very Tricky!
You da' MAN!May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs