O
OscarC
I am in the process of developing a spreadsheet for recording time
spent on various projects.
The spreadsheet contains two sheets.
Sheet1 ('Project') contains project numbers in column A. Columns H-N
are labelled Mon-Sun for a particular week. Each row in columns H-N
will summarise the hours spent on a particular project for each
employee.
Sheet2 ('Time Sheet') contains time sheets for a particular employee.
For example in the range A5:O23, there is a time sheet for a
particular week for employee Mr Bloggs. In range R4:AF23 there is
another time sheet for the same week, for employee Ben Smith. Project
numbers are entered in column A and column R for each individuals time
sheet, and the hours spent on each project entered in columns I-O for
Mr Bloggs and columns Z-AF for Mr Smith (each column is a day of the
week).
In sheet1, lets say in row 8 I have the following formula in H8 which
is looking to summarise the hours for Monday:
=(VLOOKUP($A$8,'Time Sheet'!$A$5:$I$23,9))+(VLOOKUP($A$8,'Time
Sheet'!$R$5:$Z$23,9))
The first part of the foumula (left hand side of the +) looks at cell
A8 to see what the project number is, then looks at the Time Sheet in
the range A5:I23 to look for the same project number then gets the
hours for Monday from column I for whatever row the matching project
number was found in. e.g if the project number in cell A8 (sheet1)
was "TDH/001/755", then the formula found "TDH/001/755" in cell A10 in
sheet2, then the formula would return the number of hours in cell I10
(e.g. 2.5). The second part of the formula (right hand side of the +)
would again look at cell A8 on sheet1, but then look for it in the
range R5:Z23, and return the hours from column Z.
The problem I have is that the formula returns an error if the project
name is only found in one employees time sheet and not the other.
e.g. if Mr Bloggs time sheet showed he had been working on project
"TDH/001/755", but Mr Smith had not, then the formula returns an
error. How do I adapt the formula so that it does not do this?
thanks,
Michael
spent on various projects.
The spreadsheet contains two sheets.
Sheet1 ('Project') contains project numbers in column A. Columns H-N
are labelled Mon-Sun for a particular week. Each row in columns H-N
will summarise the hours spent on a particular project for each
employee.
Sheet2 ('Time Sheet') contains time sheets for a particular employee.
For example in the range A5:O23, there is a time sheet for a
particular week for employee Mr Bloggs. In range R4:AF23 there is
another time sheet for the same week, for employee Ben Smith. Project
numbers are entered in column A and column R for each individuals time
sheet, and the hours spent on each project entered in columns I-O for
Mr Bloggs and columns Z-AF for Mr Smith (each column is a day of the
week).
In sheet1, lets say in row 8 I have the following formula in H8 which
is looking to summarise the hours for Monday:
=(VLOOKUP($A$8,'Time Sheet'!$A$5:$I$23,9))+(VLOOKUP($A$8,'Time
Sheet'!$R$5:$Z$23,9))
The first part of the foumula (left hand side of the +) looks at cell
A8 to see what the project number is, then looks at the Time Sheet in
the range A5:I23 to look for the same project number then gets the
hours for Monday from column I for whatever row the matching project
number was found in. e.g if the project number in cell A8 (sheet1)
was "TDH/001/755", then the formula found "TDH/001/755" in cell A10 in
sheet2, then the formula would return the number of hours in cell I10
(e.g. 2.5). The second part of the formula (right hand side of the +)
would again look at cell A8 on sheet1, but then look for it in the
range R5:Z23, and return the hours from column Z.
The problem I have is that the formula returns an error if the project
name is only found in one employees time sheet and not the other.
e.g. if Mr Bloggs time sheet showed he had been working on project
"TDH/001/755", but Mr Smith had not, then the formula returns an
error. How do I adapt the formula so that it does not do this?
thanks,
Michael