Thread: general architecture question

Hi,

I am developing a spreadsheet solution and would just like to post it to receive any comments to see if there is an easier way to do it, maybe its already been done, etc.

First we have a .CSV file that I receive from another system.

this file has 3 comma delimited columns
department, name, loginname

I will create a VBA subprocedure to copy the CSV data into a worksheet of a main spreadsheet file.

The next step is to create another worksheet and its contents will be to
use a column for each department. So, the worksheet data would look something like this.

column A column B Column C
HumanResources ITDept MarketingDept
John Smith:jsmith Jane Doe:jdoe John Black:jblack
John Red:jred Jason Green:jgreen Sally Blue:sblue
etc etc etc

Now The number of departments can be dynamic and the members of the department can be dynamic so my code must be able to dynamically populate the worksheet in in this way.

Next part will be to create defined names for each department Each department will be a range that includes all the cells that contain names for that column/department.

Now I will have another spreadsheet that uses these "defined names" to generate drop down list boxes that are dynamic: If a user clicks on a department in column D cell then in column E cell the dropdown list only has items (names) for that department. This is a very common request
feature and I have an example already found for this.

Once a user has filled out the spreadsheet a button will exist to export to another CSV file.

This entire exercise is so that users do not have to enter users, usernames, and deptment codes, which is prone to error.

The output CSV file is uploaded by a nightly job into another system and if there are spelling mistakes(causing lookups of users in another database to fail) the file is rejected.

Just wondering if this sounds reasonable?