Calculated fields using VLOOKUP functionality

Question

I have a large body of information (timesheets for projects). I need to report in a way that is helpful to project managers and more senior managers.

Currently using pivot table.

For a given project code I can derive the project name and the area of the business it relates to - but needs a VLOOKUP.

I can add a column to that data and use VLOOKUP - but would be more elegant to have a calculated field do the lookup. I can't see a way of doing that. Am i missing something? Add Field tells me that I can't use a reference - just do sums with the fields.  

Do I need to do something with SQL to do the lookups and summarise the query result?

This has come up before but I have always avoided the issue and tacked on the extra column.

Am on Excel 2003 but can get access to 2007 if required

Mike

Answer 1

(invented) Example as requested:

If I have a huge file of sales figures and want to summarise by department and/or add descriptions I would like to get that data from a lookup table rather than add a column to a file which is already very big. An ideal way would be if Excel allowed you to create a Calculated Field which is calculatd by doing a lookup. I have seen this issue many times in past. example data below

Part	Qty
0001	5
0002	6
0003	6
0004	6
0005	66
0006	6
Part	Description	Dept
0001	Wheel	Wheels etc
0002	Axle	Wheels etc
0003	Cylinder Block	Engine
0004	Piston	Engine
0005	Front Seat	Interior
0006	Rear Seat	Interior

I could then summarise with Dept as a Page or Row field and Qty as Data. You could imagine period being a column field.

Mike

Answer 2

Hi, could you post a sample data and the results required, it wil help