Linking formula to name

[JuliaBird]

I have a spreadsheet which records the number of hours worked each day by a team of guys. Their names are listed in alphabetical order down the first column, and hours horizontally across in date order.

On various other sheets within the same workbook I calculate their entitlements. I've created some pretty long winded formulas to do this - incorporating symbols that are used for unpaid leave (U), sick leave (S) etc - and want to add to them to ensure they are always linked to the one person, regardless of which row his name is on on the original sheet.

Ideally I want the spreadsheet to be transferable to other projects, and more of a template so people can add names and delete them without potentially ruining all the formulas.

For example, one of the formulas is (I'm new to this so it was the only way I could think of!):

Please Login or Register  to view this content.

This corresponds with Gheorghe, but could potentially get disrupted if rows are added or his name is moved about. Is there something like the VLOOKUP function which will make sure the countif's are only ever counted along the same row that George is on?

Example attached

[protonLeah]

1. Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
2. explain the logic of the formula
3. Why is 7 Oct Week 19?

[Ace_XL]

In U3 try this instead..

Please Login or Register  to view this content.

[JuliaBird]

Thanks for replying. It works, but doesn't link the name with the formula. For example, if I move Gheorghe to A13 on 'BEM', I get '#NA' in U3 'Pip'.

I'm sure there must be a way of searching the name, and then COUNTIFs along the corresponding row wherever that may be?

[Ace_XL]

The ranges in the above formula are only till row 12 ..extend them to Row 100 (say). Hence,

Please Login or Register  to view this content.

[JuliaBird]

Extremely impressive - it works perfectly. I'm now plan to study what your formula until I understand it! Thanks so much.