Forum Discussion
Requesting formula to copy from one tab to another
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!
Hijenniferk007,
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.
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.
