I have series of spreadsheets containing fund information with each spreadsheet having a file name in the format “2012-xxxx [space] fund name” e.g. “2012-0001 ABC Fund.xls”, “2012-0101 Emerging Markets Fund” etc. These spreadsheets will share a similar template but not all the funds are of the same type and therefore there may be less or more field names. Also the location of the field names may not necessarily be in the same row (but will be in the same column) for the different category of funds.
To try and have a “summary spreadsheet” which will open [or if more efficient, somehow “read”] a select range of cells in a range of spreadsheets in a folder between [where the file name begins “2012-xxxx”] and [“2012-yyyy”] and copy certain cells of information to the target spreadsheet.
Regardless of the name of the fund after the [reference number] in the file name, look at all spreadsheets in a folder called “Z:\Dump\Test_Funds” (with underscore) that meet the above filter criteria i.e. that begin with [whatever prefix is in cell A3] followed by [B3] and ignoring the fund name after that, open each spreadsheet upto [whatever prefix is in D3] followed by [E3.
Each file that is opened/read according to the above filter, should then copy and paste the value of the adjacent right cells containing the words “Fund’s name” in column B to the target spreadsheet starting under cell B10 and also “Fund's NAV (MN EUR)” starting under C10. The formula should be programmed to the macro button please. I know this task is best done in a database of some sort, but this is not an option.
Hope that’s clear, if not please ask.