|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
May 21st, 2002, 10:36 PM
|
#1 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
I solved an interesting problem on excel a few weeks ago, the aim of asking this question is just out of interest really to see how others would approach this question as I'm sure there is a more elegant way of doing it.
The setup is as follows: There were 12 files of data, one for each month (Jan.xls, Feb.xls etc.) In each monthly file, there was data for each day in that month. For example, the data for 1st January was in cols B,C,D. 2nd January in cols E,F,G and so on until the 31st of January's data. Now the task was as follows - my boss came up to me with a list of data he wanted summarised in chronological order on rows in a spreadsheet. So, he just gave me a list like B5, B8, C53, C36, C87, D2, D34, D100 say and asked to see that data for each day on a spreadsheet. So, on the final summary spreadsheet, you would have dates from A2 down for 1st Jan right down to 31st Dec with the corresponding data in adjacent columns. However, there was a hitch in that the monkey who set up the monthly files had messed around with the structure of some of the files. For instance, for the April.xls & May.xls files, the monkey had inserted some 5 rows after row 70, so that original list would be changed to being: B5, B8, C53, C36, C92, D2, D34, D105 for these months. Anyway, I'm interested in how people here would go about this problem. Needless to say that I felt it appropriate to set up an input sheet thing, because my damn boss kept changing his mind about which cells he wanted to see (and it was not very nice initially to go into VBA code to change things like that, I prefer to do that on an input sheet personally). My input sheet looked a bit like this anyway, but I'm not entirely happy with the setup since I'm convinced there is a more elegant way of approaching this. To see the formula in the cells just click on the cells hyperlink The above image was automatically generated by[HtmlMaker V1.25] If you want this code, click here and Colo will email the file to you This code was graciously allowed to be modified: by Ivan F Moala All credit to Colo Any ideas or comments appreciated. Thanks. RET79 [ This Message was edited by: RET79 on 2002-05-21 21:39 ] |
|
|
|
May 21st, 2002, 10:42 PM
|
#2 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
The macro I had was reading off the values on that input sheet, (which were dynamic ranges) so whatever you put on that sheet was used as arrays in the code.
RET79 [ This Message was edited by: RET79 on 2002-05-21 21:47 ] |
|
|
|
|
May 21st, 2002, 11:00 PM
|
#3 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
OK guys I am off until Friday on a course so won't be here to reply for a couple of days.
RET79 |
|
|
|
|
May 24th, 2002, 10:11 AM
|
#4 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
OK, am back from the course, did anyone have any ideas about this one?
Cheers, RET79 |
|
|
|
|
May 24th, 2002, 04:51 PM
|
#5 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
Oh dear, does no one have any ideas about this one
 RET79 |
|
|
|
|
May 27th, 2002, 02:31 PM
|
#6 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
OK, very last call about this one, anyone have some ideas for me?
RET79 |
|
|
|
|
May 27th, 2002, 03:49 PM
|
#7 |
|
Board Regular
Join Date: Mar 2002
Location: England, UK.
Posts: 526
|
Awww, don't get me wrong I am not askign for anyone to come up with a wonder macro or anything like that, I am just asking generally how people would go about getting this task done, I don't expect anyone to get it done for me!
RET79 |
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
