Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
Excel 2000 Hiya, I have a question for all you brainboxes out there! Im VERY new to excel so please explain clearly or I'll be completley lost!! In the cell A3 I would like to have a "Grand Total". In the cell A2, I would like to enter a number, eg. 400 (this would then appear in A3 as 400), THEN I would like to enter another number, eg. 307 in cell A2. (this wouuld then appear in A3 as 707). I hope that explains it, I find it a bit confusing. Thanks for any help you can offer :) -- @---}-- Laura..... :) Liverpool, England |
#2
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
you would need for example
A1 307 A2 400 A3 total 707 If you put the total in say A10 then every `number` from A1 to A9 would be in the total "Laura ( '_' )" wrote in message ... Excel 2000 Hiya, I have a question for all you brainboxes out there! Im VERY new to excel so please explain clearly or I'll be completley lost!! In the cell A3 I would like to have a "Grand Total". In the cell A2, I would like to enter a number, eg. 400 (this would then appear in A3 as 400), THEN I would like to enter another number, eg. 307 in cell A2. (this wouuld then appear in A3 as 707). I hope that explains it, I find it a bit confusing. Thanks for any help you can offer :) -- @---}-- Laura..... :) Liverpool, England |
#3
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
Perhaps better to simply use an adjacent top cell
to total up successive inputs down col A ? Put in say, B1: =SUM(A:A) B1 will return the total of the numbers entered down col A If you really want to accumulate by inputting repeatedly into A2, try JE McGimpsey's page at: http://www.mcgimpsey.com/excel/accumulator.html Look for: - Single cell accumulator - Worksheet Function Accumulator (using Circular References) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Laura ( '_' )" wrote in message ... Excel 2000 Hiya, I have a question for all you brainboxes out there! Im VERY new to excel so please explain clearly or I'll be completley lost!! In the cell A3 I would like to have a "Grand Total". In the cell A2, I would like to enter a number, eg. 400 (this would then appear in A3 as 400), THEN I would like to enter another number, eg. 307 in cell A2. (this wouuld then appear in A3 as 707). I hope that explains it, I find it a bit confusing. Thanks for any help you can offer :) -- @---}-- Laura..... :) Liverpool, England |
#4
Posted to microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
Adding on excel
Hi Laura,
It can be done but only with a Change event macro! Do you really want this instead of a simpler method: entering your numbers in A2, A3, etc. and click on Autosum in the next cell when you finished? Regards, Stefi €žLaura ( '_' )€ť ezt Ă*rta: Excel 2000 Hiya, I have a question for all you brainboxes out there! Im VERY new to excel so please explain clearly or I'll be completley lost!! In the cell A3 I would like to have a "Grand Total". In the cell A2, I would like to enter a number, eg. 400 (this would then appear in A3 as 400), THEN I would like to enter another number, eg. 307 in cell A2. (this wouuld then appear in A3 as 707). I hope that explains it, I find it a bit confusing. Thanks for any help you can offer :) -- @---}-- Laura..... :) Liverpool, England |
#5
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
A code solution
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "$A$2" On Error GoTo ws_exit: Application.EnableEvents = False If Target.Address = WS_RANGE Then With Target .Offset(1, 0).Value = .Value + .Offset(1, 0).Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Laura ( '_' )" wrote in message ... Excel 2000 Hiya, I have a question for all you brainboxes out there! Im VERY new to excel so please explain clearly or I'll be completley lost!! In the cell A3 I would like to have a "Grand Total". In the cell A2, I would like to enter a number, eg. 400 (this would then appear in A3 as 400), THEN I would like to enter another number, eg. 307 in cell A2. (this wouuld then appear in A3 as 707). I hope that explains it, I find it a bit confusing. Thanks for any help you can offer :) -- @---}-- Laura..... :) Liverpool, England |
#6
Posted to microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
Adding on excel
I would prefer it the easier way, but my office boss has asked me to find a
way to do it by inputting the data in just one cell, so i have to find out how to do it. And to make it even WORSE, i have sum sort of bug on my computer which wont let me go on the internet or access links (im getting that sorted in another newsgroup at the mo!) So, if you could explain it to me, i would be *SO* grateful! Thanks -- @---}-- Laura..... :) Liverpool, England "Stefi" wrote in message ... Hi Laura, It can be done but only with a Change event macro! Do you really want this instead of a simpler method: entering your numbers in A2, A3, etc. and click on Autosum in the next cell when you finished? Regards, Stefi "Laura ( '_' )" ezt írta: Excel 2000 Hiya, I have a question for all you brainboxes out there! Im VERY new to excel so please explain clearly or I'll be completley lost!! In the cell A3 I would like to have a "Grand Total". In the cell A2, I would like to enter a number, eg. 400 (this would then appear in A3 as 400), THEN I would like to enter another number, eg. 307 in cell A2. (this wouuld then appear in A3 as 707). I hope that explains it, I find it a bit confusing. Thanks for any help you can offer :) -- @---}-- Laura..... :) Liverpool, England |
#7
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
Hiya, Im sorry but i really dont have a clue what that is or what to do with
it. Im sorry but im very very new :-S -- @---}-- Laura..... :) Liverpool, England "Bob Phillips" wrote in message ... A code solution Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "$A$2" On Error GoTo ws_exit: Application.EnableEvents = False If Target.Address = WS_RANGE Then With Target .Offset(1, 0).Value = .Value + .Offset(1, 0).Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Laura ( '_' )" wrote in message ... Excel 2000 Hiya, I have a question for all you brainboxes out there! Im VERY new to excel so please explain clearly or I'll be completley lost!! In the cell A3 I would like to have a "Grand Total". In the cell A2, I would like to enter a number, eg. 400 (this would then appear in A3 as 400), THEN I would like to enter another number, eg. 307 in cell A2. (this wouuld then appear in A3 as 707). I hope that explains it, I find it a bit confusing. Thanks for any help you can offer :) -- @---}-- Laura..... :) Liverpool, England |
#8
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
Open the worksheet it is to apply to, follow the instructions at the end of
the code, go back to the worksheet and put some numbers in A2. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Laura ( '_' )" wrote in message ... Hiya, Im sorry but i really dont have a clue what that is or what to do with it. Im sorry but im very very new :-S -- @---}-- Laura..... :) Liverpool, England "Bob Phillips" wrote in message ... A code solution Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "$A$2" On Error GoTo ws_exit: Application.EnableEvents = False If Target.Address = WS_RANGE Then With Target .Offset(1, 0).Value = .Value + .Offset(1, 0).Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Laura ( '_' )" wrote in message ... Excel 2000 Hiya, I have a question for all you brainboxes out there! Im VERY new to excel so please explain clearly or I'll be completley lost!! In the cell A3 I would like to have a "Grand Total". In the cell A2, I would like to enter a number, eg. 400 (this would then appear in A3 as 400), THEN I would like to enter another number, eg. 307 in cell A2. (this wouuld then appear in A3 as 707). I hope that explains it, I find it a bit confusing. Thanks for any help you can offer :) -- @---}-- Laura..... :) Liverpool, England |
#9
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
Okay thanks Bob. I did what you asked, but when I type in A2, I get an error
message "Syntax error". Then the top line of the code you gave me highlights yellow. This is how i have it in my spreadsheet: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "$A$2" On Error GoTo ws_exit: Application.EnableEvents = False If Target.Address = WS_RANGE Then With Target. Offset(1, 0).Value = .Value + .Offset(1, 0).Value End With End If ws_exit: Application.EnableEvents = True End Sub Thanks,you've been very helpful so far :) -- @---}-- Laura..... :) Liverpool, England "Bob Phillips" wrote in message ... Open the worksheet it is to apply to, follow the instructions at the end of the code, go back to the worksheet and put some numbers in A2. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Laura ( '_' )" wrote in message ... Hiya, Im sorry but i really dont have a clue what that is or what to do with it. Im sorry but im very very new :-S -- @---}-- Laura..... :) Liverpool, England "Bob Phillips" wrote in message ... A code solution Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "$A$2" On Error GoTo ws_exit: Application.EnableEvents = False If Target.Address = WS_RANGE Then With Target .Offset(1, 0).Value = .Value + .Offset(1, 0).Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Laura ( '_' )" wrote in message ... Excel 2000 Hiya, I have a question for all you brainboxes out there! Im VERY new to excel so please explain clearly or I'll be completley lost!! In the cell A3 I would like to have a "Grand Total". In the cell A2, I would like to enter a number, eg. 400 (this would then appear in A3 as 400), THEN I would like to enter another number, eg. 307 in cell A2. (this wouuld then appear in A3 as 707). I hope that explains it, I find it a bit confusing. Thanks for any help you can offer :) -- @---}-- Laura..... :) Liverpool, England |
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
A cell formula does not have memory (of previous values in a cell). The
only way to build in memory is with VBA (You chould have a worksheet change event that updates the cell when there is a change in the cell) http://www.mcgimpsey.com/excel /accumulator.html which will be a stretch given your lack of experience in Excel. As a practical matter, memory in a calculation is almost always a bad idea. How would you deal with data entry errors in the cell? If the running total somehow gets out of sync, how would you correct, or even detect it? Scudo's suggestion is a far more robust approach. Sorry about the multi-post reply -- Comcast has apparently instituted a new policy that does not permit a simultaneous reply to all of the original groups. Jerry Laura ( '_' ) wrote: I would prefer it the easier way, but my office boss has asked me to find a way to do it by inputting the data in just one cell, so i have to find out how to do it. And to make it even WORSE, i have sum sort of bug on my computer which wont let me go on the internet or access links (im getting that sorted in another newsgroup at the mo!) So, if you could explain it to me, i would be *SO* grateful! Thanks |
#11
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
Well I am confused as it works fine here, and there doesn't seem to be any
syntax mistakes in what you show. Syntax errors usually show when you enter the code, not when you run it. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Laura ( '_' )" wrote in message ... Okay thanks Bob. I did what you asked, but when I type in A2, I get an error message "Syntax error". Then the top line of the code you gave me highlights yellow. This is how i have it in my spreadsheet: Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "$A$2" On Error GoTo ws_exit: Application.EnableEvents = False If Target.Address = WS_RANGE Then With Target. Offset(1, 0).Value = .Value + .Offset(1, 0).Value End With End If ws_exit: Application.EnableEvents = True End Sub Thanks,you've been very helpful so far :) -- @---}-- Laura..... :) Liverpool, England "Bob Phillips" wrote in message ... Open the worksheet it is to apply to, follow the instructions at the end of the code, go back to the worksheet and put some numbers in A2. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Laura ( '_' )" wrote in message ... Hiya, Im sorry but i really dont have a clue what that is or what to do with it. Im sorry but im very very new :-S -- @---}-- Laura..... :) Liverpool, England "Bob Phillips" wrote in message ... A code solution Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "$A$2" On Error GoTo ws_exit: Application.EnableEvents = False If Target.Address = WS_RANGE Then With Target .Offset(1, 0).Value = .Value + .Offset(1, 0).Value End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Laura ( '_' )" wrote in message ... Excel 2000 Hiya, I have a question for all you brainboxes out there! Im VERY new to excel so please explain clearly or I'll be completley lost!! In the cell A3 I would like to have a "Grand Total". In the cell A2, I would like to enter a number, eg. 400 (this would then appear in A3 as 400), THEN I would like to enter another number, eg. 307 in cell A2. (this wouuld then appear in A3 as 707). I hope that explains it, I find it a bit confusing. Thanks for any help you can offer :) -- @---}-- Laura..... :) Liverpool, England |
#12
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
Laura ( '_' ) wrote...
Okay thanks Bob. I did what you asked, but when I type in A2, I get an error message "Syntax error". Then the top line of the code you gave me highlights yellow. This is how i have it in my spreadsheet: Private Sub Worksheet_Change(ByVal Target As Range) .... With Target. .... The problem is the period following Target in the line above. Remove the period. |
#13
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
Didn't spot that, I wonder how she did that.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Harlan Grove" wrote in message oups.com... Laura ( '_' ) wrote... Okay thanks Bob. I did what you asked, but when I type in A2, I get an error message "Syntax error". Then the top line of the code you gave me highlights yellow. This is how i have it in my spreadsheet: Private Sub Worksheet_Change(ByVal Target As Range) ... With Target. ... The problem is the period following Target in the line above. Remove the period. |
#14
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
"Harlan Grove" wrote in message
oups.com... The problem is the period following Target in the line above. Remove the period. ??? the period should be before the Offset not just removed from the Target otherwise it stops with a "Sub or function not defined" error (But then you knew that <g) -- Regards Sandy with @tiscali.co.uk " |
#15
Posted to microsoft.public.excel,microsoft.public.excel.newusers
|
|||
|
|||
Adding on excel
Sandy Mann wrote...
"Harlan Grove" wrote in message .... The problem is the period following Target in the line above. Remove the period. ??? the period should be before the Offset not just removed from the Target otherwise it stops with a "Sub or function not defined" error (But then you knew that <g) Know, maybe. Noticed, unfortunately not. Sometimes can only handle one bug at a time. |
#16
Posted to microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
Adding on excel
arent we all getting a bit carried away?in A3 type=sum(A1:A2)
-- paul remove nospam for email addy! "Laura ( '_' )" wrote: Excel 2000 Hiya, I have a question for all you brainboxes out there! Im VERY new to excel so please explain clearly or I'll be completley lost!! In the cell A3 I would like to have a "Grand Total". In the cell A2, I would like to enter a number, eg. 400 (this would then appear in A3 as 400), THEN I would like to enter another number, eg. 307 in cell A2. (this wouuld then appear in A3 as 707). I hope that explains it, I find it a bit confusing. Thanks for any help you can offer :) -- @---}-- Laura..... :) Liverpool, England |
#17
Posted to microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
Adding on excel
arent we all getting a bit carried away? ..
Think it was the OP's office boss who insisted on having it done in just one cell <g (OP explained in reply to Stefi) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
#18
Posted to microsoft.public.excel.newusers,microsoft.public.excel
|
|||
|
|||
Adding on excel
Yep thats right it was my demon boss <vbg
Anyway I've got it all sorted now, so a HUGE thanks to everyone who helped, you saved my life! :-D -- @---}-- Laura..... :) Liverpool, England "Max" wrote in message ... arent we all getting a bit carried away? .. Think it was the OP's office boss who insisted on having it done in just one cell <g (OP explained in reply to Stefi) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
Adding Columns in Excel | Excel Discussion (Misc queries) | |||
Difference in number of Excel NewsGroups | Excel Discussion (Misc queries) | |||
Excel 97 - Adding Every 8th Row - Formula should work, but doesn't | Excel Worksheet Functions | |||
What is the formula for adding mulitple numbers in one excel cell. | Excel Discussion (Misc queries) |