CODEWITHSUNDEEP
vbaexcelexcel-formula
adam Wadsworth
adam Wadsworth

Reputation: 784

INDIRECT If Statments keeps giving #REF

I'm trying to change an if statement to an indirect one as the current one that works keeps changing from C27 to #REF! when I delete Row 27 in the Tracker sheet. 

  =IF('Tracker Sheet'!C27="","",'Tracker Sheet'!C27)

This is the old if statement that works 

  =IF(INDIRECT('Tracker Sheet'!C27)="","",INDIRECT('Tracker Sheet'!C27))

This is the if statement that I try to convert but gives me #REF!

Any help would be greatly appreciated

Upvotes: 0

Views: 41

Answers (1)

Scott Craner
Scott Craner

Reputation: 152660

Use index:

=IF(INDEX('Tracker Sheet'!C:C,27)="","",INDEX('Tracker Sheet'!C:C,27))

As long as you use the cell address directly when it is deleted it will return #REF as you are deleting the reference.

This will always look at what is the current 27th row in column C.

Upvotes: 1

Related Questions