Timesheet with Attandance Codes & Mutiple Nested Functions

Hi Everyone,

I'm creating a Timesheet that has attendance codes in it. I have a nested function working, but it appears that I have one too many criteria.

Here's the formula that partially works:

'=IF(G55="","",IF(G54=$G$37,$H$37,IF(G54=$G$38,$H$38,IF(G54=$G$39,$H$39,IF(G54=$G$40,$H$40,IF(G54=$G$41,$H$41,IF(G54=$G$42,$H$42,IF(G54=$G$43,$H$43,IF(G54=$G$44,$H$44,,IF(G54=$G$45,$H$45,IF(G58="","0:00",IF(G59>=$J$48,$G59-$G55-$I$48,$G58-$G55))))))))

As you can see, it's quite lengthy. I'm seeking a less complex or another formula that works just as well, but can accept all the attendance codes & be used in Excel 2007 & 2003.

I attached a 2007 version for your review.

The (large formula is in Cell G27 & I would like it to extract the codes & it's relevant hours from the Attendance Codes area.

Thanks

A VLOOKUP() might handle most of the IFs in the middle, but I see you're doing some odd stuff at the end.

Perhaps if you just briefly described what you're trying to total with all those IFs, maybe there's a simpler test we can offer.

Hi,

For the most part, row 27 is calculating the time arrived to the time left, minus lunch. I'm throwing in the attendance code to track the various attendance, such as # of vacation days taken. I'll do this somewhere else, which will be in some type of summary block, which I didn't create yet.

How would VLookup work here?

Thanks

Have you readup on VLOOKUP? You know how it works?

Hi,

I've tried using VLookup in the past & for whatever reasons, never could fully get it function to my needs. Give the scenario & example that I have given, how would I utilize this function or any other applicable function?

Hi JBeaucaire,

I figured out the VLookUp works & for my needs, it works great.! Now, Im trying to figure out how I can sum a row & my matrix table at the same time.

I have a matrix with codes in it, such as:

Column 1 Column 2
V 8
1/2 4

I have another table that lists the tasks & estimated hours for Monday through Sunday.

I wish to sum the week & add also add one of these codes, if the person took a vacation day.

For example,

If a person works 32 hours & takes 1 day vacation, the formula would see "V" & add 32 + 8.

How can I create the formula?

Hi Everyone,

I attached an example file for review & assistance.