1

How can I use the content in a cell in a separate formula?

I have in cell A1 the following content: $BA$10:$BA$150 This content will change on a regular basis. So next time it could be: $BZ$10:$BZ$150.

In a different cell, same sheet, I need to use a SUMIFS function:

=SUMIFS(Sheet1!$BA$10:$BA$150,Sheet1!$N$10:$N$150,'Summary '!X$13)

I would like in this formula instead of writing $BA$10:$BA$150 I want to use the content in cell A1. I tried to use the indirect function but it didn't work:

=SUMIFS(Sheet1!&INDIRECT(A1),Sheet1!$N$10:$N$150,'Summary '!X$13))

Any ideas please?

pnuts
  • 58,317
  • 11
  • 87
  • 139
n4pster
  • 103
  • 1
  • 10

1 Answers1

1

Try applying:

 INDIRECT("Sheet1!"&A1)

(The trailing space in the sheet name Summary seems just a nuisance.)

I would agree the official documentation could be clearer in this respect.
(...but there are many examples on SO!)

(Good example why show what you've tried is useful.)

pnuts
  • 58,317
  • 11
  • 87
  • 139