#1




Converting formulas from cell references to numbers
Is it possible in Excel to render a cell that shows, instead of cell references, the values within those cells? Like instead of the formula being =Q6+R6+S6+T6+U6, I could get a cell containing the contents, but within a formula (=5+4+0+2+10)?
A coworker suddenly wants every sheet to show a TYPED IN formula instead of the values, which I had calculated with an offprintarea helper column that summed the current and past todate quantities. Copy/paste values, simple. She wants to see all the individual numbers, and it will take forever to update multiple sheets that way. I was just trying to figure out a workaround. 
#2




Lame as this is, it sort of works.
In a column to the right, concatenate: =CONCATENATE("="&P15&"+"&Q15&"+"&R15) Take that column, and paste As Values to the right of it. It will display as =1+2+3 reading as text, rather than returning the total. Then highlight that column and run a macro replacing one of the formula's components with itself. I chose replace = with =, since there's an equals sign in every formula. It does change to displaying 6, and when you click in, it shows =1+2+3 in the formula bar. Yes, it's a nuts thing to need to do. 
#3




On fly!
Leave the column with sum as it is. Add an empty column next to it, with formula like Code:
= "" & Q6 & "+" & R6 & "+" & S6 & "+" & T6 & "+" & U6 And in case the components of original sum are formulas instead cell references, like: Code:
= SUM(Formula1,Formula2,Formula3,Formula4) Code:
= "" & Formula1 & "+" & Formula2 & "+" & Formula3 & "+" & Formula3 & "+" & Formula4 
#4




Take the formula such as =Q6+R6+S6+T6+U6
In the formula bar, doubleclick on say Q6 to select that single reference: 20220720_231308.png then press the key F9 on the keyboard. Excel will replace the reference with its value. If you select more than one reference in the formula it will try and calculate the bit of formula that you've selected. Here I've done it with Q6: 20220720_231348.png and here I've double clicked on all but one reference, one at a time and pressed F9 each time and got: 20220720_231441.png Any use? 
#5




I like the idea of it, and will give it a try. But to do a whole string of these would almost take as much time as doing it her way. I think what I did on mine works, if it comes to that.
It's definitely a cool tool to know, if I manage to remember it! 
#6




To follow up on this, as it may come in handy for someone else...
I was using =CONCATENATE("="&F7&"+"&D7) to get started, and that works when the running total in F7 is just a number (the quantity used to date.) But when that cell is eventually replaced with the running "adding machine tape" that she wanted (=2+0+3+2), I ended up using =CONCATENATE(FORMULATEXT(F7)&"+"&D7 Seems simple, but it took me a while to sort out what would work. I do that in a helper column, paste values over the F row, then use a little helper macro to refresh all those cells so it goes back to a working formula again. 
Thread Tools  
Display Modes  

Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
VBA or Formulas to parse out numbers based on criteria  durwood  Excel Programming  2  09172019 06:59 PM 
Rounding numbers in formulas  Lamont  Excel  3  01092019 08:19 AM 
Converting xls(x) with formulas to pdf  rheaney  Excel  2  11222016 03:30 PM 
Fields and References Don't Show as Numbers  peytontodd  Word  2  05312014 07:05 PM 
Cell formulas & formatting  kungfauxn00b  Word Tables  3  04052011 04:54 AM 