1

On sheet1 A1 is a cell with dropdown where you can choose a tile.

This title at times may or may not come up in sheet2:sheet7 with 30 rows of data below it.

I would like when I change the tile on sheet1 in A1, the sheet finds the matching title in all sheets 2-7 and sums all related data points in the 30 rows below from the other sheets.

For example:

If "Sheet1 A1" is selected "Title 1" from the dropdown, "Sheet1 A2:A31" is the sum of all "A2:A31" cells with matching "A1's" in Sheets 2-7. Note that in Sheets 2-7 the matching data may not be in column A and could be anywhere on the sheet. If "Title 1" does not find a match, value is 0

Improve this question
4
  • 1
    What have you tried so far and where are you stuck? It seems like an IFS() function would do it for you...
    – gns100
    Commented Aug 19, 2021 at 0:00
  • Hi @Ryan Corby ,, getting SUM/AVERAGE and others across sheets are popular practice but works only by using single formula,,, if & when data illustration is in similar columns,,,so please edit your post & modify the need,, if your need is fixed that you get SUM from different data points then please be specific,,, also use TAG VBA to the post, cozz VBA macro can meet your requirements!!
    – Rajesh Sinha
    Commented Aug 19, 2021 at 5:13
  • Readers please do not VOTE negative to CLOSE this post since it's a challenging question to solve,,, is different,,, and this might be situation in day 2 day working,,, this community has many experts,,, so keep it alive and try to find solution,,, I'm also trying to fix it ☺
    – Rajesh Sinha
    Commented Aug 19, 2021 at 5:21
  • If you can provide a sample about this problem, it will be more helpful.
    – Lee
    Commented Aug 19, 2021 at 8:14

1 Answer 1

Reset to default
0

Combination of SUMIF & INDIRECT wrapped with SUMPRODUCT solves the issue:

:Caveat:

  • Since OP has not shared any data set/screen shot, therefore I've assumed data.
  • For proper understanding & visualization I've used smaller data set.
  • My solution comprises standard method, to SUM value across Sheets, and are with and without Drop down.
  • Possibly, others will post a better method.
  • Also, for many sheets & larger data set, the VBA should be a good option.

enter image description here

enter image description here

enter image description here

How it works:

  1. Enter Sheet Name in Range B31:B31.

  2. First Formula in cell C38:

    =SUMPRODUCT((SUMIF(INDIRECT("'"&$B$31:$B$32&"'!"&"$b$61:$b$64"),B38,INDIRECT("'"&$B$31:$B$32&"'!"&"$c$61:$c$64"))+SUMIF(INDIRECT("'"&$B$31:$B$32&"'!"&"$s$2:$s$5"),B38,INDIRECT("'"&$B$31:$B$32&"'!"&"$t$2:$t$5"))))

  1. Create Drop Down in cell E38:

  2. Formula in cell G38:

     =SUMPRODUCT((SUMIF(INDIRECT("'"&$B$31:$B$32&"'!"&"$b$61:$b$64"),$E$38,INDIRECT("'"&$B$31:$B$32&"'!"&"$c$61:$c$64"))+SUMIF(INDIRECT("'"&$B$31:$B$32&"'!"&"$s$2:$s$5"),$E$38,INDIRECT("'"&$B$31:$B$32&"'!"&"$t$2:$t$5"))))

N.B.

  • Adjust cell references in the formula as needed.
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .