Excel reporting

  oo7juk 11:24 16 Feb 2009


At the moment I'm using an Access program that I don't like, more comfortable with excel, but don't no where to start?

Current stage:

1. I receive excel doc containg invoice data.

2. I rename doc and save to a directory.

3. I open Access, enter invoice number and this runs a query.

4. As a result of the above, a report is generated producing figures per department.

5. I use the above to square with original hard copy of invoice.

The above looks at the job number e.g. etd10 assisgns to department - Blue and generates total for that department.

What I would like excel to do:

When I receive excel doc, rename and save to directory.

Create spreadsheet that looksup above workbook and genrates same report.

The access prg has a table with 3 columns 1st - job number, 2nd - description and 3rd - department.

Can I create a workbook that refers to specific workbook and once I enter current invoice number it generates a breakdown of the invoice per department. The original invoice is sorted per job number and each job number(s) are assigned to a particular department.

I was thinking of using a vlookup, but that would only return one result. I'm looking to split a figure e.g. £5,000 into 5 different departments witha total using the job no fromthe original spreadsheet I receive electronically from a third party.

i don't know if this can be done without using VB. I know how to create command buttons, but that is for opening other sheets within a workbook.

e.g. of Access table

Job no Description Department value
et100 paper blue £100
et130 stamps yellow £149
et150 envelopes green £170
et200 imaging blue £200

The report would generate a report detaling the following:

blue £300
yellow £149
green £170

I would have to set up a table that assigns the job no's to department. Various job no's are assigned to the same department.

I'm using excel 2002

Many thanks

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Alienware 17 R4 2017 review

Illustrator Sylvain Tegroeg created thousands of intricate line drawings for the mobile game…

Best iPad buying guide 2017

Comment télécharger une application indisponible en France ?