Progressive number in different sheets

[leeingjei]

Hi,

I am trying to have progressive numer in differnt sheets of the same
cell by copying. Can anybody give me a clue? Thanks.

For example, there are three working sheets as below

A1=400 A1=401 A1=402
sheet 1 sheet 2 sheet 3

the A1 of 'sheet 2'! should be 'sheet 1'!A1 +1
and A1 of 'sheet 3'! should be 'sheet 2'!A1 +1

What the formula could be to achieve the above? Thanks.

Lee

 

[Claus Busch]

Hi Lee,

Am Sat, 31 Jan 2015 02:54:10 -0800 (PST) schrieb (e-mail address removed):

A1=400 A1=401 A1=402
sheet 1 sheet 2 sheet 3

select Sheet2 to Sheetn and write in A1:
=INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("Sheet",CELL("filename",A1))+5,99)-1&"!ZS",0)+1

The formula above only works for sheets with indices


Regards
Claus B.

 

[Claus Busch]

Hi Lee,

Am Sat, 31 Jan 2015 12:30:07 +0100 schrieb Claus Busch:

=INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("Sheet",CELL("filename",A1))+5,99)-1&"!ZS",0)+1

sorry, my translation was wrong.
Try:
=INDIRECT("Sheet"&MID(CELL("filename",A1),FIND("Sheet",CELL("filename",A1))+5,99)-1&"!RC",0)+1


Regards
Claus B.

 

[leeingjei]

Claus Buschæ–¼ 2015å¹´1月31日星期六 UTC+8下åˆ9時15分40秒寫é“：

Thanks Claus, sorry to botther you again how to create indices for these sheets?

 

[Claus Busch]

Hi Lee,

Am Sat, 31 Jan 2015 06:12:23 -0800 (PST) schrieb (e-mail address removed):

Thanks Claus, sorry to botther you again how to create indices for these sheets?

if your sheets are named Sheet1, Sheet2 and so on your sheets have
indices. Or you have another name with progressive numbers behind.
If not, you better use VBA.


Regards
Claus B.

 

[leeingjei]

I did as you instructed but the A1 cell of all other sheets becoming "#VALUE!"
I save this file as test.xlsx as uploaded to:

https://dl.dropboxusercontent.com/u/4272700/test.xlsx

Can you check and let me know where the mistake is or correct it then upload to somewhere I can download? thanks.


Claus Buschæ–¼ 2015å¹´1月31日星期六 UTC+8下åˆ10時16分37秒寫é“：

 

[Claus Busch]

Hi Lee,

Am Sat, 31 Jan 2015 06:42:03 -0800 (PST) schrieb (e-mail address removed):

Can you check and let me know where the mistake is or correct it then upload to somewhere I can download? thanks.

please look here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "test"
There are to formulas in each sheet and I hope that they will be
translated correctly when you open the file on your system.


Regards
Claus B.

 

[leeingjei]

Yeah, it works. I ranslated the German one into English one as instructed and open the file on my system, it shows exactly what I want. Thanks, Claus!!

Claus Buschæ–¼ 2015å¹´1月31日星期六 UTC+8下åˆ10時56分32秒寫é“：