How to use to Iferror and Vlookup makes blank cell to pure true blank

[pankajgrover]

Here is example, how to make D16, D17 show TRUE instead of false. I want here true blank cells..

Shares Trading - Copy.xlsx
	A		C	D
14	Infy		300	FALSE
15	Natural Gas		500	FALSE
16				FALSE
17				FALSE
1

Cell Formulas
Range	Formula
C14:C17	C14	=IFERROR(VLOOKUP(A14,Prices!$A$2:$F$25,3,0),"")
D14:D17	D14	=ISBLANK([@Brok])

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

Cells with Data Validation
Cell	Allow	Criteria
A14:A17	List	=Shares

 

[Peter_SSs]

Is this what you mean for 17?

=IF(AH16="","",IF(AH16="S",-I16+AL16,-AL16+P16-O16))

Try this for 18

23 01 14.xlsm
	G						M																					AH
9							1																					S
10							0.5																					S
11	1																											B
12	1																											B
13
14	1						2																					D
Sheet7

Cell Formulas
Range	Formula
AH9:AH14	AH9	=IF(G9&M9="","",IF(G9="","S",IF(M9="","B","D")))

I am not understanding

 

[pankajgrover]

Is this what you mean for 17?

=IF(AH16="","",IF(AH16="S",-I16+AL16,-AL16+P16-O16))

Try this for 18

23 01 14.xlsm
	G						M																					AH
9							1																					S
10							0.5																					S
11	1																											B
12	1																											B
13
14	1						2																					D
Sheet7

Cell Formulas
Range	Formula
AH9:AH14	AH9	=IF(G9&M9="","",IF(G9="","S",IF(M9="","B","D")))

I am not understanding

Hi #18 post solved thanks and for #17 post here below example in 16th row i forget AH16 to Type "B". what i did i put data AI16,AJ16, AK16 before and after i Type "B" in Ah 16 but AM16 results comes wrong , he actually picking "S" calculation instead of "B" due to i type "B" later. It should pickup -4188 in Am16.. how can i solve this issue Thanks.

Shares Trading1.xlsx
	AG	AH	AI	AJ	AK		AM
11	292.40	B	27/12/2022	1	317.30		-31125
12	449.60	B	29/12/2022	1	483.20		-42000
13	292.40	B	29/12/2022	0.5	317.30		-15563
14	1508.90	B	03/01/2023	1	1455.55		21340
15	292.40	B	10/01/2023	0.5	299.10		-4188
16	292.40	B	10/01/2023	0.5	299.10		182750
1

Cell Formulas
Range	Formula
AG11:AG16	AG11	=D11
AM11:AM16	AM11	=IF(AK11="","",(G11-M11)*B11*D11+IF(AH11="S",-I11+AL11,-AL11+P11-O11))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AH3:AH109	Cell Value	="B"	text	NO
AH3:AH109	Cell Value	="S"	text	NO

 

[Peter_SSs]

Hi #18 post solved thanks

Great.

and for #17 post here below ..

I'm confused. What you had below is a different scenario & different formula to what you were describing in post #17

 

[pankajgrover]

Great.


I'm confused. What you had below is a different scenario & different formula to what you were describing in post #17

You just considered #22 post and forget #17 post. Thanks

 

[Peter_SSs]

just considered #22

Is this what you mean then?

=IF(OR(AH11="",AK11=""),"",(G11-M11)*B11*D11+IF(AH11="S",-I11+AL11,-AL11+P11-O11))

 

[pankajgrover]

Is this what you mean then?

=IF(OR(AH11="",AK11=""),"",(G11-M11)*B11*D11+IF(AH11="S",-I11+AL11,-AL11+P11-O11))

Hi if and or does not make any difference, what is happening in #22 example, i just discovered if i copy paste to AK16 cell price 299.1 it gives wrong calculation, and if i type manually 299.1 then calculation working good. Actually in AM column i hv put condition that check B or Check S and formula applied accordingly

 

[pankajgrover]

Hi if and or does not make any difference, what is happening in #22 example, i just discovered if i copy paste to AK16 cell price 299.1 it gives wrong calculation, and if i type manually 299.1 then calculation working good. Actually in AM column i hv put condition that check B or Check S and formula applied accordingly

@Peter_SSs i sorted out issue, there was problem in AL16 column was blank, due to i insert row there. Now i rectified. Wn i insert row, previous colmn cell formula does not carry forward to new insert row, i don't know why ? . Well Thanks for help .​

 

[Peter_SSs]

Glad you got it sorted.

 

[pankajgrover]

hi @Peter_SSs or @Fluff here is example why S14, S15 not showing blank. kindly check my formula

Shares Trading.xlsx
	H						N				R	S
8							64825.00				-69030	Active
13	299.10										-4188	Active
14												Active
15												Active
1

Cell Formulas
Range	Formula
R8,R13:R15	R8	=Q8*IF(M8="",G8,M8)*B8-O8
S8,S13:S15	S8	=IF(R8="","",IF(OR(H8="",N8=""),"Active","Booked"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S3:S108	Cell Value	="ACTIVE"	text	NO

 

[pankajgrover]

hi @Peter_SSs or @Fluff here is example why S14, S15 not showing blank. kindly check my formula

Shares Trading.xlsx
	H						N				R	S
8							64825.00				-69030	Active
13	299.10										-4188	Active
14												Active
15												Active
1

Cell Formulas
Range	Formula
R8,R13:R15	R8	=Q8*IF(M8="",G8,M8)*B8-O8
S8,S13:S15	S8	=IF(R8="","",IF(OR(H8="",N8=""),"Active","Booked"))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S3:S108	Cell Value	="ACTIVE"	text	NO

I myself solved it after some little practicing like below:
=IF(H8&N8="","",IF(OR(H8="",N8=""),"Active","Booked"))