Is this possible???

[Guest]

I'm working on a spreadsheet to track time off for employees. I've set it up so that if a value is entered for 2003 in Row B, an additional 47 hours are entered in Row E. However, I need to make sure it only enters the 47 hours once. I can't just put in that if the value above it is 47 to return 0, because it's possible that there will be more than 3 lines entered. Is it possible to do this? I've managed to keep away from VB so far doing other things on this sheet and I'd like to do this without VB if possible now too as I'll need to explain how this work to other who don't know any VB. However, if necessary I can use it. But how!?!? Please Help!!

 

[Guest]

You will need to use an extra 2 columns
e.g. in column F concatenate your employee id and 47, if employee id in column A the formula would be =A1&47
In column G concatenate your employee id and the value in column E.
e.g. if employee id is in column A then the formula would be =A1&E1.

We can now use a Vlookup to check if the employee id and 47 in say Cell f12 is listed already in Cells G1 to G11.

I don't know what formula you have in column E, but amend it to start with a Vlookup which will look for the "Employee Id & 47" in the second of the new columns.
paste this formula into cell E12, after you have completed columns F and G,
IF(ISERROR(VLOOKUP(F13,G$1:G12,1,FALSE)),"ENTER YOUR FORMULA TO SHOW 47 HERE","ZERO")

I don't know if I have explained this clearly enough but I hope it helps.

 

[Guest]

No, I understand what you mean. I used it and it worked. Thanks