SUMIF using Worksheet Tab Names

[Mobius Loop]

Hello -

Can anyone help with the following:

I have a workbook which may contain any number of worksheets, but each worksheet is identical. For my example, Options 1, 2, 3, 4, & 5. Each worksheet has various data (values) in say cells A10:A20, which I need to add together based on user defined "scenarios," which could be any combination of the worksheets. For example, I may need to combine Options 1, 3 & 5 together, or Options 1 & 4, or all five Options, etc ...). However, generally, I don't need to combine more than 5 options. Per below, in the summary sheet, my hope is the user could type the "desired tab name" in Cells A1:A5, and use a 3D sum to select the correct worksheets and return the values. Any ideas?

a1 = Option1
a2 = Option3
a3 = Option5
a4 = Blank
a5 = Blank

a10 = sum(Option1!a10,Option3!a10,Option5!a10). However, if any of the cell a1:a5 were added, subtracted or changed, this formula would atomically adjust.

Many Thanks!

 

[tusharm]

Interesting idea and the UI that I would use is somewhat different...and IMO user friendlier...

Suppose the cells of interest are C3:E6 on worksheets Sheet2 and Sheet3.

Suppose you want the summary data on Sheet1.

Then, on Sheet1, create 2 checkboxes (Developer tab | Controls group | Insert dropdown > Forms Control section | Checkbox)
Change the display names on the 2 checkboxes to Sheet2 and Sheet3.
Connect the 2 checkboxes to cells B3 and B4 respectively (right click each checkbox, select Format Control... | in resulting dialog box select Control tab | specify the appropriate Cell link).
Align the 2 checkboxes so that one overlays A3 the other A4.

Now, in C3:E6 array enter the formula =B3*Sheet2!C3:E6+B4*Sheet3!C3:E6

To array enter a formula complete data entry with the CTRL+SHIFT+ENTER key combination rather than just the ENTER or TAB key. If done correctly, *Excel* will show the formula enclosed in curly brackets { and }

Check (or uncheck) each checkbox and watch the result change.

Quite flexible and easily extended to any number of sheets.

Hello -

Can anyone help with the following:

I have a workbook which may contain any number of worksheets, but each worksheet is identical. For my example, Options 1, 2, 3, 4, & 5. Each worksheet has various data (values) in say cells A10:A20, which I need to add together based on user defined "scenarios," which could be any combination of the worksheets. For example, I may need to combine Options 1, 3 & 5 together, or Options 1 & 4, or all five Options, etc ...). However, generally, I don't need to combine more than 5 options. Per below, in the summary sheet, my hope is the user could type the "desired tab name" in Cells A1:A5, and use a 3D sum to select the correct worksheets and return the values. Any ideas?

a1 = Option1
a2 = Option3
a3 = Option5
a4 = Blank
a5 = Blank

a10 = sum(Option1!a10,Option3!a10,Option5!a10). However, if any of the cell a1:a5 were added, subtracted or changed, this formula would atomically adjust.

Many Thanks!

 

[myall_blues]

How about:

=SUM(IFERROR(INDIRECT(A1&"!A10",TRUE),0),IFERROR(INDIRECT(A2&"!A10",TRUE),0),IFERROR(INDIRECT(A3&"!A10",TRUE),0),IFERROR(INDIRECT(A4&"!A10",TRUE),0),IFERROR(INDIRECT(A5&"!A10",TRUE),0))

Regards

Murray

 

[Mobius Loop]

Thanks so much. I had not considered the checkbox as a solution and love the simplicity. However, it has caused a couple of issues:

1) Other than recording a macro, is there any way to automatically add a checkbox when or if a worksheet is added?
2) The number and names of the worksheet tabs will vary from workbook to workbook. Is there a way to have the name of the checkbox automatically update when the worksheet name is changed?

Again, many thanks for your help!