About to blow my brains out....

  • Thread starter Thread starter Race1530
  • Start date Start date
R

Race1530

because of excel. As a long time Lotus user, I am dumbfounded by some aspects
of Excel. My problem is this.

In sheet 12 cell b1 i want to put a formula that will add B1 from sheet 11 and
add 1 to that. Looks like this =Sheet11!b1+1 this gives me the correct answer
(I have a date in the first reference and want it to pick up the next day -
example 10/22/2004 + 1 = 10/23/2004.

Now I want to copy this to all 255 sheets. But the answer is static as I
assume the ! is making the formula an "absolute". What the heck do I do to get
what I want? In all the excel help that I can find they only reference
addition of two cells in the same worksheet.

Man...Lotus was so easy....

Thanks for any help,
Michael
 
Michael,

These 2 simple UDFs might help.

Function NextSheet(Optional rng As String)
Dim tmp
With ActiveSheet
If .Index < Worksheets.Count Then
tmp = ActiveSheet.Index + 1
Else
NextSheet = CVErr(xlErrValue)
Exit Function
End If
End With
If Not IsEmpty(rng) Then
NextSheet = Worksheets(tmp).Range(rng).Value
Else
NextSheet = Worksheets(tmp).Name
End If
End Function

Function PrevSheet(Optional rng As String)
Dim tmp
With ActiveSheet
If .Index > 1 Then
tmp = ActiveSheet.Index - 1
Else
PrevSheet = CVErr(xlErrValue)
Exit Function
End If
End With
If Not IsEmpty(rng) Then
PrevSheet = Worksheets(tmp).Range(rng).Value
Else
PrevSheet = Worksheets(tmp).Name
End If

End Function



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top