Forum Discussion
remove a ' to convert a comment to a formula in excel
Hi lsmithop Select the range of cells and use Text to Columns on the Data tab. Select nothing in the wizard, just click Finish and it should convert them back to formulas.
May I ask an after-the-fact question? No real need to answer since you've gotten a resolution, but you piqued my curiosity.
You intrigue me with your situation: I can't help but wonder WHY you were editing 1,500 formulas. The only hypothesis I could come up with was that you had some variable in there that had to be changed. That is to say, you were changing some hard-coded number from XX to YY. If that is the case (was the case), I would want to make sure you knew about methods to avoid hard-coding numbers (numbers such as, let's say, sales tax percentages) into formulas. It's always better to have a cell (or a table) somewhere that you can refer to for numbers like that, numbers that may not change often but when they do require changes through all 1,500 cells that contain them.
So if that is the case, a cell that contains the value 0.0875, say, could receive the range name "SalesTaxPct" and then any formulas that refer to it to calculate something would say =A1*SalesTaxPct rather than =A1*0.0875.
Forgive me if you know all that already; as I said, I couldn't think of another reason to have to change the formula in 1,500 cells....
mathetes--
OK, here's your answer, if you care enough to follow my clumsy explanation.
I have a "master" worksheet which has about 70 columns -- raw data, formulas, derived data, etc. It's too wide (and too much detail) to print for our users. So I made a second worksheet, which uses "stacked" columns (3 lines of report info/club member) to provide all important info on each member:
Member Name Address Phones Emails
John Smith 123 Main St. H: 123-456-7890 John@home.com
Anytown, MD 11111 W: 123-543-2109 John@work.com
C: 234-555-1212 John@ourclub.org
Each of those reporting cells (in worksheet 2) refers to a cell in a different column but the same row (that member's row) in the master data worksheet (#1). But when I copy the above 3-line report block in worksheet 2, to make additional report blocks, the member row references get incremented incorrectly (to N+4, instead of N+1). So the "solution" I hit upon (using the tools I know) was to put a ' in front of all the = in the report block. and change the row numbers to ^^ (thus turning off the row reference increments altogether). Then I copied my commented report block 9 times, changed the ^^ to **1, ^^2, ^^3, ..., and ^^0 in the ten blocks that I had. Then I duplicated those ten blocks ten times (I needed 110 rows in the report), and then I selected successive blocks of 10 rows, and changed the ^^ to null, 1, 3, 3, and up to 10. Thus, 20 edits corrected 100 rows. Since each block contained 15 cell references, and I had 110 blocks, I now had 1650 correct cell references, but I couldn't remove the bleeping ' until Alan shared his amazing knowledge.
I am sure there is a better way to have done this, but just like "the best camera is the one you have with you", my rule is "the best solution is the one you know how to do" (including know to ask the Excel forum community when you hit a brick wall!).
Thanks to all of you!
Len
P.S. And yes, a named cell range is WAY better than a text constant!! Thanks...