Help with excel formula......Please

[Fatpigeon]

Hi guys, i'm hoping one of you can help me (im a bit of a noob). Im trying to get a running total working in excel but not having much luck. What i would like it to do is when i put a value in cell C7 on sheet 1, it would add it to a running total in cell C7 on sheet 2, is this even possible. I've tried google but can only find formulas that work on the same sheet.

Any help would be great

Thanks in advance

 

[james.miller]

It needs a sheet reference. ie, if a formular was A1+B1, it would become sheet1!A1+B1 (the exlamation mark means its a sheet reference. obviously replace 'sheet1' with the name of the sheet you want to do the calculations on)

there's two ways really, put the formular(s) on the main sheet and then select, cut, and paste the lot on to the second sheet. excel would then add the sheet name references for you.

or you can select the cells on sheet one when you are entering the formular. ie, select cell b1 on sheet 2, type in = then select each cell you want in the formular on sheet 1. Again, excel will do whats needed

 

[Fatpigeon]

Thanks for that, but whenever i edit any other cell on sheet1, the value in C7 gets added to sheet 2 again, if that makes sense.

 

[Namsnik]

If you mean:

type 3 into Sheet1!C7
Sheet2!A1=3
type 4 into Sheet1!C7
Sheet2!A1=7
type 5 into Sheet1!C7
Sheet2!A1=12​

Then this is not possible without macros (VBA).

If you mean:

type 3 into Sheet1!C7
Sheet2!A1=3
type 4 into Sheet1!C8
Sheet2!A1=7
type 5 into Sheet1!C9
Sheet2!A1=12​

Then this is possible

 

[Fatpigeon]

If you mean:

type 3 into Sheet1!C7
Sheet2!A1=3
type 4 into Sheet1!C7
Sheet2!A1=7
type 5 into Sheet1!C7
Sheet2!A1=12​

Then this is not possible without macros (VBA).

Thats what I meant, I found a VBA code for doing it with 2 cells on the same sheet, but cant find anything for using 2 sheets.

 

[Namsnik]

Post the code and so we can tweak it for you.

 

[Namsnik]

By the way, probably the easiest way to refer to cells in different worksheets is to use named ranges.

If Sheet2!c7 is a named range "myRange" then =1+myRange will work on any sheet in the workbook

 

[Namsnik]

Guess you've got better things to do on a Saturday night!
This code will work (based on named ranges)

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("myNumber")) Is Nothing Then
    With Application
        .EnableEvents = False
        Range("RunTot").Value = Range("RunTot").Value + Range("myNumber").Value
        .EnableEvents = True
    End With
End If
End Sub

Right click on the tab for sheet1 select "view code" and put the code there.

 

[Fatpigeon]

Thanks for doing the code for me, i'll give it a try when i get to work tomorrow.

 

[Fatpigeon]

That seems to have done the trick, thanks a lot for your help.

 

[Namsnik]

No problem - glad to help.