# Formula Based on Cell - No VBA

#### nwfinance

##### Board Regular
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.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### etaf

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

#### nwfinance

##### Board Regular
Book2
ABCDEFGHIJ
1LengthWidthHeight
21052SF50=A2*B2SF#REF!
3Volume100=A2*B2*C2
Sheet1
Cell Formulas
RangeFormula
G2G2=A2*B2
H2:H3H2=FORMULATEXT(G2)
G3G3=A2*B2*C2
J2J2=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.

#### nwfinance

##### Board Regular
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,))

##### Well-known Member

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.
`=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
ABCDEFGHIJKLMN
1Data 1Data 2Data 3Data 4Data 5Data 6Data 7Data 8SFCalcIndicatorAction
222.5011220.5255.00SF5SFA{ROW}*B{ROW}
554.0043552.7553.00Volume74.75
664.5054663.564.00SF135
885.5076885899.00Volume120.5
996.0087995.75922.00SF432
10116.5098116.51123.00SF71.5
Sheet1
Cell Formulas
RangeFormula

#### nwfinance

##### Board Regular
@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

##### Well-known Member

Edit the formulas as required

T202012a.xlsm
ABCDEFGHIJ
1Data 1Data 2Data 3Data 4Data 5Data 6Data 7Data 8SFAmount
222.511220.5255SF5
55443552.7553Volume80
664.554663.564SF27
885.576885899Volume308
99687995.75922SF54
10116.598116.51123SF71.5
7c
Cell Formulas
RangeFormula

#### nwfinance

##### Board Regular
Edit the formulas as required

T202012a.xlsm
ABCDEFGHIJ
1Data 1Data 2Data 3Data 4Data 5Data 6Data 7Data 8SFAmount
222.511220.5255SF5
55443552.7553Volume80
664.554663.564SF27
885.576885899Volume308
99687995.75922SF54
10116.598116.51123SF71.5
7c
Cell Formulas
RangeFormula

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

##### Well-known Member
With Named formulas
T202012a.xlsm
ABCDEFGHIJK
1Data 1Data 2Data 3Data 4Data 5Data 6Data 7Data 8SFAmountAmount
222.511220.5255SF55
55443552.7553Volume8080
664.554663.564SF2727
885.576885899Volume308308
99687995.75922SF5454
10106.598116.51123SF6565
7c
Cell Formulas
RangeFormula

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.

Last edited:

#### Dave Patton

##### Well-known Member
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
ABCDEFGHIJ
1
2Data 1Data 2Data 3Data 4Data 5Data 6Data 7Data 8SFAmount
322.511220.5255SF5.00
7c
Cell Formulas
RangeFormula

Replies
0
Views
41
Replies
1
Views
140
Replies
28
Views
946
Replies
19
Views
288
Replies
3
Views
61

1,127,420
Messages
5,624,695
Members
416,042
Latest member
Oden

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back