Fetching data from one sheet to other on condition

[BCM7]

Hi, I’m grateful if you could help me with some formulas on creating dashboard with what I’m looking for please. I’ve a data in worksheet1 as :
Stock. Gain or loss %. Col3. Col4.
ABC. 40%. 26d. 125
XYZ. 5%. 26d. 100
EFG. (11.4%). 26d. 90
And so on so forth.... Many rows.

How could I get data into second sheet from Sheet1 only when met the following conditions. I would like this data to be automatically populated in sheet2 every time when excel opens up or changes data in sheet1.

1. IF gain or loss >= 10%
2. IF gain or loss < 0%. (I.e. negative numbers)
3. IF again or loss > 0 and < 5%.

Thanks much for your help!

 

[gaz_chops]

Welcome to the forum, try something like

=IF(OR(A1<5%,A1>=10%,B1<5%,B1>=10%),A1,"")

 

[BCM7]

Thanks Gaz. Apparently, I need the data in 3 different steps. I've attached the EXCEL for your reference and for better understanding as what I'm looking for.

Portfolio Sheet has the raw_data. Dashboard sheet is where I want the data to be fetched to (from portfolio sheet). I've 3 tables in the dash board with 3 conditions each one being set on Portfolio->%gain or loss column.

Hope that's much clearer now. Once Agin, Thanks for your swift response and will be glad if you could further help in my request please.

Regards.

Portfolio Sheet has the data as follows:

Stock	LTP	Chg	Chg%	%gain or loss	Purchase Date
ABC	3314.15	(15.85)	(0.8%)	62.52%	22/05/2020
XYZ	800.00	29.00	2.34%	15.08%	22/05/2020
RTV	100	0.00	0.00%	5%	22/05/2020
JKL	700	(1.00)	(1.92%)	(3.03%)	22/05/2020

My Dash Board I'm trying to create is as follows:

Stock	% gain or Loss > 10 %
I need stock Name for %gain or loss > 10% here	I need %gain or Loss > 10% details for the same stock here.

in the same way, another table in the dash board with Condition : %gain or loss < 0% (that is negative % numbers).

Finally, another table in the dash board with Condition : %gain or loss >0 % and < 7%.

 

[gaz_chops]

You will need to change the references to suit your data

For the Stock
=IFERROR(INDEX($A$2:$A$10,MATCH(H11,$E$2:$E$10,0)),"")

For the %Gain/Loss
=IFERROR(AGGREGATE(14,6,($E$2:$E$10)/($E$2:$E$10>10%),ROW(B1)),0)

 

[BCM7]

Thanks Much, Gaz!! You’re a life saver. Truly appreciated your time and help with this. It’s working perfectly fine now. Cheers!!! Have a great week ahead!

 

[gaz_chops]

You're welcome, thanks for the feedback. You too, stay safe.