CODEWITHSUNDEEP
excelformula
Ingram
Ingram

Reputation: 674

how to drag horizontally a formula which is a string fixed

I have the following formula in excel

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

A2 is a cell in the current worksheet, which is the name of a worksheet tab. However what i wish to do is drag the formula horizontally like that of standard excel formulas to reference the cells of the "other" worksheet tab

So if i dragged horizontally i would get:

=MAX(INDIRECT($A2 & "!"&"C2:C5"))

=MAX(INDIRECT($A2 & "!"&"D2:D5"))

etc

This wont work with the formulas as a fixed text..so how would i do this?

Upvotes: 0

Views: 138

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34255

Another way is

=MAX(INDIRECT($A2 & "!"&ADDRESS(ROW(),COLUMN())&":"&ADDRESS(ROW()+3,COLUMN())))

if you want the range to start in the same row and column on the second sheet and finish three rows down.

Upvotes: 1

Mrig
Mrig

Reputation: 11702

This will solve your problem. Though not sure whether this is the best solution.

=MAX(INDIRECT($A2 & "!" & CHAR(COLUMN()+64) & "2:" & CHAR(COLUMN()+64) & "5"))

Upvotes: 1

Related Questions