# Fetching data from one sheet to other on condition

#### BCM7

##### New Member
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%.

### 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
Welcome to the forum, try something like

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

#### BCM7

##### New Member
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%.

#### Attachments

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

#### gaz_chops

##### Well-known Member
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

##### New Member
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
You're welcome, thanks for the feedback. You too, stay safe.

Replies
3
Views
114
Replies
1
Views
128
Replies
2
Views
472
Replies
10
Views
236
Replies
6
Views
250

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.

### Which adblocker are you using?

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

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