how to drag horizontally a formula which is a stri

问题:

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?

回答1:

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

回答2:

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.