i have three worksheet in excel - timesheet, employee and projects
timesheet has columns - employee name, date, project no, time spent in hrs
employee sheet bears columns - employee name, position, dept, staff salary
projects sheet has columns - project no, project name,projected hrs
i have converted all the above sheet in tables and have given relation as
employee timesheet
employee name (Primary) employee name(foreign) 1-many
projects timesheet 1-many
Project No(Primary) project no(foreign)
my query is when i create a costing sheet in pivot table
(row section) (value Section)
Project Name
dept Staff Salary
Total hrs Spent
Cost = Staff Salary*Total Hrs Spent
- dept and salary is taken from employee table
- project name is taken from projects table
-total hrs spent from timesheet
but when i create this pivot, its not giving me correct result as its giving me message to create relationship, but the fact is there is no common field between project and employee table how will i create relationship between these two table
to summarise , i need to draw a pivot table to show projecwise how many employees were engaged , their total hrs spent, their salary(or rates per hr) and the totak cost involved in that project
Early reply will be really helpful
Hi Aisha,
Welcome to our forum! It's difficult to help without a mockup file. In future, please take a few minutes to create one and share it.
Form what you describe, you need to add the Cost as a calculated column to the Timesheet table. You can do this using the RELATED function e.g. in the Timesheet table:
=Time spent * RELATED(Salary)
Then you can use this Cost field in your PivotTable which has a relationship with the Projects table.
Mynda
