0

I have 28 different tabs representing weekly data. They are titled W1, W2, W3 .... all the way up to W28. They are identically laid out just different data.

On a separate tab I am trying to SUM some fields from each week.

That tab will look like so

Week - Wins
1    - 4
2    - 2
3    - 3

the static formula for the first row in the win column would be: =SUM('W1'!AW2:AW7)

But I want to make it dynamically reference the sheet name. Let's assume Week # is column C and they start in row 4. This formula seems to return the proper sheet names:

="'W"&INDIRECT("C4:C31")&"'" (Gives me 'W1', 'W2', 'W3', etc in the corresponding wins column)

But I am unable to combine it with the above SUM formula to dynamically reference the sheet name.

=SUM("'W"&INDIRECT("C4:C31")&"'"&!AW2:AW7) -> Formula Parse Error =SUM('W&INDIRECT("C4:C31")&'!AW2:AW7) -> Unresolved Sheet Name W&INDIRECT("C4:C31")&

I've tried other variations with no luck so far.

Any suggestions? Thanks

LaravelNewbie
  • 13
  • 1
  • 3

1 Answers1

1

example:

enter image description here

enter image description here

formula:

=SUMPRODUCT(REDUCE(, SEQUENCE(28), LAMBDA(a, b, {a; IFERROR(IMPORTRANGE(
 "1ToGglifTsqotZ0jDthW2rojWbIXRYPvKUHpdJVzpZ_I", "W"&b&"!B2:B5"))})))

enter image description here

or:

=SUM(REDUCE(, SEQUENCE(28), LAMBDA(a, b, 
 {a; IFERROR(INDIRECT("W"&b&"!B2:B5"))})))

enter image description here

update:

=QUERY(REDUCE(, SEQUENCE(28), LAMBDA(a, b, 
 {a; SUM(IFERROR(INDIRECT("W"&b&"!B2:B5")))})), "offset 1", )

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • That looks like it totals all sheets together. I want each weeks row to title that weeks sheet's range. So the summary she would list total wins per week. – LaravelNewbie Nov 17 '22 at 23:42
  • so above example would have a third page that had 2 columns, Week #s and corresponding SUM totals (1,2 with 9,6) Sorry not sure how to format comments to better show that visually – LaravelNewbie Nov 17 '22 at 23:50
  • but I'll take a look at SEQUENCE, REDUCE and LAMBDA and see how they work. Thx – LaravelNewbie Nov 17 '22 at 23:50
  • 1
    Your update worked perfectly. Thank you so very much. I appreciate your time and knowledge. – LaravelNewbie Nov 18 '22 at 00:43