Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Transposing Multiple Cell references as Multiple Values | Excel Discussion (Misc queries) | |||
show multiple values that add to total in bar graph | Charts and Charting in Excel | |||
total value relating to cell values in multiple columns! | Excel Discussion (Misc queries) | |||
How do I put multiple references in the same cell? | Excel Worksheet Functions | |||
multiple cell references | Links and Linking in Excel |