If there are lots of text strings as =A1 + B1 format which you need to convert these text strings to real formulas and calculate their values in your worksheet, unfortunately, there is no direct method to solve it in Excel. But, here I can talk about some interesting tricks for you.
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
For the first defined function way, I have a problem.
when the resources for concatenate function are in another worksheet, Eval function works properly when that resource worksheet is open, But immediately when I close that resource worksheet, Eval function Not working. How I can change the codes for eval function to use closed resource worksheets?
Greetings, the above code works perfectly inside the same workbook across different sheets, but in case i have a cell reference to an external workbook it returns #VALUE!. the formulas were calculating normally earlier.
Example for the formula can't be evaluated correctly: IFERROR(INDEX('[Tracking-OSP Outside Riyadh-07-09-2017.xlsx]Tracking WO'!$U$3:$U$19000,MATCH(A3&"",'[Tracking-OSP Outside Riyadh-07-09-2017.xlsx]Tracking WO'!$B$3:$B$19000,0)),INDEX('[Tracking-OSP Outside Riyadh-07-09-2017.xlsx]Tracking WO'!$U$3:$U$19000,MATCH(value(A3),'[Tracking-OSP Outside Riyadh-07-09-2017.xlsx]Tracking WO'!$B$3:$B$19000,0))).
I'm just reading you comment on turning text into a formula. It seems that the function as mentioned above is not working. I'm not really a programmer but what i did is converting a formula into a text and in the text i have to replace a few values and combine it again in one text but now i need to convert it back into a formula. Could you give me a tip.
I don't know if this would always work, but I had a number of cells that had formulas stored as text strings. I did a full worksheet find replace on the "=" character, and all of my strings converted to formulas with that one action.
Hi your macro works, though im using it to extract a value from another worksheet that I specify in the workbook. Problem is that i'd like it read this value even when this external workbook is closed, however if it is closes eval returns a REF error. Is there a way that eval can be modified to open/display values of files that are not open?
In order to have values always available extracted from other workbooks even when they are closed, you should implement some power query. I faced this problem a while ago, and it is working perfectly for me now. The problem I am having now though is that I want to update several formulas in "daughter" workbooks, by referencing the source in my "mother" workbook, and through power query I managed to get to the point to have all formulas updated in string format in the daughters sheets, however, even by using the Evaluate function, it seems to not work with lists, conditional formating, nor more complex formulas which use many functions or matrix referencing... :( I am reeeeally having a hard time trying to find a way to make this work out now from here on forward...