Deleting rows creates error on associated sheets with this function

**md1972** · 10-29-2009, 06:34 AM

I have a function which I inherited and have subsequently changed and adapted for other spreadsheets.

The function layout is:
=IF(ROWS($A$2:$A3)>COUNTIF('A'!$E:$E,"nnnnn"),"",INDEX('A'!A$2:A$998,SMALL(IF('A'!$E$2:$E$998="nnnnn",ROW('A'!$E$2:$E$998)-ROW('A'!$E$2)+1),ROWS($A$2:$A3))))

The function searches worksheet A (in this case, a list of all staff members) and determines which department and role (nnnnn) the staff member belongs to, then updates another worksheet, relevant to that department and role (there are about 10 worksheets) with that data for that cell IF it isn't already present on that sheet.

This works perfectly, until a row is deleted from the source sheet, in which case, each cell in all of the other sheets returns an error.

This can be overcome by blanking out all of the data in that row, but this sheet is for another member of staff to use so I would prefer it if they could have the option to just delete rows when a member of staff leaves.

Is there anything I can add to the function to allow for rows to be deleted?

I am not able to use VBA, hence the convoluted function and I therefore need a solution using the function

Regards

**DonkeyOte** · 10-29-2009, 08:06 AM

To avoid issues regards row deletion you're realistically looking at either:

a) INDIRECT

b) INDEX

the former is Volatile therefore using in conjunction with already expensive arrays (like yours) is pretty much a no-no - ie your file will invariably grind to a halt.

the latter is viable but will invariably make the formula even longer.

Out of curiosity are the values being retrieved from A!A2:A998 unique or will duplicates exist (to be returned) ?

**md1972** · 10-29-2009, 10:45 AM

The values in that column (and most of the subsequent columns) won't be unique.

I've had another look and, interestingly, it's only when the 1st row of the source file is removed that the error occurs, not any other row.

I have attached a slimmed down copy of the database, with 'All Staff' being the source file and two of the recipient files with it.

Thanks