# Convert a FORMULATEXT into Formula

#### Vignesh M

##### New Member
Dears,

I have created a excel template to apply the Proposed Billed Date of Customers (please refer the attached excel sheet). I added a keyword in the column "I", and the formulatext in the column "J". I planned to use "VLOOKUP" function to apply the formulatext to the corresponding keywords in the Customer Billing file but I require a function which works exact opposite of "FORMULATEXT" to generate the billing date. Can anyone please help me on this.

Many Thanks,
Vignesh M

#### Attachments

• 11.3 KB Views: 18

#### vletm

##### Excel Ninja
Vignesh M
Curiosity Killed A Cat ...
Why You would like to do that way?
Why You cannot have formulas ... in I-column?
= Why do You would NEED to use extra steps?

#### Vignesh M

##### New Member
Vignesh M
Curiosity Killed A Cat ...
Why You would like to do that way?
Why You cannot have formulas ... in I-column?
= Why do You would NEED to use extra steps?

Hi, This is just a lookup file, I am maintaining a seperate customer master billing file which has more than 20,000 line items. I require to find out the Billing date of every customers.

► Every Customer account has a separate free period which is available in the Column "B". (I haven't shared the entire Billing Term, just shared some for understanding).
► I need to calculate the actual date to be billed for the customers using the Billing Term.
► The category "A","B", etc will be available in the accounting tool for the respective customers. I will export the dump of data and will calculate the date to be billed.
► I created a separate lookup file (attachment) with the unique formulas for the Categories "A","B" etc.
► Now I will apply the formulas based on the Categories using VLOOKUP function but I need those to be converted as Excel Formula.

I googled and find out some way in VBA called Evaluate, but it's not working with INDIRECT Function.

Thanks,
Vignesh M

#### bosco_yip

##### Excel Ninja
Try,

1] Changed the "Lookup Table" as per range I1:K6

2] Filled column D "category" all blank cells with data.

3] In C2 "Blling proposed date", copied down :

=IF(A2="","",(INDIRECT("A"&ROW())+7)+(7-WEEKDAY(INDIRECT("A"&ROW()),LOOKUP(D2,\$I\$2:\$J\$6))))

Regards
Bosco

#### Attachments

• 12.1 KB Views: 5

#### vletm

##### Excel Ninja
Vignesh M
Did You answer any of my 'why..'-questions?
I could continue asking more 'why'-questions
... eg why to use just (whatever that would mean) a lookup file?

You also let me understood that sample 'just'-file is not same kind of as You original ... file.
If something 'feels like a challenge' then there could be shorter more 'cost-economy-way' to do things.

> I need to calculate the actual date to be billed for the customers using the Billing Term. <

What needs to do that You would get needed results?
How could do that ... manually?
Could You make some more realistic samples - which shows inputs and needed outputs?

... if needs after #4 reply.

#### Peter Bartholomew

##### Well-Known Member
I share @vletm's concerns. The formulas are the same to within a numeric parameter which can be looked up.

#### Attachments

• 19.9 KB Views: 10

#### Vignesh M

##### New Member
Dear All,

My Apologies to everyone. Please disregard my original post. Now I am clearly stating my requirement below.

The attached file has two different tabs.
1. FormulaSheet:
- This sheet will have formulas for calculating the "Billing Due Date" of every customers based on their Billing Category.

2. Result:
- This sheet is extracted from SAP (Accounting/Invoicing Tool). I kept only the columns "Date to be considered for billing" and "Billing logic type" since I am unable to upload the entire data.
- Column "C" is the Due Date for Billing, which is calculated based on the Billing Category "A","B","C" etc. The desired formula is now available in the Columns "C" but I am unable to evaluate it to bring the result.

sorry @vletm for the confusion.

Thanks,
Vignesh M

#### Attachments

• 197.1 KB Views: 8
• 172.4 KB Views: 10

#### Chihiro

##### Excel Ninja
Copy Column C and Paste as Values into Column D.

Then use CTRL + H. To replace "=" with "=".

This will force evaluation of formula string. Then you can copy Column D and paste back to Column C (you can then delete D or keep as desired).

NOTE: Typically, you can use Named Range holding =EVALUATE(Cell Reference) to evaluate formula string. But I believe, it doesn't work with your set up due to INDIRECT being used in the formula string.

#### navic

##### Active Member
Hi VigneshM
You may need to add the SheetName Tab in the formula, like
Code:
``'=EOMONTH(FormulaSheet!A32+7,0)``

#### Attachments

• 36.6 KB Views: 3

#### vletm

##### Excel Ninja
Vignesh M
Excel has a lot ... a lot of functions ... okay.
Depends case, only some functions, would be more useful than others.
If I would use something like Your sample, You could do it this way. It's a sample!
I didn't start to copy or modify Your formulas ... because, seem that my Excel-version uses those ... different way.
You can modify those as You need
or
Use a macro, which would do same... less copy&paste.

#### Attachments

• 49 KB Views: 4

#### Peter Bartholomew

##### Well-Known Member
I made some progress by substituting a named relative reference in place of the INDIRECT that was such a problem. The EVALUATE function referred to within the named formula 'result'.

#### Attachments

• 255.4 KB Views: 7

#### navic

##### Active Member
My apology for the post above. Please ignore it. I did not immediately figured what you want.

I have a new proposal. Given that you have lots of rows, it may be good to use the VBA.
Run the VBA macro then manually replace the comma with the comma.
The result is the dates from the formula, based on the date in column 'A'.
Code:
``````Sub CopyRange()

Range("C2:C7084").Copy
Range("D2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

ActiveSheet.Range("C:C").EntireColumn.Hidden = True
ActiveSheet.Range("D2:D7084").NumberFormat = "d.m.yyyy"

'next step is select manualy column D and replace comma with comma

Application.CutCopyMode = False

End Sub``````

#### Attachments

• 137.4 KB Views: 1

#### vletm

##### Excel Ninja
navic
... to whom is #12?

Your: I (navic) did not immediately figured what you want.
If You refer me, then ... You would like to get the results, those dates.
I would like to give one possible solution to do it.

Why someone would like to copy ... many formulas?
If someone ... else then free to do it...

#### Peter Bartholomew

##### Well-Known Member
I have returned to my proposed solution and done some tidying up.
As others have suggested, the answer to your question is the old Excel Macro Language (XLM) function EVALUATE.
You cannot, however, use EVALUATE() from a worksheet cell; for that you need the somewhat more powerful Named Formula context.

My final formula 'DueDate' is
= EVALUATE( LOOKUP( logicType, Formula ) )
but that relies upon named relative references ETD and logicType to pick up (obvious) data from the current row of your Result sheet.

The other named reference Formula is a restructured version of the information you provided on the formula sheet in which the formulas are sorted by logic type. I have also replaced the formula fragment INDIRECT("A"&ROW()) by references to ETD. I did this manually using Ctrl-H rather than the SUBSTITUTE function because it is a one-off process (your indirect formula was only needed because the A1 notation is so appallingly bad. Even using the R1C1 notation, you could have used RC1 to reference the dates).

p.s. The file no longer has any VBA macros

#### Attachments

• 211.8 KB Views: 9

#### Vignesh M

##### New Member
Thanks everyone for the effort. Special thanks to Peter Bartholomew #14 is really awesome. Learned new things about Name Manager, VBA function etc.

#### deciog

##### Active Member
Peter, Good Morning.

Ecelente logic and result

I learned one more

Thank you very much

Decio