Combining data from Excel with Outlook address book to mail bills

[Joe Porkka [MSFT]]

I'm trying to setup an automated way to send computed bills to a list of
people in an organization (about 300).
We collect data (water usage) and use Excel to compute the bill based on
usage (current reading - previous times rate).
Each row is for a particular meter, and has a number.
We have a seperate list of people, with mailing addresses. Some people will
have more than one meter (we could make it one-to-one by creating duplicate
entries for these people as there are so few).

I'd like a way to combine the address list and the excel data and generate
bills - sounds like a mail merge but with two data sources.

Presumably Access could do this, but I don't have any experience there and I
want people after me to be able to understand what I've done.

Anyways, I'm open to suggestings. The addresses do not have to be in
Outlook, but it is convenient since Outlook Address Book does a good job at
managing this data.

 

[John Bundy]

If you are in contacts, you can select File and then export to Excel,
Personally I would do that and then vlookup from the meter sheet to get the
name and addresses from the outlook sheet. Then you can sort and see how many
have multiple entries since you say there are so few, and then use that Excel
sheet to setup your mail merge.

 

[Joe Porkka [MSFT]]

HM...I'd like it more automatic.
I can "link" the contacts from Outlook into Access - but this has a number
of problems (you cannot cusomize the link at all and the defaults won't work
for me). I will probably have to write some VBA to get this to work the way I
want it.


Related Question: I have a sheet that looks like
Meter# 1/10/2007 1/17/2007 1/21/2007
1 0 21 44
2 0 45 66
3 0 22 76
4 0 13 45
5 0 45 90

So basically, every week the number of columns will increase as I collect
more data -- my table continues getting bigger and bigger.
Someplace then I will have an column of equations for the current amount due
for each meter that looks like
= (D2-c2) * RATE

Where "D2" and "C2" should be the two most recent columns in the above table
(and RATE is of course the multiplier to convert from meter units to dollars).


This seems like such an obvious thing, yet I've rarely seen a sample
spreadsheet that deals with a constantly growing table. I have struggled with
this problem in various forms for a long time and have never found a good
solution.
The alternative here would be to manually shift the data over each week when
entering the new data - but then I loose the history of readings.

 

[Joe Porkka [MSFT]]

WRT the growing tables problem I asked about, I found "structured
references", which seems to just about solve the problem for me.