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.
On the one hand I could agree with such approach - it's always better to use what you know better. On the other hand, taking into account that manual transformations are always source of errors an usually time consuming, it could be worth to invest some time for the automation of routine operations.
I believe people here could suggest several variants (everything in Excel could be done by several ways) of such automation, from Power Query to different variants of formulas.
For example, far from optimal but relatively simple is variant with offset(), here you may just drag F1:G1 down
I have no intention to ask you for shifting on another method, that's only to illustrate that relatively simple but more reliable solutions usually exist.
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!
lsmithop , please note I gave only mockup, such formulas depends on actual data structure.