-1

I have two tabs in a Google Sheet, from which one is called Main and the other is called Temp.

The Main tab, should reflect what is marked as READY in column E of each Temp tab row.

Column E is a dropdown, made by a list of values.

I have tried to follow other discussions, and use other people examples, but none has worked. Here is my spreadsheet.

double-beep
  • 5,031
  • 17
  • 33
  • 41
Dylan Logan
  • 395
  • 7
  • 18
  • 1
    The file is not shared properly. Should ne set to anyone with the link at a minimum. Also, can you see the items where READY is YES by a reference or do you want an actual copy of the item added to the first empty row of the Main tab? – Karl_S Apr 18 '19 at 12:36
  • @Karl_S sorry about that, here is the new link: https://docs.google.com/spreadsheets/d/1wfNLxIuZVx3jzYIlJ9f4K1FEwR1fpKqPsmeiRQF4jxk/edit?usp=sharing And an actual copy of the item added to the first available row of main please. – Dylan Logan Apr 18 '19 at 12:37
  • sheet is private – player0 Apr 18 '19 at 15:10

1 Answers1

1

I created a new tab "Main by Reference" and in cell A1 added this:

=QUERY(Temp!A:E,"Select A, B, C, D where E = 'YES'", 1)

It reads the data in your Temp sheet and returns a list, in the same order entered in Temp, of all lines with the Ready column set to YES. Note that while you can add items to columns beyond those returned, any changes to Yes in the Temp tab will change the order of columns A-D in this new tab. By that I mean that if rows 3, 4, 8, and 9 are YES and all others are not, my tab will show these in rows 2, 3, 4, and 5 respectively. Changing Temp row 6 to YES will then return rows 3, 4, 6, 8, and 9 in that orderThis places the data in Main rows 2, 3, 4, 5, and 6 respectively, moving down the returned data from the last 2 rows. All this is to say that anything placed in Main row 5, cell E will now appear to apply to a different set of data. This is convoluted but if you play with the YES values you will see what happens.

Karl_S
  • 3,364
  • 2
  • 19
  • 33
  • Yeah I see what you mean, sadly this will cause problems down the line :/, do you have any ideas on how I'd get the rows to be added to the end as they get set as ready? You've gotta be able to do it with the scripting or something.. But your answer does work, and very well! – Dylan Logan Apr 18 '19 at 13:25
  • 1
    You would have to write a Google Apps Script for that. It could be done and might be a good place to try writing one. They are JavaScript based. I would suggest a search for Google apps script sheet copy row on edit. This one may be of some help getting you started: [Google script (copy row to other sheet onEdit)](https://stackoverflow.com/questions/38695652/google-script-copy-row-to-other-sheet-onedit). Then post specific questions. – Karl_S Apr 18 '19 at 13:32