Cells not being Populated with exact same formula as previous working cells

Hi so i have a pretty complex formula using if's and vlookup's to populate a calendar with x's for day a person is gone. however the last couple of people that are gone are not being marked on the calendar even though i use the exact same formula (except for cell numbers and such) for previous, working entries. Here is my formula

Please Login or Register  to view this content.

This is in Cell F51 of worksheet Calendar. A51 is the persons name "Raul Sanidad" F4 is the day number, the 4, O2 is the month number 6.

The vlookup references Raul Sanidad and goes to the table with his name and dates away. the index 7 is the start date (6/4/2008) and 8 is the end date (6/20/2008).

I cant post the workbook because it is too large, 800kb. Can anyone see an error right here that would cause the x not to appear in the said dates?
Is there a way i can post my 800kb file so that it can be seen. the entire file is pretty much needed to see the entries that are working and the ones that arent.
Thanks
Danny

You can Zip the file to make it smaller. Right click the file and send to Compressed Folder.

Heres the files

Throughout your formula, you use this

Please Login or Register  to view this content.

whereas your table now extends to $J$10. Does that fix your problem (You can do a search and replace on $J$7)

ChemistB

ChemistB,
THANK YOU SO MUCH. Ive been trying to find an error forever.
Is there a way to make the referenced area grow as new elements to the table are added?

Yes, you can use defined names and Offset to create a dynamic range. Here's a link explaining them better than I would. http://www.contextures.com/xlNames01.html#Dynamic

Once you name it, you'd use the name instead of that entire range so it makes things much simpler in more ways than one (and easier to understand if you name it wisely).
I think the name for your range (which names your table starting at A2) would be

Please Login or Register  to view this content.

Assuming you do not have anything else in the A column besides this table. This will not work if you skip any lines between names in your background table.

ChemistB

Ill try that out when i get back from lunch
when i made that first change now the month number doesnt change when i change the month.

Hmmm, not sure why your month would stop changing. Double check your comma's to make sure you didn't accidently erase something.

That named range should be used to replace both Background!$A$2:$J$7 and Background!$A$2:$J$31.

post your corrected spreadsheet if you can't find out what happened with the month.

ChemistB

ChemistB

is there a way i can refresh my vlookup statements. I have noticed that when i go in and click at the end of my formula and press enter the month number changes to the proper number. the same happens for the x's
thanks
dlroo

Tools>Options> Calculation, make sure "Automatic" is checked off.

ChemistB