OK, let me tell you the story behind it and maybe this will make more sense to you.
But before I do, yes, I want the user to be able to write down a file link, then specify cell reference , and then click a button where the macro would pick out these values for them and put them neatly on a row on a new sheet.
The story goes like this:
My boss told me he wanted me to extract the cell values for B5, C7, D10, D14 from this January spreadsheet. (he gave 20 cells actually, but let's use 4 here for siimplicity).
He wanted to see them in a row, next to each other, as he wanted to compare the values. OK, so easy enough so far
However, columns B,C and D represented the data for 1st Jan 2001. Columns E,F,G contained the equivalent data for 2nd Jan 2001, so I also had to pick out E5, F7, G10, G14 and so on accross the spreadsheet until the data ended with the 3 columns for 31 Jan 2001. So, in the result sheet I woudl have 31 rows, each containing the daily data. Ok, so that wasn't too difficult to do, just needed to write a clever loop with offset(0,3i), where i was number of day in the month, no problem.
However, he had a file for every month, and wanted to extract these equivalent cells from all 12 monthly spreadsheets, so that he had the data for every day of the year for the values of this particular fund. This was fine, just had to loop through the 12 files, no problem.
However, there was a hitch in that the files weren't all quite in the same format. For instance, the B5 cell from the January file was not equivalent to B5 from the April file, the April data for column B(for whatever reason) was moved down 2 lines, so I had to pick out B7 instead of B5 etc. However, funnily enough, the format for July-Dec was consistent with the January file, so no change was required here. But April, May and June were inconsistent with the original file, so we had to pick out slightly different cells.
Therefore, instead of hard coding for every month, I decided it would be a good idea to set up an 'input sheet', where the user would specify which cells to pick out from which files, so as to avoid the user going into the code and adjusting the arrays.
Now, with that bit of background I hope you can see why I required a solution to the simple problem of letting an user pick which cells, from what files he/she wanted to be put on a row in a new sheet.
For instance, maybe something like this
Month applicable:
January
Feb
March
November
December
C:FundValueJan.xls
C:FundValueFeb.xls
C:FundValueMar.xls
C:FundvalueNov.xls
C:FundvalueDec.xls
Need to extract values from the following cells:
B5
C7
D10
D14
Then, in another column you would have this
Months applicable
April
C:FundvalueApril.xls
B7
C7
D10
D14
and so on for all different months.
When replying to this post, please concentrate your efforts with what I just mentioned, don't worry about the offset(0,3i) bit since that is already sorted out in my existing macro. What I am most concerned about is setting up an easy to use input sheet where the user can specify which cells , from which files he wants to see and then display the values in a row on a new sheet - one row for each day.
Thankyou,
RET79
