Solved

Referencing 1st preceding sheet in excel

Posted on 2012-03-20
Medium Priority
194 Views
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
0
Question by:Jenedge73
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 5
• 2

LVL 35

Expert Comment

ID: 37743271
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.
0

LVL 35

Expert Comment

ID: 37743439
I have made an example, hope that's clearer, if needed.
Test.xls
0

Author Comment

ID: 37743540
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?
0

LVL 35

Expert Comment

ID: 37743602
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.
0

LVL 35

Expert Comment

ID: 37743744
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.
0

LVL 35

Accepted Solution

Robert Schutt earned 2000 total points
ID: 37743765
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...
0

Author Comment

ID: 37758251
thanks
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.