QUESTION: Using Excel 97-2003
I use the following formula to carry staff hours from weekly payroll tabs to a holiday accrual sheet:
The problem I have found is when employees are on the weekly payroll more than once (for example when they are paid at more than one rate) this formula only carries the total hours from the first row, and ignore the rows below which have total hours at different rates.
I've tried adding to the formula to make it search again but I'm having no luck!
Could you advise?
You are correct on the way Vlookup works. If you need to sum hours from column K based on the value in column A I would suggest
and this will give you the total hours regardless of whether there is one row or multiple rows for that employee. (this assumes hours are stored as numbers which would seem logical)
---------- FOLLOW-UP ----------
QUESTION: Hi Tom,
This formula seemed to work great until random cells came up with #value!
I've checked the format of the cell is general or number which doesn't seem to alter the result.
When I edit the formula the 'save as' box opens with 'update values:wk31' at the top. (as if I was trying to open a new document, and the display at the top is like the title)
I click cancel and get #value!
The formula then looks like this:
I've tried taking out the extra areas but it doesn't work.
I'm not sure if this is a cell issue or a problem with the tab/range being used?!
Please help again!
What you describe sounds like what one gets when they have a bad reference in terms of a bad sheet name.
for example (or to confirm) in a new workbook I entered the formula
and got the update:Sheet4 dialog. I don't have a Sheet4 in that workbook.
So it sounds like you don't have a sheet named WK31. If you think you do it is possible it is really
"WK31 " or " WK31"
Try renaming the sheet to just WK31