Aug 04 2022 07:11 AM
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!
Aug 06 2022 08:45 AM
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.
Aug 09 2022 05:36 AM
Aug 15 2022 09:24 AM
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.
Aug 15 2022 10:21 AM
Aug 15 2022 11:01 AM
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.
Aug 15 2022 11:40 AM
Aug 15 2022 12:05 PM
Aug 15 2022 12:13 PM
Aug 15 2022 02:23 PM - edited Aug 15 2022 02:25 PM
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" )
)