# Referencing 1st preceding sheet in excel

Is there a way I can reference a cell in the preceding sheet of a workbook so that when I  copy that worksheet to the end of the workbook the reference will hold to the next last sheet. I would like to use a function, however if I have to I could use a macro
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Software EngineerCommented:
As there is already a solution for this, I assume it's not possible in a formula.

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

Of course you could change this to always use an offset of -1 and maybe other defaults you want to use like look at the same cell in that sheet.

If you need help implementing that, let me know.
Software EngineerCommented:
I have made an example, hope that's clearer, if needed.
Test.xls
Author Commented:
Could i use an indirect function and change the sheet name to reflect ie a 1 week difference based on let's say a 52 week year?
Software EngineerCommented:
is this what you mean:

``````=INDIRECT(ADDRESS(ROW(),COLUMN(),,,"week"&(WEEKNUM(DATEVALUE("2012-03-20"))-1)))
``````

This references the current cell in the "week11" sheet, since weeknum for today is 12.

The literal date can be replaced by a cell ref, or a call to TODAY() if that's what you need.
Software EngineerCommented:
It can apparently also be done with a formula that gets the full reference to a cell, for example in cell A25 I've put:

``````=CELL("filename",A1)
``````

Then in A26:

``````=MID(A25,FIND("]",A25)+1,LEN(A25)-FIND("]",A25))
``````

This gets the sheet name which in this case was: week12

Then in cell A27:

``````=LEFT(A26,4)&INT(MID(A26,5,LEN(A26)-4))-1
``````

That returns the previous week, so now you can use that cell as the 5th argument to the ADDRESS call. Maybe that's more applicable to your situation? It's a pain with the extra cells though, you could write each reference out of course but it might become painfully slow, I don't really know.
Software EngineerCommented:
Just for fun, here it is: reference the current cell in the preceding sheet if your sheets are calle week11, week12 etc:

``````=INDIRECT(ADDRESS(ROW(),COLUMN(),,,LEFT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))),4)&INT(MID(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))),5,LEN(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))))-4))-1))
``````

Note that I can't really recommend this...

Experts Exchange Solution brought to you by