When a new row is added to TABLE1 in SPREADSHEET1, I want select data from that row to be pulled and automatically added to the information to TABLE2, but only IF it follows certain criteria.

Any idea on how to do this? I know how to define the criteria, but I do not know how to set up the rest of the formula. using excel 2016

  • Hello and welcome to SuperUser. Can you please be more specific?
    – wizlog
    Commented Jul 25, 2017 at 18:03
  • The best tool for this is what used to be called "power query," now "get and transform." (Look in data menu.) You'll be able to define criteria for what should be added to data mode. There's something of a learning curve, though. Another approach might be to use filter on table 1 to automatically create a new table 2. I don't think you're going to find a way to have data tables automatically add rows, though maybe someone else here will prove me wrong.
    – Jonathan
    Commented Jul 25, 2017 at 18:16

1 Answer 1


Just use the reference in an IF statement, or whatever your criteria is.

For example, I have Sheet 1, and inside this sheet I have Column A with the following data, 1,2,3,4,5,6,7,8,9,10 (A1 = 1, A2 = 2, etc.)

Now in Sheet 2 I want column A to equal the Sheet 1 Column A if that value is even. So I'd want A1 to be empty, but A2 to equal 2, and so on.

So in A1 (on Sheet 2), I'll enter the formula:

=IF(MOD(Sheet1!$A1,2) = 0,Sheet1!$A1,"")

Then put your cursor at the bottom right hand corner (until you see the the little black +), and click and drag down until however many rows, say 500.

You will see that A1 is blank, A2 is 2, A3 is blank, A4 is 4, and so on up until A10 is 10. Now go back to Sheet 1 and continue filling in the sequence up to 20. Go back to Sheet 2 and you'll notice that it auto-updated as you entered in Sheet 1.

Is this what you were looking for? If not, can you be more specific in your description so we can better help you out.

  • It seems all your answers are very comprehensive, thank you for joining Superuser!
    – wizlog
    Commented Jul 26, 2017 at 17:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .