Transposing Multiple Cell References to Multiple Values (NOT total
 

Hi Everyone

Sometimes it is useful for me to show that a cell contains a string of
numbers which make up a total.

For instance, I would show in a Cell that the total you see ($1000) is made
up of $700 + $300

Sometimes, those individual amounts come from different sources / links.

So, (as an example) H11 ($700) plus G20 ($300) = $1000

In the cell it shows = H11 + G20

Is there any function which will transpose those cell references to the
amounts they represent WITHOUT summing them in the cell?

IE I would like to get = H11 + G20 to show = 700 + 300
NOT =1000

Does anyone have any ideas?
This kind of thing is very useful for reconciliations etc.

thanks!

Transposing Multiple Cell References to Multiple Values (NOT total
 

I cannot see how the result of the formula =H11+G20 can be made to DISPLAY
=700+300. But it this is just for reconciliation work and you have Excel
2003 (maybe also 2002 - I forget), use View | Toolbars and select Formula
Auditing. The last tool on this is Formula Evaluation. With your cell
selected you can watch the evolution:
=H11+G100 become
=700 + G100 then
=700 + 300 and finally
=1000
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"LinLin" wrote in message
...
Hi Everyone

Sometimes it is useful for me to show that a cell contains a string of
numbers which make up a total.

For instance, I would show in a Cell that the total you see ($1000) is
made
up of $700 + $300

Sometimes, those individual amounts come from different sources / links.

So, (as an example) H11 ($700) plus G20 ($300) = $1000

In the cell it shows = H11 + G20

Is there any function which will transpose those cell references to the
amounts they represent WITHOUT summing them in the cell?

IE I would like to get = H11 + G20 to show = 700 + 300
NOT =1000

Does anyone have any ideas?
This kind of thing is very useful for reconciliations etc.

thanks!

Transposing Multiple Cell References to Multiple Values (NOT total
 

Try this:

="="&H11&"+"&G20

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"LinLin" wrote in message
...
Hi Everyone

Sometimes it is useful for me to show that a cell contains a string of
numbers which make up a total.

For instance, I would show in a Cell that the total you see ($1000) is
made
up of $700 + $300

Sometimes, those individual amounts come from different sources / links.

So, (as an example) H11 ($700) plus G20 ($300) = $1000

In the cell it shows = H11 + G20

Is there any function which will transpose those cell references to the
amounts they represent WITHOUT summing them in the cell?

IE I would like to get = H11 + G20 to show = 700 + 300
NOT =1000

Does anyone have any ideas?
This kind of thing is very useful for reconciliations etc.

thanks!

Transposing Multiple Cell References to Multiple Values (NOT t
 

Hey RagDyer

This is HOT! Wow - that so cool!

thanks humungously (sorry, I'm Australian!)

"RagDyer" wrote:

Try this:

="="&H11&"+"&G20

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"LinLin" wrote in message
...
Hi Everyone

Sometimes it is useful for me to show that a cell contains a string of
numbers which make up a total.

For instance, I would show in a Cell that the total you see ($1000) is
made
up of $700 + $300

Sometimes, those individual amounts come from different sources / links.

So, (as an example) H11 ($700) plus G20 ($300) = $1000

In the cell it shows = H11 + G20

Is there any function which will transpose those cell references to the
amounts they represent WITHOUT summing them in the cell?

IE I would like to get = H11 + G20 to show = 700 + 300
NOT =1000

Does anyone have any ideas?
This kind of thing is very useful for reconciliations etc.

thanks!

Transposing Multiple Cell References to Multiple Values (NOT t
 

Hi bernard

See the response from RagDyer to my question - Rag understood my question (I
did not mean display, I meant more "see values in cell")
Rag's response was exactly what I was looking for, it's great to see there
are these solutions!

thanks all the same for responing to me!

"Bernard Liengme" wrote:

I cannot see how the result of the formula =H11+G20 can be made to DISPLAY
=700+300. But it this is just for reconciliation work and you have Excel
2003 (maybe also 2002 - I forget), use View | Toolbars and select Formula
Auditing. The last tool on this is Formula Evaluation. With your cell
selected you can watch the evolution:
=H11+G100 become
=700 + G100 then
=700 + 300 and finally
=1000
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"LinLin" wrote in message
...
Hi Everyone

Sometimes it is useful for me to show that a cell contains a string of
numbers which make up a total.

For instance, I would show in a Cell that the total you see ($1000) is
made
up of $700 + $300

Sometimes, those individual amounts come from different sources / links.

So, (as an example) H11 ($700) plus G20 ($300) = $1000

In the cell it shows = H11 + G20

Is there any function which will transpose those cell references to the
amounts they represent WITHOUT summing them in the cell?

IE I would like to get = H11 + G20 to show = 700 + 300
NOT =1000

Does anyone have any ideas?
This kind of thing is very useful for reconciliations etc.

thanks!

Transposing Multiple Cell References to Multiple Values (NOT t
 

Hi RD

Err, I've tried it out and it is extremely helpful BUT is there anyway to
then return the number value of the cell ? In the end, I still need to know
that the formula cell value is 1000.
I guess I can have the Ampersand forumla Cell as well as the normal formula
cell, but if you have other suggestions, that would be great.
(I know, I'd like my cake and eat it too!)

"RagDyer" wrote:

Try this:

="="&H11&"+"&G20

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"LinLin" wrote in message
...
Hi Everyone

Sometimes it is useful for me to show that a cell contains a string of
numbers which make up a total.

For instance, I would show in a Cell that the total you see ($1000) is
made
up of $700 + $300

Sometimes, those individual amounts come from different sources / links.

So, (as an example) H11 ($700) plus G20 ($300) = $1000

In the cell it shows = H11 + G20

Is there any function which will transpose those cell references to the
amounts they represent WITHOUT summing them in the cell?

IE I would like to get = H11 + G20 to show = 700 + 300
NOT =1000

Does anyone have any ideas?
This kind of thing is very useful for reconciliations etc.

thanks!

Transposing Multiple Cell References to Multiple Values (NOT t
 

Is this good enough:

="="&H11&"+"&G20&" equals "&H11+G20
?
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"LinLin" wrote in message
...
Hi RD

Err, I've tried it out and it is extremely helpful BUT is there anyway to
then return the number value of the cell ? In the end, I still need to
know
that the formula cell value is 1000.
I guess I can have the Ampersand forumla Cell as well as the normal
formula
cell, but if you have other suggestions, that would be great.
(I know, I'd like my cake and eat it too!)

"RagDyer" wrote:

Try this:

="="&H11&"+"&G20

--
HTH,

RD


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

--------------------------------------------------------------------------
-
"LinLin" wrote in message
...
Hi Everyone

Sometimes it is useful for me to show that a cell contains a string of
numbers which make up a total.

For instance, I would show in a Cell that the total you see ($1000) is
made
up of $700 + $300

Sometimes, those individual amounts come from different sources /
links.

So, (as an example) H11 ($700) plus G20 ($300) = $1000

In the cell it shows = H11 + G20

Is there any function which will transpose those cell references to
the
amounts they represent WITHOUT summing them in the cell?

IE I would like to get = H11 + G20 to show = 700 + 300
NOT =1000

Does anyone have any ideas?
This kind of thing is very useful for reconciliations etc.

thanks!

Transposing Multiple Cell References to Multiple Values (NOT t
 

PPS Sorry, I realise I am now starting to not explain myself properly,
because in my original post I said I did not want the cell to show 1000.

What I would like is:

Cell to show 1000
Formula line to show =700+300 (when the cell is clicked on)

It may be necessary for a two part process (which I'm happy to deal with)

A) Formula line is = H11 + G20 / Cell shows 1000

By transposing (somehow) cell references to values
B) Formula line is = 700 + 300 / Cell shows 1000

At the moment with the ampersand solution I have:
Formula line is ="="&H11&"+"&G20 / Cell shows = 700+300 (ie not a value)

Does this make it clearer?
Thanks for any additional suggestions



"RagDyer" wrote:

Try this:

="="&H11&"+"&G20

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"LinLin" wrote in message
...
Hi Everyone

Sometimes it is useful for me to show that a cell contains a string of
numbers which make up a total.

For instance, I would show in a Cell that the total you see ($1000) is
made
up of $700 + $300

Sometimes, those individual amounts come from different sources / links.

So, (as an example) H11 ($700) plus G20 ($300) = $1000

In the cell it shows = H11 + G20

Is there any function which will transpose those cell references to the
amounts they represent WITHOUT summing them in the cell?

IE I would like to get = H11 + G20 to show = 700 + 300
NOT =1000

Does anyone have any ideas?
This kind of thing is very useful for reconciliations etc.

thanks!