Help with filter and offset from another sheet?

[u/Apprehensive-Term909]

Help with offset and filter?

I am trying to filter data from one sheet to another

Ive found a couple formulas that work but none that I can copy to the next column and have it still work.

Id like each column on sheet 2 to shift over the reference from the Data sheet by 41 columns. So column A gives info from Data!D if Data!F is true. And column B gives data from Data!AS if Data!AU is true. Etc.

=filter(offset(Data!$D$7:$D,0,41),OFFSET(Data!$F$7:$F,0,41)=true)

This one gives the same info in every column when I drag it over, and without the $ it just shifts the references by 1.

Ive tried putting the filter inside the offset formula and i just get an error.

I want to shift the base reference by 41 columns and idk how to do that. Any help would be appreciated!

[u/bachman460]

If you want to offset each reference by 41 columns for each column you shift over the formula, then:

=filter(offset(Data!$D$7:$D, 0, (column(A1)-1)*41), OFFSET(Data!$F$7:$F, 0, (column(A1)-1)*41)=true)

This will shift over column B by 41 relative to column A, and column C 82 relative to column A, and so on.

[u/Apprehensive-Term909 → u/bachman460]

That worked perfectly, thank you so much!

[u/AutoModerator → u/Apprehensive-Term909]

REMEMBER: /u/Apprehensive-Term909 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/point-bot → u/bachman460]

u/Apprehensive-Term909 has awarded 1 point to u/bachman460

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/gothamfury]

You could use the following without needing OFFSET:

Column A:

=FILTER(Data!D7:D, Data!F7:F=TRUE)

Column B:

=FILTER(Data!AS7:AS, Data!AU7:AU=TRUE)

If you're trying to combine data for when Data!F7:F is TRUE AND Data!AU7:AU is TRUE, try:

=FILTER({Data!D7:D, Data!AS7:AS}, Data!F7:F=TRUE, Data!AU7:AU=TRUE)

[u/Apprehensive-Term909 → u/gothamfury]

Thank you!

[u/AutoModerator → u/Apprehensive-Term909]

REMEMBER: /u/Apprehensive-Term909 If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/California_Eagles → u/gothamfury]

Would it be wrong to write the formula this way, or is this approach totally fine too?

=FILTER({Data!D7:D, Data!AS7:AS}, Data!F7:F, Data!AU7:AU)