Solved

Referencing 1st preceding sheet in excel

Posted on 2012-03-20
7
183 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
Comment Utility
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
Comment Utility
I have made an example, hope that's clearer, if needed.
Test.xls
0
 

Author Comment

by:Jenedge73
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 35

Expert Comment

by:Robert Schutt
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
thanks
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Outlook Free & Paid Tools
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now