How to change Sheet Name dynamically in a formula?

[krishco68]

Dear Expert,
I have a problem, I have 30 sheets which are named from 1 to 30. In another sheet, I mentioned a formula taking a reference of 1 sheet and when I drag the formula the name of the sheet should change automatically.
Ex: in a cell the formula is - ='1'!$J$1
and when I drag the formula the name of the sheet has to change to ='2'!$J$1, likewise for the entire 30 sheets.

Please help me in this regard.

Regards,
Murali Krishna

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what cell are you putting that formula in?

 

[krishco68]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what cell are you putting that formula in?

Ok Sir, I will update the account details.
now about the problem.
I have 30 sheets named from 1 to 30
and in 31 sheet in cell A1 I took a reference cell
Ex. A1 = '1'!$J$1 then when I drag the formula down
the formula should change automatically like A2 = '2'!$J$1

 

[Fluff]

Ok, how about

=INDIRECT("'"&ROWS(A$1:A1)&"'!J1")

 

[Georgiboy]

Similar option:

=INDIRECT("'"&ROW()&"'!J1")

 

[krishco68]

Ok, how about

=INDIRECT("'"&ROWS(A$1:A1)&"'!J1")

Sir,
It is working. Thank you very much. So nice of you.
Regards,
Murali

 

[Fluff]

Glad we could help & thanks for the feedback.

Also please don't forget to update your profile. It saves us from having to ask.