Formula Based on Cell - No VBA

nwfinance

Board Regular
Joined
Jan 22, 2016
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,657
Office Version
  1. 365
Platform
  1. MacOS
can you give more examples of how the formula would know what to use , maybe the nested IF with an explanation
 

nwfinance

Board Regular
Joined
Jan 22, 2016
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jan 22, 2016
Messages
67
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Mar 5, 2018
Messages
1,432
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
ABCDEFGHIJKLMN
1Data 1Data 2Data 3Data 4Data 5Data 6Data 7Data 8SFCalcIndicatorAction
222.5011220.5255.00SF5SFA{ROW}*B{ROW}
333.0022331.25388.00Add9VolumeA{ROW}*B{ROW}*C{ROW}
443.503344242.00Add60.5AddA{ROW}+B{ROW}+D{ROW}+E{ROW}+F{ROW}+G{ROW}
554.0043552.7553.00Volume74.75
664.5054663.564.00SF135
775.0065774.2575.00Add35
885.5076885899.00Volume120.5
996.0087995.75922.00SF432
10116.5098116.51123.00SF71.5
Sheet1
Cell Formulas
RangeFormula
K2:K10K2=ToadCalc+(0*TODAY())
 
Solution

nwfinance

Board Regular
Joined
Jan 22, 2016
Messages
67
Office Version
  1. 365
Platform
  1. Windows
@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
Joined
Feb 15, 2002
Messages
4,648
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

Edit the formulas as required

T202012a.xlsm
ABCDEFGHIJ
1Data 1Data 2Data 3Data 4Data 5Data 6Data 7Data 8SFAmount
222.511220.5255SF5
33322331.25388Add45.25
443.53344242Add60.5
55443552.7553Volume80
664.554663.564SF27
77565774.2575Add105.25
885.576885899Volume308
99687995.75922SF54
10116.598116.51123SF71.5
7c
Cell Formulas
RangeFormula
J2:J10J2=CHOOSE(MATCH(I2,{"Add","SF","Volume"},0),A2+B2+D2+E2+F2+G2,A2*B2,A2*B2*C2)
 

nwfinance

Board Regular
Joined
Jan 22, 2016
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Edit the formulas as required

T202012a.xlsm
ABCDEFGHIJ
1Data 1Data 2Data 3Data 4Data 5Data 6Data 7Data 8SFAmount
222.511220.5255SF5
33322331.25388Add45.25
443.53344242Add60.5
55443552.7553Volume80
664.554663.564SF27
77565774.2575Add105.25
885.576885899Volume308
99687995.75922SF54
10116.598116.51123SF71.5
7c
Cell Formulas
RangeFormula
J2:J10J2=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

Well-known Member
Joined
Feb 15, 2002
Messages
4,648
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
With Named formulas
T202012a.xlsm
ABCDEFGHIJK
1Data 1Data 2Data 3Data 4Data 5Data 6Data 7Data 8SFAmountAmount
222.511220.5255SF55
33322331.25388Add45.2545.25
443.53344242Add60.560.5
55443552.7553Volume8080
664.554663.564SF2727
77565774.2575Add105.25105.25
885.576885899Volume308308
99687995.75922SF5454
10106.598116.51123SF6565
7c
Cell Formulas
RangeFormula
J2:J10J2=CHOOSE(MATCH(I2,{"Add","SF","Volume"},0),A2+B2+D2+E2+F2+G2,A2*B2,A2*B2*C2)
K2:K10K2=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.
 
Last edited:

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,648
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
J3J3=CHOOSE(MATCH($I3,Calc,0),Add,SF,Volume)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,277
Messages
5,635,249
Members
416,849
Latest member
truerock

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top