Reference or fill series for part of a file location?

Copper Contributor

Hi,

I'm frequently analysing data that involves pulling it from 12 separate monthly files, so a table with data labels vertically and 1 column per each of the 12 months horizontally. I will typically create my formulas for the first month to pull in whatever data I need and copy these across to the other 11 months.

I then need to change, 11 times, the location and file being referenced from "Mydata\Jan 19\Data file Jan 19.xlsx" to "Mydata\Feb 19\Data file Feb 19.xlsx", and Mar, Apr, etc. At the moment I do this with the Replace function, which is tediously slow. For any other look up I can get Excel to complete the series, moving the cell references across with the data. I can't figure out how to either get the text of the file location and name to be treated as a series MMM and increment, or to reference another cell that holds the text I want, eg ""Mydata\"&A1&" 19\Data file "&A1"&" 19.xlsx"

Any ideas? I just want to be able to drag cross, or pre-set up something that allows my data location to increment by a month as you move along the columns.

1 Reply

@Al_kp 

That could work with INDIRECT(), but only if source file is opened.