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...
- Google App Script - Json response parsing
- Using INDIRECT in Google Sheets to reference to reference a table's named column
- Run a function published in Webapp from a Google site
- Querying a Google Sheet API using GViz
- How do you resolve a "The parameters (number[]) don't match the method signature for SpreadsheetApp.Range.setValues" error
- How do i make 3 column to arrange in 1?
- Google Apps Script getBackgrounds for colour custom function
- Auto-change or Query function Google sheets
- What is the purpose of the "UPLUS()" function in Google Sheets?
- how to construct and write a formula to google sheets to perform a dynamic date calculation
- How to extract the whole match and not just the captured group?
- Create connected drop-downs in google sheets where value can be present only in one drop-down
- Custom sort order according to cell value
- Find and print last edit dates of preexisting rows in a sheet (Google Sheets)
- Google Apps Script creates sheets version of excel file
- How to fix a #VALUE error when trying to pull an entire row of information from multiple sheets
- If var isnumber, for script
- Excel spill range reference in Google sheets
- Google Sheets Script new Date() with timezone is providing wrong time
- Trying to Email Multiple PDF Blobs in Sheets Javascript
- HTTP Google Sheets API v4 how to access without OAuth 2.0?
- List All Workdays between Start and End Dates Displaying Associated Name and Details in Other Columns
- How do I edit the response of a form submission in Google Forms/Sheets?
- How can I count repeats in a a column but reset count on non-repeats, in an array formula
- How do I stop Google AppScripts (Sheets) from creating two protected range entries
- Transfer Data from one sheet to another
- googlesheet get last non blank row from specific rows
- Send Mail after Form Submit to Google Sheet
- IFS with multiple conditions check in Google Sheet
- Can I find the value of a cell B49 that changes value according to the value of the cell F2 ( in Google sheet)