ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Progressive number in different sheets (https://www.excelbanter.com/excel-programming/450626-progressive-number-different-sheets.html)

[email protected]

Progressive number in different sheets
 
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

Progressive number in different sheets
 
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

Claus Busch

Progressive number in different sheets
 
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

[email protected]

Progressive number in different sheets
 
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



Claus Busch

Progressive number in different sheets
 
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

[email protected]

Progressive number in different sheets
 
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


Claus Busch

Progressive number in different sheets
 
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

[email protected]

Progressive number in different sheets
 
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




All times are GMT +1. The time now is 11:47 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com