Using INDIRECT and IF functions together with dynamic range

1
votes

I'm trying to combine the INDIRECT and IF functions together so I can drag formulas down without getting a #REF! error when I run my macro. Currently, I have one sheet that has all the IF functions and another sheet that it refers to. The current IF function is:

=IF('First Half'!T2="Loblaws","CA",IF(RIGHT('First Half'!T2,6)="Canada","CA","US"))

First Half is the sheet and T2 is the referenced cell. I want to be able to use INDIRECT so if the reference row 2 gets deleted, I won't get a #REF! error. But I also want the formula to be dragged down to reference T3 on the next row.

1
excelif-statementexcel-formulaworksheet-functionexcel-indirect
Sorry, same sheet, just a typo. – kmiao91

1 Answers

1
votes

Please try (in Row2 or apply an offset to each ROW()): 

=IF(INDIRECT("'First Half'!T"&ROW())="Loblaws","CA",IF(RIGHT(INDIRECT("'First Half'!T"&ROW()),6)="Canada","CA","US"))