I have a monthly spreadsheet that relies on figures from the previous month. I'd like to import these values dynamically rather than cutting and pasting them. Is this possible? How do I do it?
Google Sheets – How to Link a Cell to Another Document
google sheetsimportrange
Related Solutions
At this time it's not possible because Google Docs doesn't have built-in features to embed external content.
There are features to insert external content like the Insert menu, the Research tool, and add-ons but they are not able to "link" content. The insert an image, insert a drawing, insert a copy of the selected content; the research tool could insert link to a web page, a copy of text or of a image, but not to embed them in the way that an IFRAME works in a webpage or a gadget/widget in a web page or a sharing tool like Google+ among others.
References from the Google Docs Help Center
Insert an image
Insert a drawing
Research tool
With the following formula you can construct a month table, containing all days of the month.
Formula
=ARRAYFORMULA(
DATE(
YEAR(NOW()), // first
A1, // second
ROW( // third
INDIRECT("A1:A" & DAY(EOMONTH(DATE(YEAR(NOW()), A1, 1), 0)))
)
)
)
copy / past
=ARRAYFORMULA(DATE(YEAR(NOW()),A1,ROW(INDIRECT("A1:A" & DAY(EOMONTH(DATE(YEAR(NOW()), A1, 1), 0))))))
Screenshot
Explained
The DATE formula is used to construct the date out of the three integers it needs. The first is the YEAR, being calculated from the NOW formula. The second argument is the month that you want to calculate the days from. The third argument must be a day number. Because we're using an ARRAYFORMULA, this is fed by a ROW range. The range of the rows, is dependent upon the amount of days per month. This number is calculated as follows:
DAY( // days
EOMONTH( // days till end of month in date notation
DATE(
YEAR(NOW()), // from year
A1, // from month
1 // from day
)
)
Example
I've created an example file for you: Days of Month
Related Topic
- Google-sheets – How to use formula values present in another cell in a formula
- Google-sheets – Small Edit on Import Range Script for Mass Edits on Google Spreadsheets
- Google-sheets – Any way to take a value from a cell in another sheet in Google Sheets only once
- Google-sheets – Aggregate results by week and month for charts
- Google Sheets – Reference Adjacent Cell on Another Sheet
- Google Sheets IMPORTRANGE – How to Refer to a Cell Value
- Google-sheets – Way to link to first (leftmost) tab in Google Sheet
Best Answer
IMPORTRANGE()appears to be the function you want.From the Google spreadsheets function list:
"key" is, of course, the string in the URL for the spreadsheet that matches to the
key=parameter.I just tested it by creating two spreadsheets. In cell A1 of the first, I put a string. In cell A1 of the second, I put
=importRange("tgR2P4UTz_KT0Lc270Ijb_A","A1")and it displayed the string from the first spreadsheet. (Your key will obviously be different.)(The format of the function may depend on your locale. In France the formula is not valid with a comma, so you'll need to replace it with a semi-colon:
=importRange("tgR2P4UTz_KT0Lc270Ijb_A";"A1"))NOTES:
Google currently sets a hard limit of 50 "cross-workbook reference formulas" per spreadsheet. Source: Google Docs, Sheets, and Slides size limits. (h/t JJ Rohrer)
The "new" Google Spreadsheet (soon to be the standard) removes the 50 limit of 50 "cross-workbook reference formulas (Google Support) (h/t Jacob Jan Tuinstra)
In the "new" Google Sheets you also use the entire URL as the key (Google Support) (h/t Punchlinern)