1

I have my master Google sheet and I need a weekly updated Excel report to be imported into that sheet. Right now I use

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; IMPORTRANGE("XYZ"; "'Overview'!A4:FZ"); 10; 0);))

Here is the tricky part - I transform the Excel sheet into a Google sheet and it always receives a new link/sheet ID so to get the data from the newest version I need to change all links in all formulas in my master sheet.

My idea is if it's possible to make one cell that would serve as an ID source that I can weekly change to the newest sheet ID and it would be automatically imported from that specific sheet for all formulas.

The report sheet is extremely extensive so I want to avoid copying all data into my master sheet

1

You can do so by referencing ID in a cell like:

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A; IMPORTRANGE(A1; "Overview!A4:FZ"); 10; 0); ))

where A1 will be: 16nnuzNRx_kGGJm1EBV57kBT2bfW1-2TYwSiuaLF-QIA or the whole URL.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.