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? 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. Commented Jul 25, 2017 at 18:16

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! Commented Jul 26, 2017 at 17:44