Summing cells in Consecutive Worksheets
Francis
Sorry about the "Lawrence"<g
To install the PrevSheet function..................
With your workbook open hit Alt + F11 to go to Visual Basic Editor.
CTRL + r to open Project Explorer window.
Right-click on your workbook/project and InsertModule
Paste the prevsheet function into that module.
Alt + q to return to the Excel window.
Select sheets 2 through end by SHIFT + click
In AT8 of active sheet enter
=IF(AS8="win",1+prevsheet(AT8),prevsheet(AT8))
Ungroup the sheets.
PrevSheet(AT8) replaces the Week1!AT8 Week2!AT8 etc. etc. etc. as you asked
for.
SOME FUNCTION THAT REFERENCES PREVIOUS SHEETS,
Gord
On Tue, 14 Oct 2008 20:32:36 -0400, "Francis L. Kulchar"
wrote:
Thank you so much for your RSVP -- I am sorry to say that I am NOT at all f
amiliar with Macros, VBS, or whatever....along those lines!!!!!!!!!
What I am looking for is:
Instead of 'WEEK 1'!...I would neek something like:
'WEEK (Index(n-1))!' or SOME FUNCTION THAT REFERENCES PREVIOUS SHEETS,
ETC., ETC., ETC!!
Thanks,
FLKulchar
ncis L. Kulchar
"Gord Dibben" <gorddibbATshawDOTca wrote in message
.. .
Lawrence
If you're willing to use a User Defined Function.......
Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function
Now in place of Week1, Week2 etc. just use PrevSheet(AT8)
=IF(AS8="win",1+prevsheet(AT8),prevsheet(AT8))
Note: you will group the worksheets from 2 onwards and enter the formula
on
the activesheet.
Will be entered in all sheets except first sheet.
Gord Dibben MS Excel MVP
On Tue, 14 Oct 2008 12:28:01 -0700, F. Lawrence Kulchar
wrote:
I have the following entry in Worksheet 2, Cell AT8:
=IF(AS8="win",1+'Week 1'!AT8,'Week 1'!AT8),
....and in Worksheet 3, Cell AT8, I have:
=IF(AS8="win",1 +'Week 2'!AT8,'Week 2'!AT8)
and so on, and so on, and so on!!!!@
Basically, the formula accumulates all the "WINS" in cell AS8 as we work
our
way towards the last worksheet.
Instead of copying and pasting from Worksheet 2 to Worksheet 3, the above
formula...then CHANGING the 'Week 2(??)'! reference, etc., etc.,
Is there a way to somehow input:
'Week (n+1)'! or whatever,
thereby avoiding the necessity (obviating) to change the "Week #"
reference???
Thanks,
FLKulchar
|