Adding numbers in one cell and showing total in seperate cell

[Guest]

I am using Office 2000. I have an aritmetic problem that I want to display
in one cell and then have it total those numbers in a seperate cell. I need
it to be able to automatically change the total in cell A2 if I change a
number in cell A1 I can't figure out how.
Example of how I'd like it displayed:
[Cell A1] 1+2.5+3+4
[Cell A2] 10.5

 

[Guest]

Hi - go into cell A2 and type in an opertor (either the plus sign +, or the =
sign) and click cell A1, hit enter.

 

[Guest]

When I do that, it won't give me a sum of the numbers in A1, it just gives me
exactly what I put in A1. I've also tried to use =SUM(A1) and it just gives
a zero.
Example:
[A1] 1+2.5+3+4
[A2] Typed (=A1), Shows: 1+2.5+3+4
-or-
[A1] 1+2.5+3+4
[A2] Typed (=SUM(A1)), Shows: 0

Hi - go into cell A2 and type in an opertor (either the plus sign +, or the =
sign) and click cell A1, hit enter.
--
tenaj

I am using Office 2000. I have an aritmetic problem that I want to display
in one cell and then have it total those numbers in a seperate cell. I need
it to be able to automatically change the total in cell A2 if I change a
number in cell A1 I can't figure out how.
Example of how I'd like it displayed:
[Cell A1] 1+2.5+3+4
[Cell A2] 10.5

 

[AlfD]

Hi!

Try this in the code page behind your worksheet (I've called i
Sheet1):

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Sheet1")
.Range("A2") = "=" & .Range("A1")
End With
End Sub

Each time you change A1, its calculated value will appear in A2.


Al

 

[Dave Peterson]

There's nothing built into excel that lets you do that.

But you could create a user defined function that did it.

Option Explicit
Function Eval(myStr As String) As Variant
Eval = Application.Evaluate(myStr)
End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=eval(A24)
if A24 contained the string to evaluate.

I am using Office 2000. I have an aritmetic problem that I want to display
in one cell and then have it total those numbers in a seperate cell. I need
it to be able to automatically change the total in cell A2 if I change a
number in cell A1 I can't figure out how.
Example of how I'd like it displayed:
[Cell A1] 1+2.5+3+4
[Cell A2] 10.5

 

[Ragdyer]

Your problem is that your data in A1 is *Text*, not true numbers as
recognized by XL.

Put a
+
OR
=
in front of the 1 in A1, and if the cell is formatted as General or Number,
You'll get what you're looking for in A2.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

When I do that, it won't give me a sum of the numbers in A1, it just gives
me
exactly what I put in A1. I've also tried to use =SUM(A1) and it just
gives
a zero.
Example:
[A1] 1+2.5+3+4
[A2] Typed (=A1), Shows: 1+2.5+3+4
-or-
[A1] 1+2.5+3+4
[A2] Typed (=SUM(A1)), Shows: 0

Hi - go into cell A2 and type in an opertor (either the plus sign +, or
the =
sign) and click cell A1, hit enter.
--
tenaj

I am using Office 2000. I have an aritmetic problem that I want to
display
in one cell and then have it total those numbers in a seperate cell. I
need
it to be able to automatically change the total in cell A2 if I change
a
number in cell A1 I can't figure out how.
Example of how I'd like it displayed:
[Cell A1] 1+2.5+3+4
[Cell A2] 10.5

 

[CyberTaz]

Maybe a dumb question, but why not just put the four values in separate
cells & SUM the four cells in a 5th? Such as;

A1- 1
A2- 2.5
A3- 3
A4- 4
A5- =Sum(A1:A5)

If you use the + or = in A1 it will give you the total right there, so the
=SUM in A2 is redundant. Also, by putting the values in separate cells,
changes can be made to the individual cells without having to edit a
formula.

Use a Text Box, Comment or another cell to display the operation. A Text Box
will work best if you don't want the 4 values to show 'cause you can overlay
those cells with the Text Box to hide them.

Sorry for having such a simple mind, but all that code makes me nervous if
it isn't absolutely necessary

Regards |:>)

When I do that, it won't give me a sum of the numbers in A1, it just gives me
exactly what I put in A1. I've also tried to use =SUM(A1) and it just gives
a zero.
Example:
[A1] 1+2.5+3+4
[A2] Typed (=A1), Shows: 1+2.5+3+4
-or-
[A1] 1+2.5+3+4
[A2] Typed (=SUM(A1)), Shows: 0

Hi - go into cell A2 and type in an opertor (either the plus sign +, or the =
sign) and click cell A1, hit enter.
--
tenaj

I am using Office 2000. I have an aritmetic problem that I want to display
in one cell and then have it total those numbers in a seperate cell. I need
it to be able to automatically change the total in cell A2 if I change a
number in cell A1 I can't figure out how.
Example of how I'd like it displayed:
[Cell A1] 1+2.5+3+4
[Cell A2] 10.5

 

[Guest]

I am not cumputer savvy whatsoever. Could you possibly put this in lamens'
terms? For instance, do I type:
Private Sub Worksheet_Change(ByVal Target As Range)

With Worksheets("Sheet1")
.Range("A2") = "=" & .Range("A1")
End With
End Sub

in the formula bar? or where? and shouldit be done with cell A1 have this
or A2? Please instruct as though you were doing it to the actual worksheet
because I can always not save if it doesn't work. Thank you.

 

[AlfD]

Hi!

OK. Step-by-step:

Copy the text of the routine:

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Sheet1")
.Range("A2") = "=" & .Range("A1")
End With
End Sub
(Highlight the text and use ctrl & C to copy the text to th
clipboard.

Open the workbook at the worksheet you want your calculations to b
done on.
Note its name (on the tab at the bottom): you'll need this later.

Press Alt & F11. A new window (the code page) will open.

Paste the clipboard contents (use ctrl & v) onto this page.

Edit the routine to change the name "Sheet1" into the name of you
worksheet which you noted above. Be sure to keep the quotes.

Return to your worksheet. (Use Alt & F11 again).

If you now type 3+4+7.5 in cell A1, Excel will hold that in A1 as tex
and put the "answer" 14.5 in A2.

If you want your data to appear in different cells, just substitut
their addresses for A1 and A2 in the routine.

Al

 

[Guest]

I want them all in one cell because I'm doing timecards for a construction
co. and they have different phases each day for each man, so the "1+2+4+..."
are hours per phase. If I do them in different cells, then it makes my
worksheet way too big to record a crew of 16 because that would be at least 5
cells per person, per day on a two-week period. I hope I'm making sense.
Bottom line, the document gets too big.
When I originally did just what you're suggesting, I had 23 pages to
printout when it was done and we're on a paper limit here due to expense.

Maybe a dumb question, but why not just put the four values in separate
cells & SUM the four cells in a 5th? Such as;

A1- 1
A2- 2.5
A3- 3
A4- 4
A5- =Sum(A1:A5)

If you use the + or = in A1 it will give you the total right there, so the
=SUM in A2 is redundant. Also, by putting the values in separate cells,
changes can be made to the individual cells without having to edit a
formula.

Use a Text Box, Comment or another cell to display the operation. A Text Box
will work best if you don't want the 4 values to show 'cause you can overlay
those cells with the Text Box to hide them.

Sorry for having such a simple mind, but all that code makes me nervous if
it isn't absolutely necessary

Regards |:>)

When I do that, it won't give me a sum of the numbers in A1, it just gives me
exactly what I put in A1. I've also tried to use =SUM(A1) and it just gives
a zero.
Example:
[A1] 1+2.5+3+4
[A2] Typed (=A1), Shows: 1+2.5+3+4
-or-
[A1] 1+2.5+3+4
[A2] Typed (=SUM(A1)), Shows: 0

Hi - go into cell A2 and type in an opertor (either the plus sign +, or the =
sign) and click cell A1, hit enter.
--
tenaj


:

I am using Office 2000. I have an aritmetic problem that I want to display
in one cell and then have it total those numbers in a seperate cell. I need
it to be able to automatically change the total in cell A2 if I change a
number in cell A1 I can't figure out how.
Example of how I'd like it displayed:
[Cell A1] 1+2.5+3+4
[Cell A2] 10.5

 

[Guest]

Thank you very much for your help. It solved my problem. Is there any way
to make this formula available to use on all excel documents without having
to go through the whole Module setup over and over? For instance, making it
like the SUM function where you just type it into the formula bar?

Thanks again for your help already given!

There's nothing built into excel that lets you do that.

But you could create a user defined function that did it.

Option Explicit
Function Eval(myStr As String) As Variant
Eval = Application.Evaluate(myStr)
End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=eval(A24)
if A24 contained the string to evaluate.

I am using Office 2000. I have an aritmetic problem that I want to display
in one cell and then have it total those numbers in a seperate cell. I need
it to be able to automatically change the total in cell A2 if I change a
number in cell A1 I can't figure out how.
Example of how I'd like it displayed:
[Cell A1] 1+2.5+3+4
[Cell A2] 10.5

 

[Dave Peterson]

You can create a new workbook full of your functions. Lots of people put these
kinds of functions into their personal.xl* workbook.

But if you're going to share these utilities with others, you won't want to use
the name personal.xl*.

Use a name like: DeernadUtils.xls or DeernadUtils.xla

If you save this workbook as a .xls file, make sure you have that workbook open
and you can use:

=deernadutils.xls!eval(a1)
in any other open workbook.

If you save it as an addin (*.xla), you can then turn that addin on.

Tools|addins|browse|point at your addin workbook.
Then you can use:
=eval(a1)

If only have functions like this, I like the addin approach. You can stick a
copy out on a network drive and tell your co-workers to install it just like you
did.

==
No matter what you choose, if you don't save the workbook/addin with others,
then make sure you convert these formulas to values (edit|copy, edit|paste
special|values).

Thank you very much for your help. It solved my problem. Is there any way
to make this formula available to use on all excel documents without having
to go through the whole Module setup over and over? For instance, making it
like the SUM function where you just type it into the formula bar?

Thanks again for your help already given!

There's nothing built into excel that lets you do that.

But you could create a user defined function that did it.

Option Explicit
Function Eval(myStr As String) As Variant
Eval = Application.Evaluate(myStr)
End Function

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Short course:

Open your workbook.
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook.
should look like: VBAProject (yourfilename.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now go back to excel.

Then use a formula like:

=eval(A24)
if A24 contained the string to evaluate.

I am using Office 2000. I have an aritmetic problem that I want to display
in one cell and then have it total those numbers in a seperate cell. I need
it to be able to automatically change the total in cell A2 if I change a
number in cell A1 I can't figure out how.
Example of how I'd like it displayed:
[Cell A1] 1+2.5+3+4
[Cell A2] 10.5

 

[Guest]

I am at loss as to what is indicated here.Exactly where do I put the code? I
am not good at spread sheets . Where is the code page behind the spread
sheet? I have been working on this "simple thing" for days".

What I am doing is adding sales up for a whloe year, I have about 100 items
that I sell and wish to add up the totals.

Item 1 sold cost total
**
Item 2 sold cost total
**
the ** is where I want to enter amounts to wind up in the total area.
Thanks
chuck