Multiple Excel Data Forms 2

[chtullu]

Hello everyone,

Here's the setup. Users in my department have to reconcile the currency amounts in two different worksheets. In order to do this, they start Excel, then open both Excel files. They then arrange each worksheet, one above the other via Windows -> Arrange -> Horizontal. They then compare the relevant values in each worksheet to find matches. Each worksheet has a large number of columns so consequently there is a lot of scrolling going on as well as losing one's place. In addition, most people read from left to right, so reading from top to bottom doesn't seem to be intuitive. Is it possible, create non modal dataforms for each worksheet that will appear at the same time and allow users to view all the columns at one glance from both worksheets without the necessity of scrolling. Any suggestions? Is it even possible?

Thanks

 

[SkipVought]

chtullu,

Why does anyone have to physically LOOK at columns of numbers to compare?

Why don't you write a compare routine?

Either a column compare is identical or it is not. If its not, then you might highlight the differences in some way.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[chtullu]

Thanks for your response. Actually, I should have gone into more detail. These excel sheets are popuplated by an access application that I created. This application compares two tables - tblCadence, tblBrinks. Each table has several fields that correspond to fields in the other table, though with different names. The application looks for matches based on two criteria EnvelopeNumber (tblCadence) to ReferenceNumber (tblBrinks). The Matched results are placed in tblMatchedCadence, the records from tblCadence that were not matched are placed in tblUnMatchedCadence, while the records from tblBrinks that were not matched are placed into tblUnmatchedBrinks.

The unMatched tables are then exported to Excel where the tables are manually matched. Now I had thought to just create another lever of matching in Access, but the matching criteria, according to the users, is not always consistent - there are many variables in performing a manual match so consequently the users prefer to eyeball potential matches in Excel. I want to make it easier for them to perform their eyeball analysis. In addition, there is a great deal of resistance to change in the department, so I have had to develop applications that adhere to their procedures as closely as possible.

 

[chtullu]

You know I was just thinking. Why not keep it in Access. I could create two subform for each of the unmatched table and have each one display in datasheet view. Now the fields will all be visible at the same time - I could create a view details function that would display a subform form in form view for each record selected in the datasheet view. I could place these forms at the top of the main form while the datasheet views would be at the bottom of the main form. I haven't thought it all through yet, and the details are a bit hazy but perhaps these is the best way to go. It would eliminated the need to export to excel, make the changes in excel, and import the results into the database.

 

[PHV]

And you can play with the unmatched query wizard ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chtullu]

Thanks to everyone for their help.