Summing cells in Consecutive Worksheets
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
|