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

[dlroo]

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

=IF(ISNA(IF(AND(OR($O$2=MONTH(VLOOKUP($A51,Background!$A$2:$J$31,7,FALSE)),$O$2=MONTH
(VLOOKUP($A51,Background!$A$2:$J$7,8,FALSE))),AND(Calendar!F$4>=DAY(VLOOKUP(Calendar!$A51,Background!
$A$2:$J$31,7,FALSE)),Calendar!F$4<=DAY(VLOOKUP(Calendar!$A51,Background!$A$2:$J$31,8
,FALSE)))),"x","")),"",IF(AND(OR($O$2=MONTH(VLOOKUP($A51,Background!$A$2:$J$31,7,FALSE)),$O$2=MONTH
(VLOOKUP($A51,Background!$A$2:$J$7,8,FALSE))),AND(Calendar!F$4>=DAY(VLOOKUP(Calendar!$A51,Background!
$A$2:$J$31,7,FALSE)),Calendar!F$4<=DAY(VLOOKUP(Calendar!$A51,Background!$A$2:$J$31,8,FALSE)))),"x",""))

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

[TIPPYS]

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

[dlroo]

Heres the files

[ChemistB]

Throughout your formula, you use this

Background!$A$2:$J$7

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

ChemistB

[dlroo]

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?

[ChemistB]

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

=OFFSET(Background!$A$2,0,0,COUNTA($A$2:$A$50000),10)

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