Offset and Indirect

[zalik22]

Hi, I have the following formula:

=IF(INDIRECT("'"&$B2&"'!"&"$h$3")="","",INDIRECT("'"&$B2&"'!"&"$h$3"))

I need to increment B2 to B7 when I drag down but it's not working, can i place 5 in another cell and use a formula to change the B2 value to B2 and the cell reference(5)? Thanks!

 

[Joe4]

What cell is the first formula going into (exact address, please)?

 

[zalik22]

What cell is the first formula going into (exact address, please)?

It starts in column C2, but I will need to drag down and to the right.

 

[zalik22]

It starts in column C2, but I will need to drag down and to the right.

=IF(INDIRECT("'"&$B2+i3&"'!"&"$h$3")="","",INDIRECT("'"&$B2+i3&"'!"&"$h$3"))

I want something like this, where i3=5. I know my syntax is wrong since you cannot put B2+i3.

 

[Joe4]

=IF(INDIRECT("'"&$B2+i3&"'!"&"$h$3")="","",INDIRECT("'"&$B2+i3&"'!"&"$h$3"))

I want something like this, where i3=5. I know my syntax is wrong since you cannot put B2+i3.

You don't need a helper cell. Simply replace the references of "B2" in your formula with this, and it should do that you want:

OFFSET($B$2,(ROW()-2)*5,0)

 

[zalik22]

Thanks!

 

[Joe4]

You are welcome.
Glad I was able to help!