Formula Based on Cell - No VBA

[danielphayward]

In columns A:U I have data from an external source (downloaded CSV that always comes in the same format).

Depending on what I enter into column V I will have up to 7 different types of data in each row; simple example would be square feet, which is the height and width columns multiplied together for that row.

Right now, I just have a long nested if statement that does the job, kind of, but as new types of data are needed then I have to go in and edit the formulas for the entire sheet.

What I would like to do is have it go to a look up table on another sheet and pull the formula depending on the measurement, if that makes sense. I've tried to make it work with indirect and arrays, but arrays make my head spin.

 

[etaf]

can you give more examples of how the formula would know what to use , maybe the nested IF with an explanation

 

[danielphayward]

Book2
	A	B	C	D	E	F	G	H	I	J
1	Length	Width	Height
2	10	5	2			SF	50	=A2*B2	SF	#REF!
3						Volume	100	=A2*B2*C2
Sheet1

Cell Formulas
Range	Formula
G2	G2	=A2*B2
H2:H3	H2	=FORMULATEXT(G2)
G3	G3	=A2*B2*C2
J2	J2	=INDIRECT(VLOOKUP(I2,F:H,3,0))

The Formula that's #N/A is essentially what I'm trying to do, based on whethere i2 is SF or Volume. Let me know if that doesn't help.

 

[danielphayward]

The nested if is simple enough, for the above example it would be something along the lines of...

=IF(I2="SF",A2*B2,IF(I2="Volume",A2*B2*C2,))

 

[Toadstool]

Hi NWFinance,

You explicitly state "No VBA" but the action you require is perfect for the VBA EVALUATE function. If you're OK with some back door VBA which requires you to save as a macro-enabled workbook then we can access EVALUATE through the Formulas, Name Manager.

I've created a table as it looks like column I decides what calculate to execute. The action is noted in the next column of the table with the row number replaced with "{ROW}" which is just a placeholder for the actual row number to be substituted later.
Now I add the Name ToadCalc and have it refer to:
=EVALUATE(SUBSTITUTE(INDEX(Table1[Action],MATCH(Sheet1!$I1,Table1[Indicator],0)),"{ROW}",ROW()))

When I want the calculation I enter =ToadCalc. You see I've forced it to be volatile with the TODAY() function as otherwise it won't recalculate when your data changes, =ToadCalc+(0*TODAY())

Does that work for you?

NWFinance.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Data 1	Data 2	Data 3	Data 4	Data 5	Data 6	Data 7	Data 8	SF		Calc		Indicator	Action
2	2	2.50	1	1	22	0.5	2	55.00	SF		5		SF	A{ROW}*B{ROW}
3	3	3.00	2	2	33	1.25	3	88.00	Add		9		Volume	A{ROW}*B{ROW}*C{ROW}
4	4	3.50	3	3	44	2	4	2.00	Add		60.5		Add	A{ROW}+B{ROW}+D{ROW}+E{ROW}+F{ROW}+G{ROW}
5	5	4.00	4	3	55	2.75	5	3.00	Volume		74.75
6	6	4.50	5	4	66	3.5	6	4.00	SF		135
7	7	5.00	6	5	77	4.25	7	5.00	Add		35
8	8	5.50	7	6	88	5	8	99.00	Volume		120.5
9	9	6.00	8	7	99	5.75	9	22.00	SF		432
10	11	6.50	9	8	11	6.5	11	23.00	SF		71.5
Sheet1

Cell Formulas
Range	Formula
K2:K10	K2	=ToadCalc+(0*TODAY())

 

[danielphayward]

@Toadstool That does work for me, I think. It depends on my boss whether he's comfortable with macro enabled workbooks or not.

 

[Dave Patton]

Edit the formulas as required

T202012a.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Data 1	Data 2	Data 3	Data 4	Data 5	Data 6	Data 7	Data 8	SF	Amount
2	2	2.5	1	1	22	0.5	2	55	SF	5
3	3	3	2	2	33	1.25	3	88	Add	45.25
4	4	3.5	3	3	44	2	4	2	Add	60.5
5	5	4	4	3	55	2.75	5	3	Volume	80
6	6	4.5	5	4	66	3.5	6	4	SF	27
7	7	5	6	5	77	4.25	7	5	Add	105.25
8	8	5.5	7	6	88	5	8	99	Volume	308
9	9	6	8	7	99	5.75	9	22	SF	54
10	11	6.5	9	8	11	6.5	11	23	SF	71.5
7c

Cell Formulas
Range	Formula
J2:J10	J2	=CHOOSE(MATCH(I2,{"Add","SF","Volume"},0),A2+B2+D2+E2+F2+G2,A2*B2,A2*B2*C2)

 

[danielphayward]

Edit the formulas as required

T202012a.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Data 1	Data 2	Data 3	Data 4	Data 5	Data 6	Data 7	Data 8	SF	Amount
2	2	2.5	1	1	22	0.5	2	55	SF	5
3	3	3	2	2	33	1.25	3	88	Add	45.25
4	4	3.5	3	3	44	2	4	2	Add	60.5
5	5	4	4	3	55	2.75	5	3	Volume	80
6	6	4.5	5	4	66	3.5	6	4	SF	27
7	7	5	6	5	77	4.25	7	5	Add	105.25
8	8	5.5	7	6	88	5	8	99	Volume	308
9	9	6	8	7	99	5.75	9	22	SF	54
10	11	6.5	9	8	11	6.5	11	23	SF	71.5
7c

Cell Formulas
Range	Formula
J2:J10	J2	=CHOOSE(MATCH(I2,{"Add","SF","Volume"},0),A2+B2+D2+E2+F2+G2,A2*B2,A2*B2*C2)

That doesn't solve my problem because I'll still have to adjust all the formulas if I add something besides the initial version.

 

[Dave Patton]

With Named formulas

T202012a.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Data 1	Data 2	Data 3	Data 4	Data 5	Data 6	Data 7	Data 8	SF	Amount	Amount
2	2	2.5	1	1	22	0.5	2	55	SF	5	5
3	3	3	2	2	33	1.25	3	88	Add	45.25	45.25
4	4	3.5	3	3	44	2	4	2	Add	60.5	60.5
5	5	4	4	3	55	2.75	5	3	Volume	80	80
6	6	4.5	5	4	66	3.5	6	4	SF	27	27
7	7	5	6	5	77	4.25	7	5	Add	105.25	105.25
8	8	5.5	7	6	88	5	8	99	Volume	308	308
9	9	6	8	7	99	5.75	9	22	SF	54	54
10	10	6.5	9	8	11	6.5	11	23	SF	65	65
7c

Cell Formulas
Range	Formula
J2:J10	J2	=CHOOSE(MATCH(I2,{"Add","SF","Volume"},0),A2+B2+D2+E2+F2+G2,A2*B2,A2*B2*C2)
K2:K10	K2	=CHOOSE(MATCH(I2,{"Add","SF","Volume"},0),Add,SF,Volume)

Name manager name Add =!A2+!B2+!D2+!E2+!F2+!G2 edit to relevant cells

"That doesn't solve my problem because I'll still have to adjust all the formulas if I add something besides the initial version."

You would have to edit the range for the Match (add the new category of calculation) and Name the calculation in Name Manager
Name the formulas while in the first row for calculations 2
Fill down.

 

[Dave Patton]

Test of new version of XL2BB
This is posted just in case someone reads the post and tries the solution.

names
Calc ={"Add","SF","Volume"} in alphabetic order (not on previous post)
SF =!$A3*!$B3 set up formula while in first row of your data

T202012a.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2	Data 1	Data 2	Data 3	Data 4	Data 5	Data 6	Data 7	Data 8	SF	Amount
3	2	2.5	1	1	22	0.5	2	55	SF	5.00
7c

Cell Formulas
Range	Formula
J3	J3	=CHOOSE(MATCH($I3,Calc,0),Add,SF,Volume)