Any solution suggestion to this complex macro problem?

[Martyn]

Here goes my problem,
I have pairs of excel templates with similar names (e.g. "Martyn
Winson-11.03.2004.xlt" and "Martyn Winson-18.03.2004.xlt", "Sue
Patrick-01.03.2004.xlt" and "Sue Patrick-16.03.2004.xlt"...etc). These
templates are identical in terms of structure and consist of 15 Sheets each.
In these Sheets there are spesific cells that contain critical numerical
data for me. Say these cells are: Sheet1's K1, M2, P22, Sheet4's D22, T13
and Sheet15's Z8, AB11.
I need a macro that when executed will compare the data corresponding to the
same cells of different templates (of course new Martyn's template with old
Martyn's template) and come up with some sort of analysis. Say if the value
found in Sheet1 K1 of "Martyn Winson-18.03.2004.xlt" is greater then the one
found in Sheet1 K1 of "Martyn Winson-11.03.2004.xlt", in a new workbook I
want to display in a column an "improved by %" XX (numerical difference in
terms of persentage). Same philosophy is applicable if the newer dated
template comparing cell value is less then the old one but this time the
cell in the analysis column displays "dropped by -%)XX.
If the comparing cells have the same value, the reporting cell can display
"same" message. I hope this is possible and some brilliant codes can be
suggested here. I know there are a number of excellent coders in this group.
Thanks in advance.
Martyn

 

[Tom Ogilvy]

What is the complex part you are looking for help on. If you have the list
of workbooks, it is simply opening the two workbooks, looping through the
cell addresses, making the comparison and using an If statement to record
the results (decrease, same, increase).

If you have all the files in a single directory and you want code to go
through and identify the pairs and identify the oldest and newest, then
state that.

 

[Tushar Mehta]

Like Tom, I am at a loss as to the complexity of the problem. Further,
I see no need for a programmatic solution. All you need is a
'comparison' workbook for each person that contains a IF function!
Depending on your comfort level with XL functions, a single
'comparison' workbook (incorporating a INDIRECT function with data
entry simplified with a drop down box or two) might be all you need
irrespective of the number of people you are dealing with.

Finally, I am at a loss as to why there are templates containing
context-specific data. Templates are meant to be generic workbooks
that when used with specific datasets are saved as XLS workbooks.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Martyn]

Dear Tom and Tushar,

Sorry for classifying it as a "complex" problem.
But I am not very good with excel macro programming and there are elements
in this problem that makes it complex for "me".

1. I don't have the list of workbooks in reserve before starting the
comparison.
2. I must be able to "see" and choose which *.xlt files are to be compared
on the harddisk/floppy/CD.
3. The comparison files may or may not be in the same drive/directory
4. If finding more then two *.xlt files for the same person is possible, it
will be much better to have an "advanced" (for me) solution to this problem
by plotting bar charts for figures in comparing numbers more then two.

p.s.: Don't know the reasoning of why *.xlt file formats are used for
storing data, but it is "the" case.
Martyn

 

[Tushar Mehta]

It appears that what you are looking for is basically a complete
solution. Someone could prove me wrong but a consulting solution is
usually outside the scope of posts in these newsgroups.

You might want to take a crack at the problem and ask for help with
specific tasks where you get stuck.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Martyn]

Hi Tushar,
Thanks for your help and interest.
In fact I am trying to get advice on using elements, methods from people of
this NG while I complete tasks for a "better" solution for me. I am not
expecting a complete solution.
I opened a new thread on this problem of mine upper in the list of messages
and get some advice for some stages. Now my workbook (with a macro) manages
to import data from two "selectable" *.xlt files and compare them as
graphics. I can send the workbook or code to anyone interested. What I am
after is the need to able to compare data for workbooks more then two.
Regards
Martyn