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.
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...
Thank you for taking the time to answer my curiosity-motivated question. Your answer is not at all what I had hypothesized.
And I'm glad to see that @Sergei Baklan gave you his usual brilliant alternative way to meet the need you've been meeting the hard way. 🙂
Still another alternative--if the group you refer to as "our users" is really only accessing/viewing the information, i.e., not entering updates or corrections--would be to develop a distinct output report that you update every week (or however frequently it's called for). You could invest the time to use MailMerge in Word, using your Excel database as the source, printing the result to PDF.......and it could be formatted nicely, etc., etc. Once such a MailMerge file has been created, it would be simplicity itself to run it on whatever frequency is needed, printing hard copy if needed, certainly PDF for "soft copy"
That way you maintain the database in Excel as it should be maintained (single row per member); you can add a column if some new category of information becomes essential, make a simple change in the MailMerge master, and be all done with it. I like this because it cleanly separates the Input end of things from the Output...