• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Change text string returned as a result of a formula to real text you can edit

C

claudine

New Member
Hello All,


I have a new quirky issue, which is probably because I am trying to over step my excel skill sets (again).


This formula returns action plan suggestoins from another worksheet based on the responses to questions the user selects;


=IF(A3=1,'Intervention Admin 1'!B3,IF(A3=2,'Intervention Admin 1'!C3,IF(A3=3,'Intervention Admin 1'!D3,IF(A3=4,'Intervention Admin 1'!E3,IF(A3=5,"Work to Maintain and Review.",IF(A3="",""))))))


Obviously, this means that the user cannot edit the action plan suggestions.


1) is there a formula I can apply that converts this formula to text?

2) i have tried a macro that makes a column run a "paste special" values only to get the text. Since the action plan suggestions are formatted like those below, I get name errors (I think excel is ignoring the ' and reading the - as a minus sign);


'- No benchmarking in place.


As ever your genious is most highly appreciated.


With every kindest regard,


Claudine
 
Hello Claudine,


Not sure why you want to convert a formula to text, but have you tried these steps ?


1/ select the cell containing the formula,

2/ Press F2

3/ At the beginning of the text before the = sign, type ' and press return...


Regards.
 
Hi Cyril, Thank you for the swift response.


It's a good solution but won't work for me as;


1) the if function retuns action planning suggestions based on the response the user give to a series of questions. So if they answer level one in cultural maturity, they get action suggestions that appear as a result of the if function to get them to a level 2 of culture maturity.


2) I need the users to then review the action suggestions to see if they can do them, delete or add to them as necessary.


3) I need to set up a macro or formula to make what is a result of a formula and make it editable text.


4) I cannot have my users interact with the formula - just with the result of the formula.


WOuld it help to post an example?


Many thanks again and fingers crossed this makes better sense.
 
Sorry for my misunderstanding.


An example file would be neat of course, and is always appreciated


Regards.
 
Hi, claudine!

As far as I could see it'd be practical to leave that cell content as it is (formula) and add a new column (next to it is highly recommended) in which the user can accept the calculated value or choose a new one, for example from a drop down validation list with all the 5 specified values.

Regards!
 
Hi claudine,


For the first time in my life i tried a VBA code and that is working!!! :)

[pre]
Code: 
Sub TheSelectCase()
Select Case Range("A3").Value
Case 1
Range("B3").Value = Worksheets("Sheet2").Range("B3").Value
Case 2
Range("B3").Value = Worksheets("Sheet2").Range("C3").Value
Case 3
Range("B3").Value = Worksheets("Sheet2").Range("D3").Value
Case 4
Range("B3").Value = Worksheets("Sheet2").Range("E3").Value
Case 5
Range("B3").Value = Worksheets("Sheet2").Range("F3").Value
End Select
End Sub
[/pre]

..and sample file: http://dl.dropbox.com/u/60644346/claudine_sample.xlsx


@VBA Masters: Please point out mistakes/improvements in above code.


@SirJB7: Adding an additional column will be most suitable way to solve the issue so that one can write something like 'Remarks' there. You are right.


Regards,

Faseeh
 
Dear Cyril, no need for apologies I appreciate your helping me. Will work out how to upload the w/book just in case.


Dear SirJB7, I will add the extra column, perhaps just letting the users type their own actions and then hiding the auto suggestions is a more elegant solution.


Dear Faseeh, I will try this vba and have a look at the sample you very kindly set up.


Very hopeful and grateful to each of you.


Kindest regards,


Claudine
 
Hi, claudine!

Glad you solved it. Thanks for your feedback and for your kind words too. Welcome back whenever needed or wanted.

Regards!


@Faseeh

Hi!

Thanks for your ack.

Regards!


@Faseeh (2)

Hi, again!

As I don't see any user/nick called "VBA Masters" in this topic, I'll dare to write a little, may I?

a) congrats for your successful incursion in the VBA world, keep on going!

b) the code is nearly perfect, you could only optimize it using a With statement for Range("B3") (it'd make Excel cache that range for faster access but here's it'd pass unnoticeable)

c) this statement does the same as your snippet (piece of code):

Code: 
Range("B3").Value = Worksheets("Sheet2").Cells(3, Range("A3").Value + 1).Value

... assuming data in A3 ranges from 1 to 5, otherwise add an If condition at the beginning:

[code]If Range("A3").Value>=1 and Range("A3").Value<=5 Then ...

c) the same validation must be performed from within your structure:

[pre]Sub TheSelectCase()
Select Case Range("A3").Value
Case 1 to 5
Range("B3").Value = Worksheets("Sheet2").Cells(3, Range("A3").Value + 1).Value
End Select
End Sub[/code][/pre]
That's all.

:)

Regards!
 
@Sir JB7,


Thank you sir for the explanation, I will examine it in details for sure..


Thank You,

Faseeh
 
Hello All,


I have had a play with the sample spreadsheet, unfortunately it gives me the same error I had before.


HOWEVER I have found an elegant work around, and provide it here in case it helps anyone else.


In sheet one, two, three four and five - The user selects 1, 2, 3, 4 or 5 in cell A3 according to some definitions they are asked which they identify with.


This formula is in A4 and returns suggested actions to get to the next level;

=IF(A3=1,'Intervention Admin 1'!B3,IF(A3=2,'Intervention Admin 1'!C3,IF(A3=3,'Intervention Admin 1'!D3,IF(A3=4,'Intervention Admin 1'!E3,IF(A3=5,"Work to Maintain and Review.",IF(A3="",""))))))


In sheet six a summary of all their work appears along with all the action suggestions.


I needed the users to be able to interact with the action suggestions, add more or delete some out. Because it is the result of a formula and references one of the other worksheets, I needed to convert the formula result to text.


I had tried paste special, values only. And after editing the actions got a #name error because excel had added an = sign for some unknown reason.


I discovered this was because each of the actions I had written had '- in front of them to look like bullet points. After the paste special values command, excel then read the - as a minus sign and deleted the '.


My workaround was to change all the - to special symbol emdashes.


Lo and behold - it now works.


I would like to thank everyone for helping me, and share a quick woot woot for lateral thinking combined with Excel!


Have a wonderful weekend and wishing you all much happiness.


Claudine
 
You must log in or register to reply here.
Back
Top