0

I'm trying to save a single sheet in Google Sheets to a CSV. In my example it's historical stock quotes formatted for Quicken import. All of the "work" is done in other sheets - specifying date ranges, specifying securities, pulling data from google finance, etc.

The sheet I want to export has a lot of INDIRECT formulas looking at the other sheets and pulling the appropriate data to export. However it appears that the CSV Download makes it so the formulas cannot see data from other sheets. Is that expected behavior? I'd rather not do this with the extra step of copying and pasting as values (even in a script).

Formulas in columns A, B, & C: note Workdays is a named range for the # of NETWORKDAYS of the quote date range

A: =indirect("'Sheet2'!A" & int((row(A1)-1)/Workdays)+2)

B: =indirect("'Sheet1'!R" & mod(row(B1)-1,Workdays)+6 & "C" & int((row(B1)-1)/Workdays)*2+2,0)

C: =if(B1="","", text(indirect("'Sheet1'!R" & mod(row(B1)-1,Workdays)+6 & "C" & int((row(B1)-1)/Workdays)*2+1,0),"mm/dd/yyyy"))

Here's what it looks like online for the date range May 1-May 4:

enter image description here

Here's how it ends up in the CSV file in Notepad:

enter image description here

See this sheet: https://docs.google.com/spreadsheets/d/1Od05DAly6tm6lrIG4Gp7Rn8lDKCbW-1S5AV4OB-AhMo/edit?usp=sharing

  • can you share a copy of your sheet? – user0 May 27 at 15:31
  • added the link to the sheet – Scott May 27 at 15:37
  • 1
    I had an intense go on your sheet and it seems impossible. the only successful export was with copied values. – user0 May 27 at 17:14

Your Answer

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

Browse other questions tagged or ask your own question.