How to create an indirect reference to multiple named ranges

Can you supply a sample workbook?

same here, please

Try this little workbook. Click the blue button.
29135421.xlsm

Apologies for editing the question thinking I had no comments. Will review answers today

Martin this is very cool! but I have no idea how it works or exactly what it does. When I choose B&C which should add up to 5, the number 15 shows up in the first cell in tab A. Your userform is beautifully done.

Subodh, this uses the same technique I think, and it clearly adds up the chosen tabs, but:

Thank you both, this is nearly there.
My array (equivalent to your column of numbers in tab A) is a full P&L and Balance sheet, so 12 months x 3 years (36 columns) by about 50 rows. It's identical on each tab so we can add the named ranges. So basically your solutions are the answer, I just need a little more explanation so I can replicate into my own spreadsheet so:

Same questions to both:
1. Where is the code and how do I make it work?

2. How do I check it's calculating right? If I use an indirect function to reference a cell I can see it written out: "name_range_tab_B+name_range_tab_c" and so on. How do I check what has been added up to make the sum?


Thank you

OK, I figure out Martin's: you're adding the tabs selected to the number already there.
I would need it to replace the numbers with the sum of the sheets selected. Thanks

sounds like a pivot table to me...

Perfect Martin, but I still need to know how it works and where the code is else I can't adapt it to my sheets...

Go to Visual Basic via Alt+f11. When you are there look in Module1 and you'll find the ShowForm macro which opens the userform named frmTabs and it's that userform that does all the work. To put the userform in your workbook then while in Visual Basic go to the menu and select File->Export... and select frmTabs. Then in your workbook do the opposite and File->Import... that file. In that form you'll find this code:

Private Sub CommandButton1_Click()
ActiveSheet.UsedRange.Cells.Offset(1, 0).ClearContents

If chkB = True Then
    AddToA Sheets("B")
End If
If chkC = True Then
    AddToA Sheets("C")
End If
If chkD = True Then
    AddToA Sheets("D")
End If

Unload Me
End Sub
Private Sub AddToA(ws As Worksheet)
Dim lngLastRow As Long
Dim cel As Range
Dim wsA As Worksheet

Set wsA = Sheets("A")
For Each cel In ws.UsedRange.Cells
    If IsNumeric(cel) Then
        wsA.Range(cel.Address) = wsA.Range(cel.Address) + cel
    End If
Next
    
End Sub

Select allOpen in new window

If you have any problems understanding what happens when you click OK button (CommandButton1) let me know.

Thank you Martin, that's very helpful. It's all coming together.
One last question: how do I define the cells in A that are going to have the process applied? In my model there are a dozen or more different blocks that will be changed and other blocks that are formulas or static numbers.

For example if in your example I wanted the macro to apply the summing to A2:A3 and A6:A7?

There are three ways that I can think of to do that.
1) Require that the user manually select the area(s) like this
2) Provide checkmark toggles that the user can turn off and on by clicking the cells like this
3) Add pre-defined ranges that the user can select on the userform like this

If you want to do either method 2 or 3 then please close this question and ask a new one since I believe I've answered your original question.

Thank you Martin and Subodh
I am opening another question shortly to deal with the final stage of specific ranges on each tab

The follow-on question is at https://www.experts-exchange.com/questions/29135628/Add-specific-ranges-to-macro-summing-named-ranges-from-multiple-tabs.html