Not sure what to do

[vdavissva]

My boss gave me a spreadsheet (example below). The data carries on in rows with about 20 doctors (each separated with their own individual Charges/Adjustments/Net Receipts rows) and columns until the end of the year 2001, then there is another similar worksheet for 2002, and I would possibly create more for future years. He asked me to create a formula or print macro or ??? so that the client could use shortcut keys to enter a month and a doctor's name and get the data for just that doctor to print out on an individual page. That's what they want to have happen. I thought of creating a Visual Basic program that could do this, but that might be too complicated and time-consuming. I haven't used Advanced Excel too much, but I'm considered "the expert".

It sounds simple and I think it's in the deep recesses of my brain somewhere how to do this, but everything I've tried isn't satisfactory, assuming that the client knows nothing about Excel and I won't be there to teach them.


Dr.Wilson 1/01 2/01
Charges 30,918 22,008
Adjustments 21,453 8,954
Net Receipts 26,287 17,563

[Von Pookie]

Would Autofilter be an option?

Just have them choose what they want from the list and then just print it to show what they filtered out of the list.

[vdavissva]

Autofilter would be too easy. They want to be able to compare current month to this month last year or end of 2001 to end of 2002 (at the end of the year) and have this print out on a page as an individual report. But I've never done reports like this in Excel. It sounds like something I've done in database software, but I don't have experience in Excel reports yet.

[Chris Davison]

On 2002-05-08 07:31, vdavissva wrote:
My boss gave me a spreadsheet (example below). The data carries on in rows with about 20 doctors (each separated with their own individual Charges/Adjustments/Net Receipts rows) and columns until the end of the year 2001, then there is another similar worksheet for 2002, and I would possibly create more for future years. He asked me to create a formula or print macro or ??? so that the client could use shortcut keys to enter a month and a doctor's name and get the data for just that doctor to print out on an individual page. That's what they want to have happen. I thought of creating a Visual Basic program that could do this, but that might be too complicated and time-consuming. I haven't used Advanced Excel too much, but I'm considered "the expert".

It sounds simple and I think it's in the deep recesses of my brain somewhere how to do this, but everything I've tried isn't satisfactory, assuming that the client knows nothing about Excel and I won't be there to teach them.


Dr.Wilson 1/01 2/01
Charges 30,918 22,008
Adjustments 21,453 8,954
Net Receipts 26,287 17,563

Okay, so someone puts in a Doctor's name and a month and you want it to return all his info from all the financial years....

Could this be a job for :

a) data validation (consisting of a list of the doctors names and a list of relevant months)

b) vlookup (where the vlookup value is a concatenation of the name and month)

seeing as the info is in different rows and columns for a particular doctor in a particular month, you can incorperate OFFSET to bring back the relevant relative rows and columns once a match to the doctor's name and month has been found

does this sound viable ? if it does but you don't know how, just repost and I'll post an detailed example

if you're okay with the terminology, good luck

[Jack in the UK]

Chris as a mate pleasse tell me e97 with ME ??? any good.. i have never got on with 98 >>>

[artslave]

Have you explored Pivot Tables? (Data/Pivot Table) Check out Excel Help to get an overview or get started, and we can help with specific questions.

Pivot Tables can be finicky about the layout of the source data, so it's possible they may not be usable for you, but they are also very flexible in output, so if you get a result that's ALMOST what you need, it can probably be tweaked.

You could use "Doctor" as a page field, there's then a "Show Pages" command that would provide a separate worksheet tab for each doctor.

Have a look, in any case....it's a useful tool for any Excel user.


Catherine

[Nimrod]

You may want to use the "Mail Merge" wizard in Word. This allows you to create a form letter or form then point to excel as your database. ... Check out the help file in word and search for info on Mail Merge.

[lenze]

The real problem is the layout of the spread sheet. If possible, I would redo it to be more like a database table with columns for fields.
Doctor,Date,Charges,Adjust,Net Recpts,etc

Now each row in the table will represent one doctors information for one specfic date. You will have lots of rows(records)but inputing will be easier(you can use a form) and manipulating the data will be a snap. You will have a choice of autofilter, Dfunctions,Pivot Tables,Array Formulas, etc.

[ This Message was edited by: lenze on 2002-05-09 14:18 ]

[Jack in the UK]

be very careful with Array formuls, or only use a few,., they slow and can trash a sheet in seconds, they are cool good and very good if used light lightly

level,, if you know they are there you have to many be careful..

as suggested use database function hard to use at first but VERY powerful.. top end i say...