CODEWITHSUNDEEP
excelreferenceexcel-formulaformula
adrenom
adrenom

Reputation: 47

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:
=IF(ISNUMBER(FIND("Yes",'HI Project Work Database'!D2)), ('HI Project Work Database'!$B2&" - "&'HI Project Work Database'!$A2), "")

Upvotes: 1

Views: 947

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

You can replace the references with INDEX formulas:

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

Upvotes: 2

Related Questions