Avoiding reference error when deleting rows in another sheet

I have a visual calendar I made using a userform and VBA. The information is fed into separate datasheets and the calendar uses formulas to put the information on the calendar before a macro is ran to merge things together.

Right now, the calendar works perfectly however I noticed that if I go to one of the datasheets and delete a row, the row in the calendar then gets #REF error inside the formula. This makes it difficult to remove old projects off the calendar and move things up.

The formulas on the calendar right now are looking at specific sheet for the value "Yes" under the month (Jan-Dec for 2 years) and then taking the name and project name cell values.

I'm wondering what kind of adjustment I can make to this formula so that a reference error doesn't happen. I looked around and tried to do an INDIRECT/ADDRESS formula but couldn't really get them to work.

Example formula:

Please Login or Register  to view this content.

Hi,

You might consider INDEX as a solution

=IF(ISNUMBER(FIND("Yes",INDEX('HI Project Work Database'!D:D,2))), INDEX('HI Project Work Database'!$B:$B,2)&" - "&INDEX('HI Project Work Database'!$A:$A,2), "")

The workbook does not appear to have made the trip but you can use ROW() rather than a fixed row number- you may need to add or subtract an amount depending on which row the formula cell is in relation to the referenced cell. Copying across columns will not be a problem.