Autopopulate Cell on another sheet, based on value of cell in another

surkdidat

Well-known Member
Joined
Oct 1, 2011
Messages
597
Office Version
  1. 365
Is there anyway I can populate the data from Sheet 1 into Sheet2 based on the value of a cell in the row on Sheet1

For instance Sheet1, Column D, will have Yes or No in. Column A will have a unique reference number.

If I have

--A-- |--B--
12345| YES
23456|YES
34567|NO
45678|NO
56789|YES

In Sheet2, I want

--A--|
12345
23456
56789

(So it is not taking over the rows with "no" in.)

EDIT : It would be helpful if this is automated, and does not rely on the user having to click on a button or something similar, thanks :) )
 
Last edited:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
in Sheet2!A1
=IFERROR(INDEX(Sheet1!$A$1:$A$5,AGGREGATE(15,6,ROW($A$1:$A$5)/((Sheet1!$B$1:$B$5="YES")),ROWS(A$1:A1))-(1-1),1),"")
and copy down for as many rows as you have on Sheet1 Column A
 
Upvote 0
Thankyou for this - however, one problem I have is that in the above rows 1, 2 and 5 are Yes, and 3 and 4 are No. If row 3 or 4 changes to YES it puts it (logically) in order. However, where on Sheet2 I will have comments/dropdowns etc against the references (that are populating) then it throws everything out. Is there anyway I can get around this?
in Sheet2!A1
=IFERROR(INDEX(Sheet1!$A$1:$A$5,AGGREGATE(15,6,ROW($A$1:$A$5)/((Sheet1!$B$1:$B$5="YES")),ROWS(A$1:A1))-(1-1),1),"")
and copy down for as many rows as you have on Sheet1 Column A
 
Upvote 0
"where on Sheet2 I will have comments/dropdowns etc against the references"

You need to explain where you actually have the comments/dropdowns

Can you supply some example data?
 
Upvote 0
OK, will do my best!!

So, if in sheet 1 I mark it as "YES" it copies the reference into Sheet2, no problem.

In Sheet 2 I have comments I input based on the reference.

So, for example Sheet1 has references 1,2,3,4 and 5.

At the moment, References 1 2 and 5 state YES in Sheet1

In sheet2, I have comments such as a date, and that if it is shipped, a day it has been delivered etc.

In the meantime, Reference 3 is changed to YES

In sheet2, this now has references 1 2 3 and 5.

However, the manual comments input do not move.

So, if I had a date for reference 5 (08/04/19 in one cell, and SHIPPED in the next one along), when reference 4 is added, the spreadsheet leaves these comments in that line, so it looks like they relate to reference 4 rather than reference 5



"where on Sheet2 I will have comments/dropdowns etc against the references"

You need to explain where you actually have the comments/dropdowns

Can you supply some example data?
 
Upvote 0
impossible unless the comments are on sheet 1 in which case copy the formula and change the index

=IFERROR(INDEX(Sheet1!$A$1:$A$5...

to the comments column
 
Upvote 0

Forum statistics

Threads
1,224,891
Messages
6,181,614
Members
453,057
Latest member
LE102024

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
Back
Top