How to make a cell dependant on another, and perform different calcs based on the orginal cell?

%3CLINGO-SUB%20id%3D%22lingo-sub-1382769%22%20slang%3D%22en-US%22%3EHow%20to%20make%20a%20cell%20dependant%20on%20another%2C%20and%20perform%20different%20calcs%20based%20on%20the%20orginal%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1382769%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20size%3D%224%22%3EHi%2C%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EMy%20excel%20skills%20are%20average%20at%20best%20and%20I%20am%20trying%20to%20track%20my%20trades%20better%20and%20have%20tried%20to%20create%20a%20sort%20of%20ledger%2C%20however%20I%20have%20hit%20a%20brick%20wall.%20%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3EI%20am%20trying%20to%20make%20the%20spreadsheet%20as%20simple%20as%20possible%2C%20and%20have%20it%20be%20as%20automonous%20as%20possible.%20%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3EMy%20question%20is%20the%20following%2C%20is%20it%20possible%20to%20have%20the%20multiple%20cells%20within%20the%20same%20row%2C%20dependant%20on%20one%20single%20cell%2C%20and%20if%20so%2C%20how%3F%20The%20best%20way%20for%20me%20to%20explain%20is%20to%20use%20an%20example%3A%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EIf%20cell%20A1%20says%20'LSE'%20then%20the%20following%20formulas%20will%20apply%20in%20the%20following%20cells%20within%20the%20row%3A%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20P1%20%3D(L1*T1)*M1%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20Q1%20%3DP1%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20W1%20%3D((T1-U1)*L1)%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20X1%20%3DW1%2FSummary!F5%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3Eand%20so%20on..%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EIf%20cell%20A1%20says%20'NYSE'%20then%20the%20following%20formulas%20will%20apply%20in%20the%20following%20cells%20within%20the%20row%3A%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20P1%20%3D(L1*T1)*M1%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20Q1%20%3DP1*0.81%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20W1%20%3D((T1-U1)*L1)*0.81%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20X1%20%3DW1%2FSummary!F5%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3Eand%20so%20on..%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EIf%20cell%20A1%20says%20'EPA'%20then%20the%20following%20formulas%20will%20apply%20in%20the%20following%20cells%20within%20the%20row%3A%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20P1%20%3D(L1*T1)*M1%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20Q1%20%3DP1*0.88%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20W1%20%3D((T1-U1)*L1)*0.88%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20X1%20%3DW1%2FSummary!F5%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3Eand%20so%20on..%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EOn%20my%20spreadsheet%20there%20are%20a%20total%20of%2012%20cells%20within%20the%20row%20that%20I%20want%20linked%20to%20the%20initial%20cell%20(LSE%2CNYSE%20or%20EPA)%20and%20automatically%20update.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EAlso%2C%20I%20am%20trying%20to%20make%20it%20so%20it%20works%20on%20any%20row%2C%20so%20that%20if%20I%20click%20on%20Cell%20A4%2C%20and%20select%20NYSE%2C%20the%20cells%20within%20row%204%20display%20the%20formulas%3A%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20P4%20%3D(L4*T4)*M4%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20Q4%20%3DP4*0.81%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20W4%20%3D((T4-U4)*L4)*0.81%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20X4%20%3DW4%2FSummary!F5%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3Eand%20so%20on..%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EOr%20if%20I%20click%20cell%20A9%20and%20select%20EPA%2C%20the%20cells%20within%20row%209%20and%20display%20the%20following%20formulas%3A%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20P9%20%3D(L9*T9)*M9%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20Q9%20%3DP9*0.88%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20W9%20%3D((T9-U9)*L9)*0.81%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3ECell%20X9%20%3DW9%2FSummary!F5%3C%2FFONT%3E%3CBR%20%2F%3E%3CFONT%20size%3D%224%22%3Eand%20so%20on..%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EI%20have%20tried%20the%20following%20formula%3A%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3E%3DIF(F12%3D%22GBP%22%2CQ12)%3DIF(F12%3D%22USD%22%2CQ12*0.8)%3DIF(F12%3D%22EUR%22%2CQ12*0.88)%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EHowever%2C%20all%20that%20I%20get%20back%20is%20'TRUE'.%20I%20want%20the%20calculation%20not%20a%20confirmation%20that%20the%20formula%20is%20correct%20%3CLI-EMOJI%20id%3D%22lia_confused-face%22%20title%3D%22%3Aconfused_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EI%20hope%20I%20have%20explained%20this%20well%2C%20and%20of%20course%2C%20thank%20you%20in%20advance%20for%20any%20help%20it%20is%20much%20appreciated.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3ECheers%2C%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20size%3D%224%22%3EC%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1382769%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1388938%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20make%20a%20cell%20dependant%20on%20another%2C%20and%20perform%20different%20calcs%20based%20on%20the%20orginal%20cell%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1388938%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F664804%22%20target%3D%22_blank%22%3E%40CobyCurtis%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20adjust%20the%20formula%20to%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFS(F12%3D%22GBP%22%2CQ12%2CF12%3D%22USD%22%2CQ12*0.8%2CF12%3D%22EUR%22%2CQ12*0.88)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20this%2C%20you%20will%20get%20the%20intended%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%2C%20I%20believe%20you%20can%20set%20up%20your%20data%20in%20a%20better%20way%2C%20so%20you%20won't%20hardcode%20the%20numbers%20in%20your%20formula%2C%20for%20example%2C%20have%20a%20table%20for%20the%20values%20of%20each%20GBP%2C%20USD%20and%20EUR.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22wumolad_1-1589447601099.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F191604i555053602114EF05%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22wumolad_1-1589447601099.png%22%20alt%3D%22wumolad_1-1589447601099.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20then%20use%20INDEX%20and%20MATCH%20or%20VLOOK%20(XLOOK%20is%20also%20useful)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DINDEX(%24A%241%3A%24B%243%2CMATCH(%24F%2412%2C%24A%241%3A%24A%243%2C0)%2C2)*%24Q%2412%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

Hi,

My excel skills are average at best and I am trying to track my trades better so I have tried to create a sort of ledger; however I have hit a brick wall.
I am trying to make the spreadsheet that is as simple as possible and have it be as autonomous as possible.
My question is the following; is it possible to have the multiple cells within the same row, dependent on one single cell, and if so, how? The best way for me to explain is to use an example:

If cell A1 says 'LSE' then the following formulas will apply in the following cells within the row:
Cell P1 =(L1*T1)*M1
Cell Q1 =P1
Cell W1 =((T1-U1)*L1)
Cell X1 =W1/Summary!F5
and so on..

If cell A1 says 'NYSE' then the following formulas will apply in the following cells within the row:
Cell P1 =(L1*T1)*M1
Cell Q1 =P1*0.81
Cell W1 =((T1-U1)*L1)*0.81
Cell X1 =W1/Summary!F5
and so on..

If cell A1 says 'EPA' then the following formulas will apply in the following cells within the row:
Cell P1 =(L1*T1)*M1
Cell Q1 =P1*0.88
Cell W1 =((T1-U1)*L1)*0.88
Cell X1 =W1/Summary!F5
and so on..

 

On my spreadsheet there are a total of 12 cells within the row that I want linked to the initial cell (LSE,NYSE or EPA) and automatically update.

Also, I am trying to make it so it works on any row, so that if I click on Cell A4, and select NYSE, the cells within row 4 display the formulas:
Cell P4 =(L4*T4)*M4
Cell Q4 =P4*0.81
Cell W4 =((T4-U4)*L4)*0.81
Cell X4 =W4/Summary!F5
and so on..

Or if I click cell A9 and select EPA, the cells within row 9 and display the following formulas:
Cell P9 =(L9*T9)*M9
Cell Q9 =P9*0.88
Cell W9 =((T9-U9)*L9)*0.81
Cell X9 =W9/Summary!F5
and so on..

 

I have tried the following formula:

=IF(F12="GBP",Q12)=IF(F12="USD",Q12*0.8)=IF(F12="EUR",Q12*0.88)

However, all that I get back is 'TRUE'. I want the calculation not a confirmation that the formula is correct

 

I hope I have explained this well, and of course, thank you in advance for any help it is much appreciated.

 

Cheers,

C

1 Reply
Highlighted

Hi @CobyCurtis 

 

You can adjust the formula to 

 

 

=IFS(F12="GBP",Q12,F12="USD",Q12*0.8,F12="EUR",Q12*0.88)

 

 

With this, you will get the intended value.

 

However, I believe you can set up your data in a better way, so you won't hardcode the numbers in your formula, for example, have a table for the values of each GBP, USD and EUR.

 

wumolad_1-1589447601099.png

 

You can then use INDEX and MATCH or VLOOK (XLOOK is also useful)

 

 

=INDEX($A$1:$B$3,MATCH($F$12,$A$1:$A$3,0),2)*$Q$12

 

 

Cheers,