Changing Cell Refrerence when copying an Indirect Formula

[howard]

I have the following Indirect formula in Cell B2

=SUBTOTAL(9,INDIRECT("'"&$A2&"'!G6:G50000"))

When I copy this formula into C2 , D2 etc , i need the last celll references to change i.e. G6:G50000 to change to H6:H50000 when copied to C2, I6:I50000 when copied to D2 etc

It would be appreciated if someone could assist me in amending my formula in order for this to change when copied to to next cell

 

[Marcelo Branco]

Assuming Z is the last column of interest (adjust) try in B2 and copy down
=SUBTOTAL(9,INDEX(INDIRECT("'"&$A2&"'!G$6:Z$50000"),0,ROWS(B$2:B2)))

Hope this helps

M.

 

[howard]

Thanks Marcelo.

when I Copy this formula which is in Cell B2 to C2 , it gives me the same value as in B2

I want to copy the formula to the right


Kindly amend your formula to accomodate the above

 

[Marcelo Branco]

Oops ... I didn't read your question carefully ...
To copy to the right, try (adjust Z)
B2
=SUBTOTAL(9,INDEX(INDIRECT("'"&$A2&"'!G$6:Z$50000"),0,COLUMNS($B2:B2)))

M.

 

[howard]

Many thanks Marcelo. Formula works perfectly

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.