Lookup for Formulas?

[ikslohap]

Or formulae, if you prefer? I have a spreadsheet with nine columns of various data values, and a tenth column that contains a flag value (N, or S, or L, or NW, etc.). The flag identifies the nature of the data in that row (long story, not important for my question).

The *** ELEVENTH *** column needs to calculate a value using a formula, and the formula is different for each possible flag. I'm wondering if I can build a lookup table that contains the flags and the associated formulas, so my 11th column will in each case produce the correct value for each row's assigned flag. For example, on row 1 the N flag would use A1*A4/A6, on row 2 the L flag would use (A2-A7)*A3, and so on.

I'm sure I can do this with a custom VBA function, but would rather not go that route with this particular customer if a lookup table can do what I need. That way they can tweak any of the formulas without having to get into VBA coding.

[Glenn Kennedy]

Will you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[ikslohap]

Okay, here's a small sample.
First nine columns are actual data, "sanitized" for confidentiality -- mostly just removing identifying column headers
Column ten contains flags
Column 11 is text entries illustrating what the formulas look like for various flags
Column 12 is text entries of the lookups I hope would produce the desired result
And at right is the lookup table I'm envisioning. Of course I realize I'd have to use relative cell references there rather than absolute references.

So, is something like this even possible?

[Glenn Kennedy]

Ummmm.. Your attachment dropped off somewhere...

[samba_ravi]

your formula in the cells are confusing,
please show the expected result with formula for each case
in K2 for NW
K4 for N
K6 for L
K8 for LLF and
in K12 for S.
hope you understood what I mean

[Glenn Kennedy]

This is still pretty obscure...

=((E5-D5)/D5) + G5

So... irrespective of the date, this ALWAYS refers to 4/12/17. yes or no. If yes, Why? Why not another date?? If no, more details, please.

[kvsrinivasamurthy]

May be this
In L2 then drag down

Please Login or Register  to view this content.

[ikslohap]

The issue is not the formulas, they are a known and do what they are supposed to if entered manually. It's whether formulas "stored" in a lookup table can be pulled into the spreadsheet using a VLookup driven by the flag value. I know I can pull in text or numeric values from a lookup table, just not sure if I can pull in a (relative cell reference) formula that way and expect it to work. Thought I'd get a simple yes or no, VLookup can or cannot do that, but it looks like I just need to try it and see what happens. (The last reply, though, with the nested if's, is also a possibility, although it would involve a dozen or so if's, not just the 4-5 in my example, and as I recall there's some limit on that. Thanks for that, will consider it as an alternative.)

[samba_ravi]

can use match and choose function.

[ikslohap]

Hmmm, not familiar, will educate myself. Thanks.

[ikslohap]

So, just to close this out, the answer would appear to be no. Storing formulas in a vlookup table won't allow them to be pulled into a spreadsheet based on some flag value. In the lookup table the formulas are nothing more than text, and pulling them into the spreadsheet yields TEXT, not an actual formula that can manipulate values in other cells. Even relative cell references don't appear to solve that problem. If I've missed something feel free to set me straight, but it appears I'll have to play around with a custom function or a really heavyweight nested If formula.

[JeteMc]

There is a way to do this, although I don't think that the results are going to be what you expect. This tutorial explains the EVALUATE function.
First remove the '= from the front of the text values in column O
Second define the Flag as the name for each of the text values in column O (see the name manager)
Third define a name such as Result with the following refers to: =EVALUATE(INDIRECT('Three Signal'!J2))
Fourth in the first cell where you want the result, I chose M2, type: =Result
Fifth fill down
Notice: that the results in column M all reference the same cells, that is the formulas are absolute not relative to their rows. As in when S is in column J it will always multiply the value in C11 by -1. I believe this would have been the same result if your VLOOKUP had worked. If that is not your desired outcome I would suggest that you reconsider kvsrinivasamurthy's suggestion above.
Let us know if you have any questions.

[ikslohap]

Agree. Evaluate would have done it for me, although I'd still have had to get into .xlsm mode, which I had hoped to avoid with this client. But as Redmond has apparently dropped that function with 2013 and beyond, will have to stick with what the customer had, namely a dozen columns showing the results for each possible flag, and a "copy down" of those forumae every time the customer runs an update, and a "whatever it says over in column R" formula where I was hoping for a simpler solution. But I'm sure Microsoft calls it progress. Thanks to all here who tried to solve the apparently unsolveable.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[FDibbins]

Depending on how many "codes" you have, some form or nested IF's may work?

[FlameRetired]

Or possibly CHOOSE driven by MATCH?

=CHOOSE(MATCH(J2,{"NW","N","L","LLF","S"},0),"formula 1","formula 2","formula 3","formula 4","formula 5")

[ikslohap]

Nope, too many flag codes, 12 as I recall.

[FDibbins]

If it is only 12, you could still use nested IF's - may get messy, but still do-able