Incremental number in Excel

[bnc123]

I have 10 sheets in my Excel workbook. In cell D5 on sheet1, I have the number 1.

If I want that number to increase by 1 in cell D5 on sheet2, what formular should I use in cell D5 in sheet2?

The obvious answer would be:

=Sheet1!D5+1

But if I copy that formula on to sheet3, cell D5 on sheet3 also reads:

=Sheet1!D5+1

instead of reading:

=Sheet2!D5+1

So, is there a way of referencing cells in 'previous' sheets, so that the number will increase by 1 in cell D5 of all subsequent sheets?

 

[byundt]

The most general way to do it is with a user-defined function such as posted by John Walkenbach

http://j-walk.com/ss/excel/tips/tip63.htm

If your worksheets are named Sheet1, Sheet2, Sheet3, etc. then you can use this worksheet formula:
=INDIRECT("Sheet" & (MID(CELL("filename",A1),SEARCH("sheet",CELL("Filename",A1))+5,9)-1) & "!A1")+1

Brad

 

[bnc123]

Well Brad,

I am not a very advanced Excel user. So, I replaced 'filename' with the actual filename, 'Sheet' with 'Sheet1' and 'A1' with 'D5' in your suggestion above and pasted it on Sheet2 of my workbook.

Nothing happened. Infact the cell turned into '#value'.

So, can you please be a bit more specific. I have been to the link you provided and I must say, I don't understand what's going on over there either.

Thanks in advance.

 

[byundt]

If your incrementing number is located in cell D5, please paste exactly the following formula in Sheet2 cell D5 (and subsequent worksheets cell D5):
=INDIRECT("Sheet" & (MID(CELL("filename",D5),SEARCH("sheet",CELL("filename",D5))+5,9)-1) & "!D5")+1

The first two references to cell D5 could be to any cell on the worksheet. Only the last reference actually needs to be changed--but it is probably less confusing if you change all three.

Please do not change the references to "Sheet", "filename" and sheet. The reference to "Sheet" is to the beginning of the worksheet name (the formula assumes they are named Sheet1, Sheet2, Sheet3, etc). The reference to "filename" is a keyword telling the CELL function what type of information to return--the path, filename & worksheet name. The reference to "sheet" is to tell the FIND function what information to look for in the info returned by CELL.

Brad

 

[bnc123]

Thanks Brad,

It worked.

Ofcourse I'll have to go through your explanation a few times before I can get a grasp on it, but the imnportant thing is that it worked for now.

Thanks again and have a nice day.