Formula Help

[Mainland]

Good Morning Guys,

I'm hoping someone will be able to help. I am looking to adjust the formula below so that when when I drag and fill the cell containing the formula to duplicate, it automatically increases the sheet number e.g sheet1, sheet2, sheet 3 but doesn't change the references to particular cells.

=Sheet1!R14C2

[Jonmo1]

Are the sheets literally named Sheet1 Sheet2, or do they have more meaningful names ?
And will you be 'dragging' the formula Up/Down or Left/Right ?

[Mainland]

Hi Jonmo,

Thank you for your quick reply. The sheets are all named sheet1, sheet2 etc. I will be dragging them down.

[Mainland]

It is Excel 2016 if that makes a difference

[Jonmo1]

Try

=INDIRECT("Sheet"&ROWS(R1C1:RC1)&"!R14C2",FALSE)

[Mainland]

Doesn't seem to work. Just says REF

[Jonmo1]

ok, I was going by the original formula you posted
=Sheet1!R14C2

That's written in R1C1 style, so I assumed your Excel is using R1C1 style instead of A1 style.
Is that not correct?

If you're using A1 style, try
=INDIRECT("Sheet"&ROWS(A$1:A1)&"!B14")

[Mainland]

Yes the sheet is in R1C1 style. I am assuming i should be able to insert your formula anywhere on the sheet and it should extrapolate the correct information to that cell?

[Mainland]

I think the problem is that if we rename the Sheet (i.e. call Sheet 1, Test Sheet 1 it doesn't like it). Is there a way to adjust the formula so we can change the sheet name without causing an error?

[Jonmo1]

That's why I asked if the the sheets were literally named Sheet1 Sheet2
You said YES.

No, you'd have to manually update the formula if you change the sheetname after the formula is created.

I would recommend renaming your sheets as they need to be first.
THEN work on this indirect formula.

[Jonmo1]

If you can maintain a list of sheetnames in a range, say Z2:Z5 or whatever
Z2 = Sheet1
Z3 = Sheet2
Z4 = SomeOtherSheetName
etc.

Then you can do
=INDIRECT("'"&Z2&"'!R12C2",FALSE)

[Richard Buttrey]

Chaps, chaps!

Remember Forum Rule 1 please.

@mainland. Please edit post #1 and change the subject header.

[Mainland]

Thanks for your help. I think I've got it.

[Richard Buttrey]

Thanks for your help. I think I've got it.

....and the subject header????