Fetching data from one sheet to other on condition

BCM7

New Member
Joined
Jul 13, 2020
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
Welcome to the forum, try something like

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

BCM7

New Member
Joined
Jul 13, 2020
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
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:
StockLTPChgChg%%gain or lossPurchase Date
ABC3314.15(15.85)(0.8%)62.52%22/05/2020
XYZ800.0029.002.34%15.08%22/05/2020
RTV1000.000.00%5%22/05/2020
JKL700(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% hereI 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%.
 

Attachments

  • Screenshot1.jpg
    Screenshot1.jpg
    168.6 KB · Views: 3
  • Screenshot2.jpg
    Screenshot2.jpg
    123.1 KB · Views: 3

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
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)

1594651915723.png
 

BCM7

New Member
Joined
Jul 13, 2020
Messages
6
Office Version
  1. 365
Platform
  1. MacOS
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

Well-known Member
Joined
Apr 29, 2003
Messages
6,468
Platform
  1. MacOS
You're welcome, thanks for the feedback. You too, stay safe.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top