Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a excel calendar that we use for vacation days. Our leave year is April through the following March. I would like to add a 13th sheet where in Column A would be an employee's name, Column B would be their starting leave balance. Column C is where I would like to have a formula that would subtract 8 hours of leave from Column B and put that result in Column C whenever the employees name is added anywhere in the first 12 sheets. Too make it a little more difficult, at times an employee would only take part of a day off. It would be put on the calendar as "Johnson 2L" meaning that Johnson took 2 hours of late leave. In that situation then Column C should only subtract 2 hours from Column B.

share|improve this question
2  
What have you tried so far? Where did it fail? –  Hennes Mar 25 '13 at 16:02
    
Sorry, I should have mentioned I am completely new to formulas. I don't even know where to start. –  Dana Mar 25 '13 at 16:05
    
What's the structure of your first 12 sheets? –  Kev Mar 25 '13 at 16:06
    
The first 12 sheets are a single month of the current leave year. It was an excel calendar I downloaded and altered slightly for our use. For each day there is 4 to 6 "spots" to type in an employees name. Is there some way to post the file here for viewing? –  Dana Mar 25 '13 at 16:13
    
You can post a link to a screenshot on imageshack or something maybe. But if not, you have days in column A and columns B-G are where their names might be typed? –  Kev Mar 25 '13 at 16:15

1 Answer 1

On Sheet13, paste each of these lines into C2 through O2, but make sure to press Ctrl+Shift+Enter rather than just Enter. Then you can fill down with that formula.

=SUM(IF(IFERROR(FIND(A2,Sheet1!B$4:O$33),0),IFERROR(1*MID(Sheet1!B$4:O$33,LEN(A2)+2,LEN(Sheet1!B$4:O$33)-LEN(a2)-2),IF(Sheet1!B$4:O$33=A2,8,0)),0))
=SUM(IF(IFERROR(FIND(A2,Sheet2!B$4:O$33),0),IFERROR(1*MID(Sheet2!B$4:O$33,LEN(A2)+2,LEN(Sheet2!B$4:O$33)-LEN(a2)-2),IF(Sheet2!B$4:O$33=A2,8,0)),0))
=SUM(IF(IFERROR(FIND(A2,Sheet3!B$4:O$33),0),IFERROR(1*MID(Sheet3!B$4:O$33,LEN(A2)+2,LEN(Sheet3!B$4:O$33)-LEN(a2)-2),IF(Sheet3!B$4:O$33=A2,8,0)),0))
=SUM(IF(IFERROR(FIND(A2,Sheet4!B$4:O$33),0),IFERROR(1*MID(Sheet4!B$4:O$33,LEN(A2)+2,LEN(Sheet4!B$4:O$33)-LEN(a2)-2),IF(Sheet4!B$4:O$33=A2,8,0)),0))
=SUM(IF(IFERROR(FIND(A2,Sheet5!B$4:O$33),0),IFERROR(1*MID(Sheet5!B$4:O$33,LEN(A2)+2,LEN(Sheet5!B$4:O$33)-LEN(a2)-2),IF(Sheet5!B$4:O$33=A2,8,0)),0))
=SUM(IF(IFERROR(FIND(A2,Sheet6!B$4:O$33),0),IFERROR(1*MID(Sheet6!B$4:O$33,LEN(A2)+2,LEN(Sheet6!B$4:O$33)-LEN(a2)-2),IF(Sheet6!B$4:O$33=A2,8,0)),0))
=SUM(IF(IFERROR(FIND(A2,Sheet7!B$4:O$33),0),IFERROR(1*MID(Sheet7!B$4:O$33,LEN(A2)+2,LEN(Sheet7!B$4:O$33)-LEN(a2)-2),IF(Sheet7!B$4:O$33=A2,8,0)),0))
=SUM(IF(IFERROR(FIND(A2,Sheet8!B$4:O$33),0),IFERROR(1*MID(Sheet8!B$4:O$33,LEN(A2)+2,LEN(Sheet8!B$4:O$33)-LEN(a2)-2),IF(Sheet8!B$4:O$33=A2,8,0)),0))
=SUM(IF(IFERROR(FIND(A2,Sheet9!B$4:O$33),0),IFERROR(1*MID(Sheet9!B$4:O$33,LEN(A2)+2,LEN(Sheet9!B$4:O$33)-LEN(a2)-2),IF(Sheet9!B$4:O$33=A2,8,0)),0))
=SUM(IF(IFERROR(FIND(A2,Sheet10!B$4:O$33),0),IFERROR(1*MID(Sheet10!B$4:O$33,LEN(A2)+2,LEN(Sheet10!B$4:O$33)-LEN(a2)-2),IF(Sheet10!B$4:O$33=A2,8,0)),0))
=SUM(IF(IFERROR(FIND(A2,Sheet11!B$4:O$33),0),IFERROR(1*MID(Sheet11!B$4:O$33,LEN(A2)+2,LEN(Sheet11!B$4:O$33)-LEN(a2)-2),IF(Sheet11!B$4:O$33=A2,8,0)),0))
=SUM(IF(IFERROR(FIND(A2,Sheet12!B$4:O$33),0),IFERROR(1*MID(Sheet12!B$4:O$33,LEN(A2)+2,LEN(Sheet12!B$4:O$33)-LEN(a2)-2),IF(Sheet12!B$4:O$33=A2,8,0)),0))
=B2-SUM(C2:N2)

Some assumptions:

  1. Sheet 13's row 1 is used for header names like name, starting, remaining.
  2. Your first 12 sheets are called Sheet1 through Sheet12.
  3. All the sheets look roughly like your scan, i.e., the area we want to search for names is B4:O33 on any given sheet.
  4. There are no typos in people's names.

This will make a monthly total, and then a grand total in the O column.

share|improve this answer
    
I get an error saying formula contains more values, names, references than is allowed. Could I email someone the file, might be easier than dealing with me, the formula newbie. –  Dana Mar 25 '13 at 18:51
    
No worries, I think we can fix it here, just a sec –  Kev Mar 25 '13 at 19:25
    
My sheets aren't named sheet1, sheet2, etc. They are named Apr 2013, May 2013, Jun 2013, etc. But I can always change the formula a little to correspond. The range might be a little off on some as well but I can change that if needed. –  Dana Mar 25 '13 at 19:45
    
Sure thing. Don't forget to change all instances within the formula. Also, since your sheet names have spaces in them, you'll need apostrophes surrounding the sheet names, like 'Apr 2013'!B$4:O$33. –  Kev Mar 25 '13 at 20:23
    
Will give it a shot tomorrow. Thanks for your help. WIll let you know if it worked –  Dana Mar 25 '13 at 20:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.