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

Hi Lee,

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

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("Sh eet",CELL("filename",A1))+5,99)-1&"!ZS",0)+1

The formula above only works for sheets with indices


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Lee,

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

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

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


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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

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


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


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Lee,

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

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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秒寫é�“：


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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

Hi Lee,

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

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

please look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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秒寫é�“：
please look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional