Formula based on cell value

[lil_ern63]

Hello all,

I have researched and found similar questions, but all the results are based on only a few options. I am looking for a solution with potentially unlimited options (okay, not really unlimited, but the list could become rather lengthy). I know I could use a string of embedded IFs, however I am then locked into the formulas defined in the IFs and any updated would have to be extended to multiple rows of formulas.

I have attached a quick and dirty example file.

In short, I will have a line with some user defined attributes (in the example, it will be thickness, length, and width). Then using these attributes, based on the value that is input into column E (in the attached I used a dropdown to force the name), the result in column F would be calculated. I don't have to use a dropdown menu, but thought it would be cleaner and easier to force the exact text needed assuming a lookup formula is used. In the example, I used typical formulas that everyone is familiar with, and then threw in a random length^2 and width^2 to spice it up. The future formulas/calculations could range in complexity of simply addition of two attributes to complex calculation. Future calculations may also be based on more than just the three attributes shown.

My thought was the solution to this problem would be in the green cells, and then copied down what could be dozens or hundreds of rows. The bordered list would also hopefully be expandable. I hope I have explained my problem, and what I am hoping to achieve. I guess part of my requested solution is to also describe how to "adjust" the formulas currently in column K, so if any change is needed in the future, they can be easily updated, and then filter throughout the sheet.

Lastly, I would like to achieve this without VBA if possible, but if necessary, well then it is necessary.

As always, any help is appreciated, and if any additional info is needed, don't hesitate to ask. I hope I didn't ramble too much. This forum has been fantastic at helping me in the past (even when I don't specifically ask the question).

[dflak]

I had to revert to VBA.

First the caveats.

For the formula to work, the data MUST be in an Excel Table and that table MUST be the only table on the sheet. Technically speaking it must be the first table on the sheet, but if it is the only table then it has to be the first. I did this so I did not have to "hard code" a table name and you can use any table name you like.

The formulas are defined in another Excel Table called Table_Formula and this must be on a separate sheet.

The parts of the formula MUST match the column headers in the Data Table. I did not include a way to validate that you do this. Failure to do this will probably result in a #Value! error for the formula.

Having these restrictions in place adds great flexibility to the application. Although the formula parts must be in the column header, it does not matter what order they occur in. Also you can add column headers. For example, you can add a column header for mass and compute density: mass / (thickness*length*width). Yes, you can use parenthesis to force the order of operation.

The data table has validations so you can use the drop down list to select an item.

The program works by getting the Formula from the Formula Table based on the Item selected. The program goes through and finds the thickness, length, etc. on that row and substitutes the value on that row for the formula. For example, LIne 2 is perimeter - the "raw" formula is length*2 + width*2 - the program finds 3 for length and 2.5 for width and "converts" the raw formula into 3*2 + 2.5*2 = 11.00.

As long as you are careful to use the column header names exactly the way they appear on the data table, you can make any formula you want in the Formula Table. The program is tolerant of spaces and case.

[lil_ern63]

dflak,

Impressive to say the least! I will try to incorporate into the actual sheet this is intended for, and see if there are any issues. At a glance, I am concerned about the caveat regarding being the only table. While I am still building the "program" this is intended for, I already have other areas where tables might be most effectively used.

Also, (and my apologies since I haven't implemented and played with your solution yet), is it possible and/or difficult to use this for multiple locations? What I mean is I envisioned using a callout (say perimeter in the example), and while it will do one calculation (length*2 + width*2) in one cell on the sheet, in another perimeter may need to be calculated differently, therefore would require a different formula (i know that sounds odd, since perimeter is perimeter. I guess think of it as length could double as radius, therefore perimeter is also circumference in this case.)

Secondly, will the "raw" formula follow order of operations? While the example formulas are rather simple, the ones I will most likely use can become rather lengthy and cumbersome.

[dflak]

As long as each table is the only table on its sheet, it will be OK. The other option is to name the tables and pass the name of the table to the function. I don't know if the function can get the name of the table that it's in - there probably is a way to do this, but I can't think of one that would do it elegantly.

If you want to make a new formula and call it circumference with the understanding that length is diameter you can do that. On the other hand, you can make a new column called radius. The code is set up to ignore those columns that don't have data necessary for the calculation, so the fact that circumference doesn't use length, width or thickness doesn't matter.

The formulas are evaluated exactly in the order specified by Excel. So 4 * 1 + 3 = 7 and 4 * (1 + 3) = 16.

I just thought of one more caveat. The formulas must be expressed in VBA terminology. This should not be an issue for most formulas. The normal operands and expressions work in both universes. However some "higher" functions do not. For example, the MOD function in Excel is MOD(Number, Base). In VBA it's Number Mod Base.

Likewise there is no native MAX or MIN function in VBA. So if you want to put a "floor" on a calculation so the result is never less than zero you can use MAX(Value,0). In VBA you would have to use If(Value < 0, 0, Value). Yes, the if statement should work. Maybe in some future enhancement, I'll create a Max and Min function and put it in the module.

[lil_ern63]

Thanks dflak.

Like mentioned before, I will do some playing around.

I understand if the best, or only, way to achieve what I am looking for is via VBA. It just throws me quite a way outside of my comfort zone.

I went ahead and posted another example, with a sample from the sheet I have built so far to try and explain the single call out using two different formulas in two different locations. I guess my idea was to change the callout in column Q (pink highlight), and then column R (orange highlight) would use the formula in column E (orange highlight) and then column AA would use the formula in column O (both green highlight).

As you can see the formulas can be rather lengthy, and I wrote them as I have done in excel, just changing out cell reference for the word describing what the cell contains (i.e. length).

Line 20 can be copied down countless times. Also for what its worth. This single sheet may have 10 lines of input, but then an identical sheet can be created in the same workbook with 1 line or 100 or more, and so on.

Not sure if I have cleared up anything, or made matters worse, but again thanks for any and all help.

[dflak]

ROUND() will work; there is a VBA function by that name. ROUNDUP() will not work. Likewise VLOOKUP will not work. My original attempt was to have the function create a string with the appropriate values. However it went into the cell as a string and not as a formula which is exactly the way Excel is supposed to work. If I could have gotten that to work, then you would have your answers.

[lil_ern63]

Well I appreciate your help/attempt. I may have to try a little more of a clumsy solution, where all possible solutions are calculated on that line and then the drop down will tell another formula which solution to pick up. Just worried that many formula will bog the system down.

[lil_ern63]

Would it be possible to used named ranges? I did come across the ability to write a formula and call it Full_Depth. Is there a way to use the dropdown menu and whatever is picked is the named range formula to use? I was thinking along the lines of INDIRECT, but can't get it to work. Not sure if this need to stay in this thread or start a new thread.

[dflak]

I may have something for you tomorrow.

[lil_ern63]

Here is a different example/approach I was thinking.

Like the attached shows, my thought is to have a table with a list of types or callouts that will be shown in a drop down list (to eliminate user input error due to spelling mistakes). That "type" is then used to lookup in a table using VLOOKUP and return the name of a already defined Relative Named Range that would contain an appropriate formula. I just can't get the result of the VLOOKUP to convert into a language/format that excel understands to be callout the named range. Very similar to INDIRECT using a cell reference (and therefore its text contents) to spell out a tab name, or cell location. The contents of the referenced cell in INDIRECT can be updated and therefore the INDIRECT looks at a new location.

Not sure if this should stay in this thread or a new one started. It is a continuation of the same problem/question, but using a different approach.

[dflak]

Here is my "best and final" offer.

The caveats are:
- The formula must be in the first (only) table on the spreadsheet.
- The column containing the drop down list (column E in the example) can be named anything, but if named other than formula, modify the call to the function accordingly: =MakeFormula([@formula])
- The formula must be in the column to the immediate right of the drop down list (column F in the example)
- The answers will appear in the column to the immediate right of the formula (column G in the example)

Again the restrictions keep from hard coding anything. Table name can be anything. You can have any number of columns and they can be named whatever you like. The drop down list can appear in any column as long as the two columns to the right are reserved as noted above.

You can come up with any formula Excel allows. This application works at the Excel and not the VBA level as the previous version did.

One more caveat. The sheet module for the sheet that contains the data table must have the following code:

Please Login or Register  to view this content.

This code is the magic that converts the string returned by MakeFormula into a "real" formula.

[dflak]

Change to the sheet event code. The former code had a hard coded column name.

Please Login or Register  to view this content.

[dflak]

Here is the copy of a "good" file.

[lil_ern63]

dflak,

took a look at your file and this works great! I am still going to do some digging (or posting on here) regarding referencing a named range that contains a formula since I can't visually get away from that approach. If all else fails, I will be able to revert back to your solution and adjust my sheet accordingly.

Thanks again! Marking [Solved]