A formula that returns a formula (not the value)

[Shawn Miller]

Hello,

Not sure I am describing what I am aiming to do. I am trying to do some kind of LOOKUP or INDEX/MATCH that returns the actual formula from somewhere else (not the value.)

Attached is an example of what I am trying to do. Right now I do many manual steps to make this work by naming ranges, adding text, then copy/paste, find/replace, etc (I have outlined my steps in gray at the bottom.) Is there an easier way to accomplish what I want and just use one formula in the yellow cells? I have searched and searched and have not been able to find something that helps.

I would prefer just a function but open to learning a VBA option (my VBA skills are minimal.)

Thank you!

 

[vletm]

I changed [E9] formula.
I almost solved with only formula, still missing step from [D] to [E]
... but [F] has it with one formula
but with VBA, it works
press [test] to run

 

[p45cal]

Have a play with and check carefully the attached; it uses an Excel 4.0 Macro function Evaluate in defined names, so even though there is no macro/vba it will still ask you to enable macros. Shouldn't be too difficult for you to tweak those defined names for a model on a different sheet.
Note that the text in cells E7:E9 are the formulae in cells C7:C9 but without the preceding = sign.

 

[Shawn Miller]

I changed [E9] formula.
I almost solved with only formula, still missing step from [D] to [E]
... but [F] has it with one formula
but with VBA, it works
press [test] to run

Thank you for the reply. I am trying to make this dynamic because the model I am referencing has 100+ formulas (I just used 3 for an example.) Do you think there is a way to do this?

 

[Shawn Miller]

Have a play with and check carefully the attached; it uses an Excel 4.0 Macro function Evaluate in defined names, so even though there is no macro/vba it will still ask you to enable macros. Shouldn't be too difficult for you to tweak those defined names for a model on a different sheet.
Note that the text in cells E7:E9 are the formulae in cells C7:C9 but without the preceding = sign.

Thank you so much for the reply. I see the named ranges but not sure I understand how it works. Also, is there a way for it to pull in the actually formula? I could live without but that would help. Also, I will have to look into this more and see if this works....and it can be applied to reference a model with 100+ formulas...then I think this would do it!

 

[NARAYANK991]

Hi Shawn ,

Can you not upload a workbook which is exactly what your final workbook will be like , except for the number of formulae being 3 instead of 100+ ?

Instead of writing that the model will be on a different tab , why not put it in the sample workbook in that manner ?

The more the sample workbook differs from your actual working file , the greater the chance that the suggested solution will need to be tweaked to suit.

Narayan

 

[deepakbathla]

Greetings of the day.....!

Good Morning All,

Sir's, I want to know how to come data in sequence in (E5 Column).
I copied this sheet somewhere.


Thanks/ Regards
Deepak Bathla

 

[NARAYANK991]

Hi Bathla ,

Since you have already posted several times in this forum , please ensure you start a new thread for a question which is no way related to this thread.

To answer your question , it is not so simple , especially if there is going to be a lot of variation in the data ; when you start a new thread , please upload a workbook which has enough data , not just one cell.

Narayan

 

[p45cal]

I see the named ranges but not sure I understand how it works. Also, is there a way for it to pull in the actually formula? I could live without but that would help. Also, I will have to look into this more and see if this works....and it can be applied to reference a model with 100+ formulas...then I think this would do it!

In the attached I have separated the Model onto a separate sheet called Model. I hope you only have one model!
On that Model sheet I have added a formula:
=frmla
in the cells immediately to the right of your actual formulae in column C.
frmla is a defined Name which uses old XLM 4 functions which are stll supported in current versions of Excel. This Name shows the formulae in column B as strings in column C.
(Using the formula in any cell will show the formula of the cell to its immediate left as a string.)

If you have Excel2013 or later, you can delete that defined name and use the built-in =FORMULATEXT() instead… …and there's another way to get just text strings: copy the original formulae to somewhere else (pay attention that the range refs within the formulae remain correct regarding absolute/relative refs), format those cells as TEXT using the format cells dialogue, then starting at the topmost cell, press F2 then Enter, repeat as often as necessary. This will quickly work down the list converting the formulae to text strings.

I have removed Names eval1, eval2 etc. as this would be cumbersome with 100+ formulae. In their place there is one defined Name, evaly, which again uses XLM 4 terms. If used in any cell on any sheet (eg. =evaly), it will use the cell value 2 cells to the left (you've used Task A, Task B etc.) to lookup on the Model sheet which formula string to use, and the cell immediately to the left as a value (date) to plug into that formula. That defined name uses a table on the Model sheet in cells B7:D209 which will give you room to add some 200 more Tasks/formulae.
I added 'AnotherSheet' being a copy of your Sheet1 just to check it worked on multiple sheets.

It would be more future-proof and easier if you allowed macros, or at least user-defined functions, but this at least seems to work; YOU MUST CHECK THOROUGHLY, though.

 

[Shawn Miller]

First, thank you for all the feedback! I see the limitation with using these "older" options in Excel. I am not opposed to user defined functions or VBA, it is just I have no experience with user defined functions and I am very light VBA user.

Attached is maybe a better example. I have my deadline model as one tab. Essentially we have 4 different deadlines for each US state that we work with and it is based on either a start date or adjusted date. On the "DesiredReport" tab I am trying to fill in the yellow section with formulas to get deadlines (this logic will be applied to 80k records each month.)

I am trying to tell Excel (on the desired report tab) to go to the deadline model and determine based on the deadline type (deadline 1, deadline 2, etc.), use the formula for the deadline BUT apply it to each individual record on the desired report tab. What makes this tricky is the deadline for each deadline type can be based on start date or adjusted date, and can vary by deadline type or by state.

What is the best way to do this and make it so it can be managed going forward? Again, I am open to any option, especially if avoiding VBA is too time consuming.

 

[p45cal]

What makes this tricky is the deadline for each deadline type can be based on start date or adjusted date, and can vary by deadline type or by state.

How do you decide whether to use the start date
or the adjusted date? Are there some rules to follow?

 

[Shawn Miller]

Great question...it depends on the state, and also which of the 4 deadlines. The formulas in the model reference one of 2 dates listed at the very top of the model so the "rules" are already built in to the model. Most states rely on just one or the other date but look at AZ...deadline 1 is based on start date, and deadline 2 and 3 are based on adjusted date.

 

[p45cal]

There are only 40 states - is that the lot? Is there any particular reason for the drop down cell G4? Working on this…

 

[p45cal]

Some checking on the attached please.

 

[Shawn Miller]

Wow I think this works and this is really cool. Although I dont really understand how those functions work. It is really the evaly and frmla functions right? If I can learn how to adjust this to a larger dataset I think this would be great!

Thank you for all your help.

 

[p45cal]

I dont really understand how those functions work. It is really the evaly and frmla functions right?

Yes.
With formulae in defined Names it's important to remember that the formula that appears in the Name Manager will change according to which cell is the active cell on the sheet at the time you're looking at the formula there.
If you select H22 on the DeadlineModel sheet, then go into Name Manager you'll see that the formula for frmla is:
=GET.CELL(6,!C22)
The C22 part of the formula is 5 columns to the left of H22 (C v. H) and in the same row (22) because the C22 part is relative both for column and row (no $ symbols).
If you do the same thing with say J29 selected, the formula appears in Name Manager as:
=GET.CELL(6,!E29)
From these observations you can see that when you use frmla, that it refers to a cell 5 cells to the right, and on the same row, of the cell it's used in. This is a change to the previous workbook I posted where it referred to the cell immediately to the left. This accommodates the larger table of formulae on the Model sheet.

The same applies to evaly.
Choose the single cell G7 on the Report sheet then go into Name Manager and look at the evaly formula and you see:
=EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(VLOOKUP(!$C7,DeadlineModel!$A$22:$J$61,COLUMN(!G7)+1,FALSE),"$I$4",MONTH(!$D7)),"$J$4",DAY(!$D7)),"$K$4",YEAR(!$D7)),"$M$4",MONTH(!$E7)),"$N$4",DAY(!$E7)),"$O$4",YEAR(!$E7)))

The result you get in that cell is the same as entering:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(VLOOKUP($C7,DeadlineModel!$A$22:$J$61,COLUMN(G7)+1,FALSE),"$I$4",MONTH($D7)),"$J$4",DAY($D7)),"$K$4",YEAR($D7)),"$M$4",MONTH($E7)),"$N$4",DAY($E7)),"$O$4",YEAR($E7))
directly into G7.
There are 6 SUBSTITUTions going on, one for each of the 6 cells ($I$4:$K$4,$M$4:$O$4) on the Model sheet that the formulae on that sheet refer to. The middle part of that fomula:
VLOOKUP($C7,DeadlineModel!$A$22:$J$61,COLUMN(G7)+1,FALSE)
is looking up the state ($C7) in column C in the first column of the table on the Model sheet (DeadlineModel!$A$22:$J$61 (fully absolute references so that the same table is always used)) and returns what it finds in the 8th column of that table (COLUMN(G7)+1 (note that G7 there is the same as the selected cell so COLUMN(G7) returns 7 (from the G column, not the 7th row!) and adds 1 to it). The FALSE part of the formula is to ensure an exact match is found.
So the VLOOKUP part of the formula returns the formula string in the row corresponding to the state and the column corresponding to the deadline number.
In this case (with G7 selected) that string is Deadline2 for AZ:
=IF(DATE($O$4,$M$4,$N$4)<=DATE($O$4,6,30),DATE($O$4,8,15),DATE($O$4+1,2,15))
Here all the cell references are going to be substituted and I'll just take one of them as an example, $O$4.
$O$4 gets substituted by YEAR($E7), E7 contains the adjusted date July 31 2015, so YEAR($E7) returns 2015 so the formula becomes:
=IF(DATE(2015,$M$4,$N$4)<=DATE(2015,6,30),DATE(2015,8,15),DATE(2015+1,2,15))

The same happens for the other SUBSTITUTIONS.

That's it.

There are a few things you can do to help you understand what's going on; one of them is to select a cell with =evaly in and click the Evaluate formula button on the Formulas tab, then use Step in at the first opportunity to follow the calculation steps Excel uses in the calculation.

If I can learn how to adjust this to a larger dataset I think this would be great!

For my last attachment, if there are no more than 40 states, the only enlargement required would be to copy the evaly formulae down on the Report sheet and fill in some data in columns C:E for the formulae to work on.

Apart from that, armed with the above notes, you should be able to tweak the defined names to accommodate the layout of your data on each sheet.

 

[Shawn Miller]

Wow...thank you, thank you, thank you! This is really cool and I now want to go learn more about these functions. Plus I never use the substitution formula so I will look at this more. Thank you again, I really appreciate you showing me this!