Requesting formula to copy from one tab to another

[jenniferk007]

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]

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.

[#3]

Thank you, I will give this a try.

[#4]

@jenniferk007 

Using OneDrive, I was successful in getting the formula to work dynamically. However, there are still some outliers to be fixed. Namely, it appears that the header in columns 4 and 5 on Sheet 2 are being truncated. I don't understand the formula enough to see why. There is also an error in 2.3.e, where I assumed missing information from Tab1. But that row has all the information being requested in the formula.

https://1drv.ms/x/s!AkS7DYdbFniAfr4R2wFMdjMegEQ?e=Qqx352

[#5]

UPDATE: Also, what is supposed to happen is when an "x" is entered into Column G on Tab1, the cell background color should change to red. Then each row should be added to the second Tab1 Action Plan tab.

[#6]

Hi @jenniferk007 ,

We have already "chatted" with each other in your document. One problem is that Filter() can only return cells with a maximum of 255 characters. The truncation of the cell headers was a problem with the display and column width. I don't know if this is a specific Excel for Web problem. If you increase the column width, you can read your headings.

Otherwise, you have adapted the formula well to your table.

[#7]

I really appreciate your help!

[#8]

I must correct myself. It is not the FILTER that causes the problem with 255 characters, but the CHOICE function. If you make an "intelligent table" out of your table, you would no longer have the problem. If you want, I can create an example for you with your current values.

[#9]

Sure, that would be great. I don't know what an intelligent table is! :o)

[#10]

@jenniferk007 

Not sure I understood entire logic, as variant

with

=LET(
   selection, $A$2,
   headers, 'Tab1'!$A$1:$M$1,
   data, 'Tab1'!$A$2:INDEX('Tab1'!$M:$M, XMATCH(,'Tab1'!$A:$A) - 1 ),
   length, ROWS(data),

   criteriaHeader, 'Tab1'!$E$1:$I$1,
   criteria, 'Tab1'!$E$2:INDEX( 'Tab1'!$I:$I, length + 1 ),
   marks, XLOOKUP( selection, criteriaHeader, criteria ),
   IFERROR(
     SUBSTITUTE(
         FILTER( INDEX(data, SEQUENCE( length ), XMATCH($C$2:$F$2,'Tab1'!$A$1:$M$1) ),
                 marks = "X" ),
         "", "" ),
   "no marks" )
 )