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

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

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

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