CODEWITHSUNDEEP
excelformula
Ingram
Ingram

Reputation: 674

Referencing a cell from another worksheet in excel

In column A of worksheet i have a list of names. These names also have a corresponding worksheet.

So for example in column A i have a cell with DP in it, there is also a tab called DP.

In column B I am trying to write a formula that looks at column A and then gets the maximum of the the range in in the worksheet.

I have tried this and a few other variations but cant get it to work

=MAX(TEXT(A2,"@")&"!&B2:B5")

So here i am looking at column A, turning it into text putting the exclamation mark which is normal for referencing another worksheeet and then the range. I have tried a few variations of this including not making the B2:B5 a reference rather than text but it seems to refer to the same worksheet rather than the worksheet i want, it therefore gives me a circular reference.

Help Appreciated

Upvotes: 0

Views: 57

Answers (1)

Mrig
Mrig

Reputation: 11702

Try this:

=MAX(INDIRECT(A2 & "!B2:B5"))

Upvotes: 1

Related Questions