Formula for advance worksheet name to next worksheet name

[David Frank]

Worksheet1 has cell A1 equal to contents of cell E1 on worksheet2. I want to
copy that down through A50 so that A2 equals contents of E1 on worksheet3, A3
equals E1 on worksheet4 etc. I have two problems, 1) I dont know a formula
for incrementing worksheet name by one/next worksheet name. 2) E1 reference
increments to E2, E3 etc when I copy it down through A50. Any idea on a
formula for this?

 

[FSt1]

hi
make the cell reference in your sheet1 A1 formula absolute.
how?
=Sheet2!$E$1

no matter where you paste it, it will always refer to Sheet2!$E$1

regards
FSt1

 

[ShaneDevenshire]

Hi David,

If I understand your request, this will only work if the sheet names
increment by 1, in other words, this will not work if sheet2 is called
accounting and sheet3 is Admin. It will work if sheet1 is called Sheet1 and
the second sheet Sheet2 or any other incrementing name: Enter this formula
in cell A1 of Sheet1 and copy it down

=INDIRECT("Sheet"&ROW(A2)&"!$E$1")

If the sheet names can not be incremented you will need to create and use a
VBA Function.