I'm feeding various lines of data from different spreadsheets that have a date dependent naming syntax (apart from the date in each spreadsheet varying with the date the data is relevant for, the name of each spreadsheet I am feeding data from remains the same). Is there a way in which I can lookup the various figures in a more automated way rather than setting up the lookup and then manually editing the spreadsheet names from one cell to the next?
For example, in cell A1 I have the date 31/12/2015, and I want to populate cells B1 to AB1 with data from another spreadsheet in which the name includes the date 31/12/2015.
To populate the next row which is for the date 01/01/2016, I know that manually I can drag the formula in row A down and then change the spreadsheet name the data is being fed from to the same document name with the date 01/01/2016, and so on but I am doing this for over 200 entries; hence I am looking at automating this as much as I can.
Any ideas would be really appreciated.