Requesting formula to copy from one tab to another

Occasional Contributor

I'm looking for a formula that will copy the contents of a row on one tab into another tab. I want this to happen when something is entered into a column on the same row. Let's say I have a list of tasks to be done on Tab 1). On Tab 2, I just want to list all the tasks completed. Tab 2 has a formula will pull the completed tasks title and description (entire row) from Tab 1 when an "x" is added.
 
The formula in Tab2 looks at each row in Tab1 and then selects the content in columns A, C, D, and K, when Column K is not empty (when it has an "x" in it). Then that row is copied into Tab2 where the formula is. If there is nothing in Tab1/column K then Tab2 shows "No records found."

 

Here is the one that was working in Google Sheets:

=IFERROR(Query('Tab1'!A2:K,"Select A,C,D where K <>'' ",1),"No records found")


 
If you know how to write this formula please share it here, thank you so much!

2 Replies

Hi@jenniferk007,

it doesn't work quite as elegantly in Excel as it does in Google Sheets with Query. But there is also a solution with FILTER() in a single cell. I have built an example file for you. Here you will find the formula in cell Tab2!A2.

If you make a table out of the data, as in my example, you can restrict the cell ranges even more easily. But here I would use three formulas. Each for the entry from column A, C and D (in E2, F2 and G2).

Restricting the rows makes sense. If you were to apply the filter over the whole table, it would be very detrimental to performance. In the one-cell solution, I worked with LET() and LOOKUP() to find the last entry in column K and thus restrict the table range.

Have a look at my example to see if you can do something with it. Good luck.

 

Thank you, I will give this a try.