Excel 2007 : Text to formula

[Burgaw]

Greetings!

I have several hundred rows of text that I need to change to formula.

a sample is listed below, it was made merging several TEXT columns tegether to get the needed code/formula to advance numbers. I need help converting from text to a formula on another sheet where it can read and keep track of the numbers if drawn.

Code in brackets as shown is a text cell on sheet1 A1:

[ =AND(COUNTIF(DRAWS!B2:F2,1),COUNTIF(DRAWS!B2:F2,48),COUNTIF(DRAWS!B2:F2,59))*1 ]

I need to copy the text to sheet2 A1 and us it as a formula

I think I need to use INDIRECT but don't understand how to make it work

I seen how to convert text to a formula before on the forum but can not find the answer. Any help would be appreciated!

Thanks in advance

[DonkeyOte]

INDIRECT won't suffice I'm afraid - that particular function converts strings to ranges (only)

You will need to use Evaluate - either via a User Defined Function (VBA) or via an old XLM call installed via Names.
If as implied by profile you are using XL2003 (ie pre XL2007) the latter may be preferable as there are no macro type requirements.

With Sheet2!A1 the active cell add the following name:

Please Login or Register  to view this content.

In Sheet2!A1 enter:

Please Login or Register  to view this content.

The use of INDIRECT in the Evaluate is to ensure the Name is Volatile and as such Sheet2 will recalculate at all times (implicit precedents)

[martindwilson]

cant you just copy it making sure other sheet is formated general then bulk find replace = with =

[Burgaw]

Thanks for the input but all I get is Function not valid or an error

[DonkeyOte]

Not clear to whom your prior post was directed but both suggestions should work assuming the Sheet1 formula constructions are valid in the first instance.

The Evaluate method assumes the formulae may change over time etc and sheet2 should update automatically.
If this is a one off task then the copy & paste values, find & replace of = operator is certainly the better option.

Attached is a file demonstrating the Evaluate method.

[Grumpy88]

Hi. I have a similar issue, but can't quite get my head around the proposed solutions stated here. I have a whole bunch of columns containing pure numbers, that I want to convert in a once-off batch to formulas (i.e. programmatically place an equal to sign in front of each number, without having to do this manually in each cell).
I need to do this because I have used the Ctrl & H Replace function to convert the decimal fraction portions of certain of these numbers to ordinary numerator/denominator fractions (e.g. "2.33" has been changed to "2+2/6"), so that the final Sum formula of each column is more accurate. However, without an equals to sign in front, the Sum formula no longer includes such cells in its answer.

Any ideas how I can quicklu use the Replace function again to add equal to signs to each cell?

Thanks.