Changing only a Sheet Name in a formula in copy/paste

[notanexpert]

if a formula is:
='Game 1'!$C$6

and I want to drag that down in to several other rows, but i want it to game the game number from 1 to 2 to 3 etc... on down the list while leaving the column and row alone

could it be accomplished dragging it into different columns (horizontally) as well?

 

[Weaver]

I think you'd have to use INDIRECT() to achieve this.

create another column and enter Game 1 in the first cell. Say we use A2:A10

Then in B2

=INDIRECT("'" & A2 & "'!$C$6")

and copy down

HTH

 

[notanexpert]

I got to be honest, i am not sure i exactly understand what your formula is doing but it is working and i think i can get it to do what i want for now.

thanks a million.