• 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

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?

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

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

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.

Please help me...

sorry @vletm for the confusion.

Please revert if any questions.


Thanks,
Vignesh M
 

Attachments

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.
 

vletm

Excel Ninja
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

navic

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

vletm

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

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

deciog

Active Member
Peter, Good Morning.

Ecelente logic and result

I learned one more

Thank you very much

Decio
 
Top