Calculaion based on Previous Sheet

[kishan]

hi, i need some help with a particular function i need.
basically i need a function that adds 1 to the number on
the previous sheet in the cell B1. For example if i have
12 in B1 on sheet 1 :: then in sheet two it would display
13. i have going to be making a lot of copied of this
sheet and so i don't want to type in the formulas myself.
can someone help me. also i was planning on excluding one
sheet from this formula - this would be the first sheet
that starts the numbers.

 

[Don Guillett]

try this for row 3 and below and column 1
right click sheet tab>view code>insert this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 3 Or Target.Column <> 1 Then Exit Sub
Sheets(ActiveSheet.Index - 1).Range("b1") = 1 + Target
End Sub

 

[Guest]

i am not sure how i can call this function. can u give me
the exact details. i inserted the code into the sheet
like you said. but how do i get it to work

 

[kishan]

hi thanks for the reply. i inserted the code like you
said, but then what do i have to do. how do i call the
function. can u please give some more details on how to
use the code. thanks

 

[Don Guillett]

For the sheet where you inserted it. If you put a number in a cell below row
2 and in column A., cell b1 of the previous sheet will have that number +1.
Is that what you wanted?

 

[David]

Don Guillett wrote

Sheets(ActiveSheet.Index - 1).Range("b1") = 1 + Target

Do you know any benefit/drawback of using that or this:
ActiveSheet.Previous.Range("b1") = 1 + Target

Just curious

 

[Don Guillett]

Should be the same.