Making Excel act like a database.

[jfrick]

I need some help on a common problem in our company.

We have several clients that we have agreed to do some Excel 2003
spreadsheets for. The spreadsheets analyze our performance for them.

These spreadsheets follow a very specific formatting that the clients
have gotten use to and are unwilling to change.

When we have to update the spreadsheets once a month, it can take all
day to do the updating for each client because we are currently using
copy and paste to get the data in the right cells.

I'll explain more in a second, but what I am hoping to find out from
this posting is alternate ways to do the spreadsheet updating to save
us time.

Here is a rough idea of the spreadsheets.

On one worksheet, there are a hundred or more 30 "tables" for each
office location for the client. A "table" is their term and refers to
a set of cells with borders, data and formulas relative to a specific
location for our client. So you might see "tables" like this

SAMPLE of TABLES:
-----
Los Angeles
Month Desc Trans Retail
Jan Brand X 6 $466.07
Feb Brand X 9 $2,740.36
Total 15 $3,206.43

New York
Month Desc Trans Retail
Jan Brand X 81 $11,446.40
Feb Brand X 47 $13,042.66
Total 128 $24,489.06

Now the actual tables are much more complicated and each row much,
much longer.

When I get data for a new month or brand, it comes to me like this:

SAMPLE of DATA FILE:
-----
Los Angeles Mar Brand X 6 $466.07
New York Mar Brand Z 9 $2,740.36

(Again this is a very simplified version of the actual data.

Every month we have to go in row by row and cut and paste a row for
each city into the above table for that city. And to complicate
matters, we don't get one Data File, we get 5. The 5 data files have
a row per city per brand and only some of the cells for that city and
brand. that make up one row in the "Tables". Thus we have to cut and
paste 5 times to finish one row.

I know this is a very bad design, and I can think of several ways to
organize the "tables" better. So far the clients have refused to
allow us to redesign the top "tables".

So my problem is how to get away from the cut and pasting we are
doing. Another way to think of this is we get the data in 5 files and
in a different sort order, and we have to extrract certain cells per
row 5 times, assemble a new row, reorder the data, and apply some very
complicate formatting.

Can anyone think of a way to do our updating more automatically?

I have been experimenting with copying in the 5 data files to separate
worksheets in the same file, one month's worth of data per worksheet,
and then using vlookups for each cell in the "Tables". It is very
tedious work setting these all up, and I am worried the resulting
amount of calculation will be huge.

Are there other people that have the same type of problem? How did
you solve this?

Another thought I had, by the way is this: this record manipulation
reminds me of a database. So maybe we ought to replace our cut and
pasting with an Access routine. Before I go this route, I wanted to
see if there is any way to keep this in Excel.

Thanks for any help or thoughts.

 

[Bob Phillips]

The simplest way would be to create a macro that imports one file, takes a
line and pastes it into the table.

You might be able to record a macro that does some of the basic steps and
amend that to loop all the data.

To add further, we would need a better idea of the data, but it should not
be hard for someone who knows what they are doing.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

One possible solution would be to combine the five data files into a single
file on one worksheet and then use a Pivot Table to display the final results.