Making a Number Relative

I have a workbook with lots of worksheets all based on a template. Depending on certain criteria a different number of rows will be automatically deleted on each worksheet to remove rows where there are no entires. The problem I have is because I'm using 'ROW()-75' as part of my formula if any rows above row 75 are automatically deleted the '75' part won't update to reflect the correct number.

Is there any way to make the number relative so that when rows above are deleted, the value will change accordingly? This is my formula:

Please Login or Register  to view this content.

Many thanks

What determines the 75?

Originally Posted by HangMan

=IFERROR(INDEX('Data'!$A:$S,MATCH($C$8&$I$69&$C$71&COUNTIF($B$75:$B75,$B75)+ROW()-75,'Data'!$S:$S,0),MATCH($C$74,'Data'!$A$2:$S$2,0)),"")

The formula is dependent upon which row it's entered on and you haven't provided that detail.

I'm guessing that it's entered on row 75. As you copy the formula down it increments like so:

COUNTIF(...)+0
COUNTIF(...)+1
COUNTIF(...)+2
COUNTIF(...)+3

If that's the case...

Replace: ROW()-75

With: ROWS(A$1:A1)-1

@ Ford

Appologies, it was late... the formula is entered into various different rows but the row number it is entered into is the same as the number, so ROW()-75 is entered in row 75, ROW()-100 in row 100 and so on.

@ Tony

Perfect, that does the trick, I'd totally forgotten about ROWS()... many thanks...

You're welcome. Thanks for the feedback!