Forum Discussion
Copy Data to Other Sheets' Columns Based on Criteria
Hi Thomas,
The easiest is with Power Query - query source data, filter and land result back. Sample attached.
SergeiBaklan Thank you Sergei - it does not appear that if I make a change to the original table, that the queries update? (note I have kept the queries in the same Workbook, on a separate Sheet).
Thomas, to show update click on ribbon Data->Refresh All. Or right click on resulting table and Refresh.
SergeiBaklan Thank you. That does work, but I was hoping for an automatic update. The people in my office will not know to click refresh (and in fact I'm keeping them out of the excel so they will only see what is referenced into our AutoCAD file from excel). But I can make do with this if there is no easy way to auto update.
Thomas, there is no autorefresh, but you may right click on query, open properties and here set refreshing on file open and/or every X minutes (when file is opened).
Alternatively you may use formula like
=IFERROR(INDEX(Table1[Item],AGGREGATE(15,6,1/(Table1[Lower Flat Plan]="X")*(ROW(Table1[Item])-ROW(Table1[[#Headers],[Item]])),ROW()-ROW($G$1))),"")
Formula is in G2 in attached file
drag it down till max of your range. Empty cells appear if no match.
Formula uses structured references. If your source is the range, not table, it'll be similar.
With this it'll be updated as you add/remove "X".