Vlookup and countif help

[Mr Marvin]

im looking for some help with using the above. i can what i want to do manually but it be a long process. the scenario is i have2 worksheets, - absence record and rota. i wanted to keep a master sheet to record any sickness for agents. the set up of the rota is the agents name will be horizontally starting line G2 and the calendar month to the left and if they are off sick that the absence record sheet will automatically count the number of sick days.

The absence record sheet is in a different file and the set up of that is the agents name will run down vertically with the specific month we looking to record horizontal. so i can look back instead of addin up manually, into the absence sheet and see that agent 1 has had 3 days off in November for example.

is it possible that a formula could be used to pick up on the agents name and also add the days off. i was looking at hlookups /vlookups with countif. i can get it add the absences across the worksheet but not for individual agents.

will each agent have to have their own formula to target those cells

tia

 

[SanjayGMusafir]

The absence record sheet is in a different file

I'm not sure Excel formula would work when data is on another file. If it is within same file but on a different sheet - then we can start thinking about what your other requirements are.

Even then a sample data using XL2BB would be required to understand all the other scenario you are mentioning and to understand the possibility of populating some formulae.