User Profile
lsmithop
Copper Contributor
Joined Jan 20, 2020
User Widgets
Recent Discussions
Re: remove a ' to convert a comment to a formula in excel
SergeiBaklan Sergei - Wow, thanks for a great tip. I have used H/VLookup (which look sort of similar) for years, but have never even looked at offset(). Will test it tomorrow -- it looks better (and potentially more maintainable) than my approach. Thanks again!3.1KViews0likes1CommentRe: remove a ' to convert a comment to a formula in excel
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...3.1KViews0likes4CommentsRe: remove a ' to convert a comment to a formula in excel
Alan_MurrayHi Alan -- In case you are not old enough to remember Xerox' ad with Brother Dominic, here's a link: https://www.youtube.com/watch?v=LAt-lB9JIqw That's how I feel about your solution! It's like you reached into Excel and said "This is what Len needs..." Thank you 1,500 times!! Len3.1KViews0likes1Commentremove a ' to convert a comment to a formula in excel
I routinely stick a ' in front of a formula to stop Excel from complaining/editing my text while I edit it to be the formula I want. Then I remove the ' to convert the cell back to a live forumula. Well, today I needed to edit 1500 cells to contain the formulas I needed, so i edited all my = to be '=, adjusted all my formulas, and now I cannot figure out how to use Excel's Replace (or even Find!!) to convert them back. Oops! If I search for '= (just apostrophe equals) I get "We couldn't find what you're looking for". I have tried varying all the options, tried using ^? to avoid ' in case that's some special character, etc. I attached a trivial case which shows the search failing... Can someone save my 1500 formulas? How can I rescue them? Thanks! LenSolved3.5KViews0likes11Comments
Recent Blog Articles
No content to show