Skip rows in source sheet when dragging down formula in Google Sheets
I am trying to drag down a formula that is pulling data from one sheet into another. I need to skip rows on the sheet that it is pulling from.
Example:
='Heat 1'!AG2
='Heat 1'!AG6
='Heat 1'!AG10
etc.
I did some research and found a few formulas that I thought might work, but they were looking to have the formula skip rows on the sheet that is pulling, not that is getting pulled from.
Solution
Here's one approach you may test out which allows you to get the whole output table in one go:
=let(Λ,reduce(tocol(,1),tocol(G:G,1),lambda(a,c,vstack(a,map(indirect(c&"!A2:A"),lambda(Σ,if(Σ="",,{Σ,torow(offset(Σ,,column(AG1)-1,4))})))))),
filter(Λ,index(Λ,,1)))
- the tab names needs to be listed in
Column_G(as in screenshot). You could hardcode them within the formula too if thats how you need it...
- Allow script to edit locked cells
- How to join unique strings in their own column with QUERY?
- How do I use XLOOKUP for multiple criteria?
- How to use AppendRow
- How to code "Next Friday" in Google Sheets?
- How can I resolve this `Error: You must select all cells in a merged range to merge or unmerge them.` while merging cells through App Script?
- Casting method asTextItem throws error for Google form item
- Counting blank cells by arrayformula
- ARRAYFORMULA with GOOGLEFINANCE function
- Conditional formatting depending on two cells
- How do I find the highest sum of multiple cells in column A where the sum is based on multiple cells matching in column B in Google Sheets?
- Returning the last input entry
- Draw and filter Charts in App Script Web app
- Google Sheets - select "most recent " cell in a column and return corresponding value in same row
- Byrow formula with reference to last row and last column
- How Do I Give rows in a table of contents a key. Then look up the key Later using the data from the rows in table of contents?
- Duplicate whole spreadsheet using Google Sheets API
- Array Formula to omit certain values
- Google Sheets Query While Removing Duplicates
- NAME error hiperlink function Apps Script Google Sheets
- Three-dimensional referencing
- Can I use a named range as column headers?
- Trying to get Google App Script Code to Acknowledge Cells In A Column in Excel to do Individual Math
- How to get the price of a stock at a particular date from Google Finance?
- How to Split a String Up into Individual Characters
- How to integrating Google Sign-In into your google script web app
- Formula is currently double-counting a cell
- Get dropdown options in google sheets in table
- Search for targets and indicate where they come from
- One-to-one assignment in Google sheets with data validation