# How to pick and assign formula on behalf of cell color



## pankajgrover (Dec 25, 2022)

Hi, i am doing  copy paste formula according to buy sell option. But i want if cell have green color this Formula =(((F22-L22)*B22)*C22)-AI22+O22-N22 apply or if have red color this Formula =(((F21-L21)*B21)*C21)-H21+AI21 apply in same Net result column. Still now i doing manually and How i do that  automatic? I want in below example AI column auto pick formula from cell AG16, AG17 color based .Should i do any conditional formatting ? Thanks

Shares Trading (1).xlsxACADAEAFAGAI15Net result16Oil6621.0023/12/20220.56500.00-605017Gas430.0023/12/20221410.0025500sheetCell FormulasRangeFormulaAI16AI16=(((F16-L16)*B16)*C16)-H16+AH16AI17AI17=(((F17-L17)*B17)*C17)-AH17+O17-N17


----------



## HongRu (Dec 25, 2022)

maybe this helps.
Info only - get.cell arguments


----------



## pankajgrover (Dec 25, 2022)

HongRu said:


> maybe this helps.
> Info only - get.cell arguments


I did not understand this article very complex. Any easy way to do that ?


----------



## Peter_SSs (Dec 25, 2022)

*How* are the cells getting coloured green or red?
- Conditional Formatting?
- vba?
- Manual?
- Something else?


----------



## pankajgrover (Dec 25, 2022)

Peter_SSs said:


> *How* are the cells getting coloured green or red?
> - Conditional Formatting?
> - vba?
> - Manual?
> - Something else?


I will do it manual cell color that's no issue. but i want result formula pick from color. Another idea was in my mind that i insert 1 more column and give value S or B sale or Buy , and that defines result formula. But i happy with manual color because its easy for me understand that values green and red means buy and sale data without the need of 1 extra column. Thanks


----------



## Peter_SSs (Dec 25, 2022)

To me it would make more sense to add that extra column with B or S. That will make the formula choice easy and you can also set up Conditional Formatting to automatically make the row green or red depending on that B or S value in the extra column.


----------



## pankajgrover (Dec 25, 2022)

Peter_SSs said:


> To me it would make more sense to add that extra column with B or S. That will make the formula choice easy and you can also set up Conditional Formatting to automatically make the row green or red depending on that B or S value in the extra column.


If i go with extra column how to do that result column pick formula on based of B or S ? if can i also go with color based option formula pick up pls also share how to do that ? I just want formula in auto mode in only *AI column* in above example . Thanks


----------



## Peter_SSs (Dec 25, 2022)

Try something like this. Ignore the actual results in column AI as I did not have your values for column B, C, F etc I just made up some numbers to check that my formulas gave the same results as yours.

22 12 25.xlsmACADAEAFAGAIAJ15Net result16Oil6621449180.56500-27S17Gas43044918141041BBuy SellCell FormulasRangeFormulaAI16:AI17AI16=(F16-L16)*B16*C16+IF(AJ16="S",-H16+AH16,-AH16+O16-N16)Cells with Conditional FormattingCellConditionCell FormatStop If TrueAE16:AJ17Expression=$AJ16="B"textNOAE16:AJ17Expression=$AJ16="S"textNO


----------



## pankajgrover (Dec 25, 2022)

Peter_SSs said:


> Try something like this. Ignore the actual results in column AI as I did not have your values for column B, C, F etc I just made up some numbers to check that my formulas gave the same results as yours.
> 
> 22 12 25.xlsmACADAEAFAGAIAJ15Net result16Oil6621449180.56500-27S17Gas43044918141041BBuy SellCell FormulasRangeFormulaAI16:AI17AI16=(F16-L16)*B16*C16+IF(AJ16="S",-H16+AH16,-AH16+O16-N16)Cells with Conditional FormattingCellConditionCell FormatStop If TrueAE16:AJ17Expression=$AJ16="B"textNOAE16:AJ17Expression=$AJ16="S"textNO


This is working . Thanks. If i opt for cell color instead of creating new column B or S. I just asking for knowledge purpose , Like below what will be code for replace Bold words
=(F16-L16)*B16*C16+*IF(AG16="Cell color is red"*,-H16+AH16,-AH16+O16-N16)


----------



## Peter_SSs (Dec 25, 2022)

You would need to use vba not standard worksheet functions.


----------



## pankajgrover (Dec 25, 2022)

Hi, i am doing  copy paste formula according to buy sell option. But i want if cell have green color this Formula =(((F22-L22)*B22)*C22)-AI22+O22-N22 apply or if have red color this Formula =(((F21-L21)*B21)*C21)-H21+AI21 apply in same Net result column. Still now i doing manually and How i do that  automatic? I want in below example AI column auto pick formula from cell AG16, AG17 color based .Should i do any conditional formatting ? Thanks

Shares Trading (1).xlsxACADAEAFAGAI15Net result16Oil6621.0023/12/20220.56500.00-605017Gas430.0023/12/20221410.0025500sheetCell FormulasRangeFormulaAI16AI16=(((F16-L16)*B16)*C16)-H16+AH16AI17AI17=(((F17-L17)*B17)*C17)-AH17+O17-N17


----------



## pankajgrover (Dec 25, 2022)

Peter_SSs said:


> You would need to use vba not standard worksheet functions.


I hv little knowledge about VBA so it will be better go for another Insert column B or S for solving my purpose. Thanks and Marry Xmas .


----------



## Peter_SSs (Dec 25, 2022)

pankajgrover said:


> .. go for another Insert column B or S for solving my purpose.


I think that is what I would be doing.



pankajgrover said:


> Thanks and Marry Xmas .


You're welcome. .. and thanks.


----------



## pankajgrover (Dec 25, 2022)

Peter_SSs said:


> I think that is what I would be doing.
> 
> 
> You're welcome. .. and thanks.


Dear sir, i am doing 1 more some complex calculation . In below example *AK3* i want actual result *15770*. Actually i am trying to do use if formula but i am unable to do. In example my formula works if G4 or M6 have no values,  but issue arise when G3 or M3 have some both values  . So how to make formula to solve this  in single AK Result Column, may be if or function hv to used ? here is example :

Shares Trading (1)33.xlsxABCFGHLMNOAHAIAJAKAL2NameUnitsLPQtyPriceAmountS.QtyS.PriceBrok.S.AmountQtyPriceAmountRESULTRESULT SHOULD BE in AK Col.3Silver30.0069033.00169191.002075730169750.001000.002091500169750.002091500-100015770 FOR (((F3-L3)*B3)*C3)-H3+AJ34Silver30.0069033.00 164972.501949175168000.002040000-30990-309905Silver30.0069033.00 0.564845.009726750.568000.001020000-15495-154956Natural Gas1250.00430.401433.00541250 1410.005125002550025500kkCell FormulasRangeFormulaB3:B6B3=IFERROR(VLOOKUP(A3,Prices!$A$2:$C$25,2,0),0)C3:C6C3=IFERROR(VLOOKUP(A3,Prices!$A$2:$C$25,3,0),0)H3:H6H3=((F3*B3)*G3)O3:O6O3=((L3*B3)*M3)-(N3)AJ3:AJ6AJ3=((AH3*B3)*AI3)-(N3)AK3:AK6AK3=(F3-L3)*B3*C3+IF(G3="",-H3+AJ3,-AJ3+O3-N3)Named RangesNameRefers ToCellsShares=OFFSET(Prices!$A$2,0,0,COUNTA(Prices!$A$2:$A$25))B3:C6Cells with Data ValidationCellAllowCriteriaA3:A6List=Shares


----------



## Peter_SSs (Dec 26, 2022)

Is this directly related to the previous question? Doesn't seem to be anything to do with B/S or red/green?
Unless you can explain this is closely related to the previous question I suggest that you start a new thread for the new question.


----------



## pankajgrover (Dec 26, 2022)

Peter_SSs said:


> Is this directly related to the previous question? Doesn't seem to be anything to do with B/S or red/green?
> Unless you can explain this is closely related to the previous question I suggest that you start a new thread for the new question.


Ok i will post this to new thread. Thanks


----------

