How to pick and assign formula on behalf of cell color

[pankajgrover]

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).xlsx
	AC	AD	AE	AF	AG		AI
15							Net result
16	Oil	6621.00	23/12/2022	0.5	6500.00		-6050
17	Gas	430.00	23/12/2022	1	410.00		25500
sheet

Cell Formulas
Range	Formula
AI16	AI16	=(((F16-L16)*B16)*C16)-H16+AH16
AI17	AI17	=(((F17-L17)*B17)*C17)-AH17+O17-N17

 

[pankajgrover]

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]

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

I think that is what I would be doing.

Thanks and Marry Xmas .

You're welcome. .. and thanks.

 

[pankajgrover]

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.xlsx
	A	B	C			F	G	H				L	M	N	O																			AH	AI	AJ	AK	AL
2	Name	Units	LP			Qty	Price	Amount				S.Qty	S.Price	Brok.	S.Amount																			Qty	Price	Amount	RESULT	RESULT SHOULD BE in AK Col.
3	Silver	30.00	69033.00			1	69191.00	2075730				1	69750.00	1000.00	2091500																			1	69750.00	2091500	-1000	15770 FOR (((F3-L3)*B3)*C3)-H3+AJ3
4	Silver	30.00	69033.00									1	64972.50		1949175																			1	68000.00	2040000	-30990	-30990
5	Silver	30.00	69033.00									0.5	64845.00		972675																			0.5	68000.00	1020000	-15495	-15495
6	Natural Gas	1250.00	430.40			1	433.00	541250																										1	410.00	512500	25500	25500
kk

Cell Formulas
Range	Formula
B3:B6	B3	=IFERROR(VLOOKUP(A3,Prices!$A$2:$C$25,2,0),0)
C3:C6	C3	=IFERROR(VLOOKUP(A3,Prices!$A$2:$C$25,3,0),0)
H3:H6	H3	=((F3*B3)*G3)
O3:O6	O3	=((L3*B3)*M3)-(N3)
AJ3:AJ6	AJ3	=((AH3*B3)*AI3)-(N3)
AK3:AK6	AK3	=(F3-L3)*B3*C3+IF(G3="",-H3+AJ3,-AJ3+O3-N3)

Named Ranges
Name	Refers To	Cells
Shares	=OFFSET(Prices!$A$2,0,0,COUNTA(Prices!$A$2:$A$25))	B3:C6

Cells with Data Validation
Cell	Allow	Criteria
A3:A6	List	=Shares

 

[Peter_SSs]

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]

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