Importing data from multiple sheets dynamically and sort by date

Question

https://docs.google.com/spreadsheets/d/1G9-lVE15VlOUhMM_OVOn-Xwi7fmJHz-FNJdVVnwBCYI/edit#gid=2100307022

I need to import a range of data from another spreadsheet which has many sheets, I have a cell which contain the references to the spreadsheet and a column that list the all the sheet where I need to retrieve the data from. I am able to retrieve the data from a single sheets or type the sheets into the formula manually, but I need it to be done dynamically.

Using

ARRAYFORMULA( IF(F2:F<>"", IMPORTRANGE(G2, F2:F&"!A3:Q"), "") )

I was able to do what I need but the data spread across multiple row which causes an overlapping error.

rockinfreakshow · Accepted Answer · 2024-01-16 09:27:45Z

1

You may try this base formula which you can wrap inside your existing query part:

=reduce(tocol(,1),tocol(F2:F,1),lambda(a,c,vstack(a,importrange(G2,c&"!A3:Q"))))

make sure importrange is properly linked by granting permission to access source sheet

edited Jan 16, 2024 at 9:27

answered Jan 16, 2024 at 9:22

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kent Ong Over a year ago

I only need the data from column 1,2,17,3,8 and sort them by column 2 which is a date in dd/mm/yyyy format

rockinfreakshow Over a year ago

=query(<above_reduce_formula>,"Select Col1,Col2,Col17,Col3,Col8 Where Col2 is not null order by Col2 DESC")

Collectives™ on Stack Overflow

Importing data from multiple sheets dynamically and sort by date

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related