CODEWITHSUNDEEP
excelexcel-formulareferenceexcel-indirect
n4pster
n4pster

Reputation: 103

Dynamic reference within Excel formula

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?

Upvotes: 1

Views: 64

Answers (1)

pnuts
pnuts

Reputation: 59495

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.)

Upvotes: 1

Related Questions