LinkBack URL
About LinkBacks
Hello,
B1=aaa; C1=bbb; D1=B1&C1; I need A1 to take formula text from D1 that is B1&C1 and return value aaabbb. I tried indirect("="&D1) but it returns REF error. Please help
Hello,
B1=aaa; C1=bbb; D1=B1&C1; I need A1 to take formula text from D1 that is B1&C1 and return value aaabbb. I tried indirect("="&D1) but it returns REF error. Please help
Try:
=INDIRECT("D1")
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
also does not work, REF error
Perhaps,
=INDIRECT(LEFT(D1,FIND("&",D1)-1))&INDIRECT(RIGHT(D1,LEN(D1)-FIND("&",D1)))
It should return "B1&C1" (without the quote marks).Originally Posted by ngorg
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
this works in this specific example, but instead of B1&C1 I have a large complex formula, I need more general formula to convert the text of a formula into the real formula
You can't use INDIRECT that way. Each part of the formula would need to be a parameter. Have you thought of using a LET formula?
=LET(a,"aaa",b,"bbb",c,a&b,c)
It does not return a result, just REF error. I attached the file again with indirect (D1) resulting REF error
The function INDIRECT can only return a cell (or range) reference, not a formula.
So its not possible to return the operand & by an INDIRECT-function.
Last edited by HansDouwe; 09-01-2023 at 07:42 AM.
Glenn said this:
=INDIRECT("D1")
NOT this:
=INDIRECT(D1)
How about this?
=LET(a,B1,b,C1,a&b)
I was wrong... plain and simple!!
You can't do that without some sort of macro, whether VBA or XLM.
Last edited by romperstomper; 09-01-2023 at 07:55 AM.
Everyone who confuses correlation and causation ends up dead.
Perhaps, you can input in D1 =B1&C1 direct to get answer.
Is there any way I can convert formula text in a cell to real formula? I have different kinds of formulas for different versions of cells. For example on the first sheet in A:A column is Version column, where A1=3, A2=2, A3=6... etc. On another sheet I have A:A column with version numbers and B:B column with formula type to use. I need on the first sheet B:B to index the formula text from second sheet (B:B), that matches the version type (A From sheet 1 and A:A from sheet2), and return the formula text on the first sheet B:B and convert it to real formula so it calculates the results on that sheet. So I need some way to convert formula text to real formula. I home I explained clearly. Thank you.
Provide a more deatiled sample workbook with some examples of what you mean.
Sorry, Indirect("D1") gives the result "B1&C1", I need it to calculate "B1&C1" and give the result "aaabbb".
Let formula as I understand makes me use only one formula (a&b), I need the conversion to use different formulas according to different versions of a previous cell. (I have explained above)
See post #15 - I'm waiting for a better sample workbook.
I understand what you mean, but that is not possible in Excel.Is there any way I can convert formula text in a cell to real formula?
The function FORMULATEXT converts a real formula to text, but there is no inverse of the function FORMULATEXT that converts a text to a formula.
You need to code all possible formula's and select the result of the formula you want.
Sometimes you can combine different formulas in one formula.
Last edited by HansDouwe; 09-01-2023 at 08:08 AM.
This is the sample, thank you
Try in H12 copied down:
Please Login or Register to view this content.
Last edited by AliGW; 09-01-2023 at 08:40 AM.
Another way:
Please activate all the formulas in columns K, L & M and try in G2 and copy down:Formula:
Please Login or Register to view this content.
Last edited by HansDouwe; 09-01-2023 at 08:51 AM.
OMG this worked! Thank you!!!
This also works !!!
What worked? My solution or Hans'?
Glad to have helped.
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Thanks for the feedback and rep.. Glad to have helped.
both worked
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks