Solved

Referencing 1st preceding sheet in excel

Posted on 2012-03-20
7
185 Views
Last Modified: 2012-03-27
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
Comment
Question by:Jenedge73
  • 5
  • 2
7 Comments
 
LVL 35

Expert Comment

by:Robert Schutt
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

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

Author Comment

by:Jenedge73
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 35

Expert Comment

by:Robert Schutt
ID: 37743602
is this what you mean:

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

Open in new window


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

by:Robert Schutt
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)

Open in new window


Then in A26:

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

Open in new window


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

Open in new window


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

by:
Robert Schutt earned 500 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))

Open in new window


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

Author Comment

by:Jenedge73
ID: 37758251
thanks
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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!
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question