I have a cell where I need to keep the amounts separate to show workings
out, but I need this to be then added up in a sum, is there a way to do this
as at present I have to manual work this out and enter in another cell toget
the answer and this leaves too much room for error.

maybe

=50+55+50+50

that'll leave the individual amounts visible to anybody who selects the
cell, but will still show the total in the cell itself

"shyboy2k" wrote:

> I have a cell where I need to keep the amounts separate to show workings
> out, but I need this to be then added up in a sum, is there a way to do this
> as at present I have to manual work this out and enter in another cell toget
> the answer and this leaves too much room for error.

One cell only? Select that cell, put an equal sign in front of it in the
Formula Bar and then press return.

Rick


"shyboy2k" <(E-Mail Removed)> wrote in message
news:A635B12A-45B8-4390-8E4C-(E-Mail Removed)...
>I have a cell where I need to keep the amounts separate to show workings
> out, but I need this to be then added up in a sum, is there a way to do
> this
> as at present I have to manual work this out and enter in another cell
> toget
> the answer and this leaves too much room for error.

On Aug 28, 12:19 pm, shyboy2k <shybo...@discussions.microsoft.com>
wrote:
> I have a cell where I need to keep the amounts separate to show workings
> out, but I need this to be then added up in a sum, is there a way to do this
> as at present I have to manual work this out and enter in another cell toget
> the answer and this leaves too much room for error.

Generally, you put the amounts in different cells and sum them
elsewhere, and that's how people get to see both the list of
individual numbers and the total.

If you always have exactly 4 values to add up, a repetitive series of
VALUE/LEFT & VALUE/MID functions can be used to create a sum from the
text. But I don't think it would be easy to do this.

VBA can, or UDFs written in VBA. I use morefunc, which can be
downloaded from download.com, and it has a function called EVAL that
can do this very easily.

Another alternative is to use the EVALUATE function. But it is tricky
to do so, since you can't actually use EVALUATE in a cell. (go ahead,
try) Try this: Define a Name with this formula:
=EVALUATE(INDIRECT("RC[-1]",FALSE))
Let's call it Special_Sum. Enter the following in these cells:
A1: 50+55+50+50
B1: =Special_Sum

This works if the Sum will always be 1 cell to the right of the
spelled-out text. If it will be somewhere else, you'll have to modify
the RC[-1] reference.

Hi,

Here is a slightly different thought:

In Excel you can press Ctrl+` this is a TOGGLE of the Formula/Value views.
The ` is usually located on the keyboard to the left of the "1" key on the
top row, it is not the apostrophy key '.

--
Cheers,
Shane Devenshire


"shyboy2k" wrote:

> I have a cell where I need to keep the amounts separate to show workings
> out, but I need this to be then added up in a sum, is there a way to do this
> as at present I have to manual work this out and enter in another cell toget
> the answer and this leaves too much room for error.

I've seen this request before and thought it was an interesting idea.

Let's say you have the following typed into A1:

4+5

Now you'd like to have the result automatically displayed in another cell.
The result for this equation, of course, is 9. However, if we don't know
what the equation will be, yet we still want the result automatically
displayed in another cell, then well need to use a UDF (User Defined
Function).
This is done through VBA.
-Press Alt+F11 to open the VB Editor.
Past the following code into a Module.

Function RetVal(Target As Range) As Variant
Dim str As String
'Target must be a single cell
If Target.Count > 1 Then
RetVal = vbNullString
Exit Function
End If
'This section removes double quotes, if any.
str$ = Replace(Target.Value, """", vbNullString)
'Evaluate the cell entry.
RetVal = Evaluate("=" & str$)
End Function

Now you can use the following formula in any cell to return the desired
calculation of the equation entered in A1.

=RetVal(A1)


HTH,
Paul

--

"shyboy2k" <(E-Mail Removed)> wrote in message
news:A635B12A-45B8-4390-8E4C-(E-Mail Removed)...
>I have a cell where I need to keep the amounts separate to show workings
> out, but I need this to be then added up in a sum, is there a way to do
> this
> as at present I have to manual work this out and enter in another cell
> toget
> the answer and this leaves too much room for error.

Hi shyboy,

Spiky touched on the old EVALUATE macro function above.
This site has a bit more info on it.
http://www.ozgrid.com/News/excel-eva...ormula-VBA.htm
Scroll down to Excel tips and tricks.

HTH
Martin


"shyboy2k" <(E-Mail Removed)> wrote in message
news:A635B12A-45B8-4390-8E4C-(E-Mail Removed)...
>I have a cell where I need to keep the amounts separate to show workings
> out, but I need this to be then added up in a sum, is there a way to do
> this
> as at present I have to manual work this out and enter in another cell
> toget
> the answer and this leaves too much room for error.

Hello Paul,

This worked a treat once I allowed the MACRO to work on the spread sheet,
thnx 4 ur help

shyboy2k

"PCLIVE" wrote:

> I've seen this request before and thought it was an interesting idea.
>
> Let's say you have the following typed into A1:
>
> 4+5
>
> Now you'd like to have the result automatically displayed in another cell.
> The result for this equation, of course, is 9. However, if we don't know
> what the equation will be, yet we still want the result automatically
> displayed in another cell, then well need to use a UDF (User Defined
> Function).
> This is done through VBA.
> -Press Alt+F11 to open the VB Editor.
> Past the following code into a Module.
>
> Function RetVal(Target As Range) As Variant
> Dim str As String
> 'Target must be a single cell
> If Target.Count > 1 Then
> RetVal = vbNullString
> Exit Function
> End If
> 'This section removes double quotes, if any.
> str$ = Replace(Target.Value, """", vbNullString)
> 'Evaluate the cell entry.
> RetVal = Evaluate("=" & str$)
> End Function
>
> Now you can use the following formula in any cell to return the desired
> calculation of the equation entered in A1.
>
> =RetVal(A1)
>
>
> HTH,
> Paul
>
> --
>
> "shyboy2k" <(E-Mail Removed)> wrote in message
> news:A635B12A-45B8-4390-8E4C-(E-Mail Removed)...
> >I have a cell where I need to keep the amounts separate to show workings
> > out, but I need this to be then added up in a sum, is there a way to do
> > this
> > as at present I have to manual work this out and enter in another cell
> > toget
> > the answer and this leaves too much room for error.
>
>
>

Thanks MartinW

on your post I will look at this link for future reference on Macro extra
for the when I need one again...

Shyboy2k

"MartinW" wrote:

> Hi shyboy,
>
> Spiky touched on the old EVALUATE macro function above.
> This site has a bit more info on it.
> http://www.ozgrid.com/News/excel-eva...ormula-VBA.htm
> Scroll down to Excel tips and tricks.
>
> HTH
> Martin
>
>
> "shyboy2k" <(E-Mail Removed)> wrote in message
> news:A635B12A-45B8-4390-8E4C-(E-Mail Removed)...
> >I have a cell where I need to keep the amounts separate to show workings
> > out, but I need this to be then added up in a sum, is there a way to do
> > this
> > as at present I have to manual work this out and enter in another cell
> > toget
> > the answer and this leaves too much room for error.
>
>
>

You're welcome. I credit Tom Hutchins for the code. It may or may not be
his...but I saw it in one of his posts.

Great stuff.

--

"shyboy2k" <(E-Mail Removed)> wrote in message
news:7E9A7C7B-3CF5-4976-B2FF-(E-Mail Removed)...
> Hello Paul,
>
> This worked a treat once I allowed the MACRO to work on the spread sheet,
> thnx 4 ur help
>
> shyboy2k
>
> "PCLIVE" wrote:
>
>> I've seen this request before and thought it was an interesting idea.
>>
>> Let's say you have the following typed into A1:
>>
>> 4+5
>>
>> Now you'd like to have the result automatically displayed in another
>> cell.
>> The result for this equation, of course, is 9. However, if we don't know
>> what the equation will be, yet we still want the result automatically
>> displayed in another cell, then well need to use a UDF (User Defined
>> Function).
>> This is done through VBA.
>> -Press Alt+F11 to open the VB Editor.
>> Past the following code into a Module.
>>
>> Function RetVal(Target As Range) As Variant
>> Dim str As String
>> 'Target must be a single cell
>> If Target.Count > 1 Then
>> RetVal = vbNullString
>> Exit Function
>> End If
>> 'This section removes double quotes, if any.
>> str$ = Replace(Target.Value, """", vbNullString)
>> 'Evaluate the cell entry.
>> RetVal = Evaluate("=" & str$)
>> End Function
>>
>> Now you can use the following formula in any cell to return the desired
>> calculation of the equation entered in A1.
>>
>> =RetVal(A1)
>>
>>
>> HTH,
>> Paul
>>
>> --
>>
>> "shyboy2k" <(E-Mail Removed)> wrote in message
>> news:A635B12A-45B8-4390-8E4C-(E-Mail Removed)...
>> >I have a cell where I need to keep the amounts separate to show
>> >workings
>> > out, but I need this to be then added up in a sum, is there a way to do
>> > this
>> > as at present I have to manual work this out and enter in another cell
>> > toget
>> > the answer and this leaves too much room for error.
>>
>>
>>