• 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.

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

  • Billing_Logic_Final.xlsx
    11.3 KB · Views: 24
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?

... Have You 'googled'?
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.

Can anyone please help me.


Thanks,
Vignesh M
 
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

  • Billing_Logic_Final(BY).xlsx
    12.1 KB · Views: 6
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. <
Is above Your real challenge?
Your thread is 'Convert a FORMULATEXT into Formula'.

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.
 
I share @vletm's concerns. The formulas are the same to within a numeric parameter which can be looked up.
 

Attachments

  • Billing_Logic_Final (PB).xlsx
    19.9 KB · Views: 13
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.

Please help me...

sorry @vletm for the confusion.

Please revert if any questions.


Thanks,
Vignesh M
 

Attachments

  • Billing_TestFile.xlsm
    197.1 KB · Views: 8
  • Screenshot.jpg
    Screenshot.jpg
    172.4 KB · Views: 16
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.
 
Hi VigneshM
You may need to add the SheetName Tab in the formula, like
Code:
'=EOMONTH(FormulaSheet!A32+7,0)
 

Attachments

  • VigneshM-navic42035.xlsb
    36.6 KB · Views: 3
Vignesh M
Still waiting for answers ...
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

  • Billing_TestFile.xlsb
    49 KB · Views: 4
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

  • Billing_TestFile (PB).xlsm
    255.4 KB · Views: 9
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

  • VigneshM-navic42035-2.xlsb
    137.4 KB · Views: 1
navic
... to whom is #12?
You haven't give answers...

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...
 
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

  • Billing_Logic_Final (PB).xlsm
    211.8 KB · Views: 30
Peter, Good Morning.

Ecelente logic and result

I learned one more

Thank you very much

Decio
 
Back
Top