Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-19-2022, 01:50 PM
kilroyscarnival kilroyscarnival is offline Converting formulas from cell references to numbers Windows 10 Converting formulas from cell references to numbers Office 2021
Expert
Converting formulas from cell references to numbers
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default 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 off-print-area helper column that summed the current and past to-date 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.
Reply With Quote
  #2  
Old 07-19-2022, 02:11 PM
kilroyscarnival kilroyscarnival is offline Converting formulas from cell references to numbers Windows 10 Converting formulas from cell references to numbers Office 2021
Expert
Converting formulas from cell references to numbers
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

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.
Reply With Quote
  #3  
Old 07-19-2022, 08:40 PM
ArviLaanemets ArviLaanemets is offline Converting formulas from cell references to numbers Windows 8 Converting formulas from cell references to numbers Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

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
NB! The added column displays all components of sum as string! You can take it as comment of formula.

And in case the components of original sum are formulas instead cell references, like:
Code:
= SUM(Formula1,Formula2,Formula3,Formula4)
in added column you use formula:
Code:
= "" & Formula1 & "+" & Formula2 & "+" & Formula3 & "+" & Formula3 & "+" & Formula4
Reply With Quote
  #4  
Old 07-20-2022, 03:18 PM
p45cal's Avatar
p45cal p45cal is offline Converting formulas from cell references to numbers Windows 10 Converting formulas from cell references to numbers Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

Take the formula such as =Q6+R6+S6+T6+U6
In the formula bar, double-click on say Q6 to select that single reference:
2022-07-20_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:
2022-07-20_231348.png
and here I've double clicked on all but one reference, one at a time and pressed F9 each time and got:
2022-07-20_231441.png


Any use?
Reply With Quote
  #5  
Old 07-25-2022, 10:07 AM
kilroyscarnival kilroyscarnival is offline Converting formulas from cell references to numbers Windows 10 Converting formulas from cell references to numbers Office 2021
Expert
Converting formulas from cell references to numbers
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

Quote:
Originally Posted by p45cal View Post
Any use?
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!
Reply With Quote
  #6  
Old 11-30-2022, 02:17 PM
kilroyscarnival kilroyscarnival is offline Converting formulas from cell references to numbers Windows 10 Converting formulas from cell references to numbers Office 2021
Expert
Converting formulas from cell references to numbers
 
Join Date: May 2019
Posts: 344
kilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nicekilroyscarnival is just really nice
Default

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.
Reply With Quote
Reply

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 09-17-2019 06:59 PM
Rounding numbers in formulas Lamont Excel 3 01-09-2019 08:19 AM
Converting formulas from cell references to numbers Converting xls(x) with formulas to pdf rheaney Excel 2 11-22-2016 03:30 PM
Fields and References Don't Show as Numbers peytontodd Word 2 05-31-2014 07:05 PM
Converting formulas from cell references to numbers Cell formulas & formatting kungfauxn00b Word Tables 3 04-05-2011 04:54 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft