How to click and drag down an "Indirect" formula

[GuyGadois]

I have the following formula and I want to drag it down 100 cells and have the cell references update and not keep the original reference. Is that possible?

=IF(INDIRECT("'"&Reference_Sheet&"'"&"!A5")="","",INDIRECT("'"&Reference_Sheet&"'"&"!A5"))

I want the next cell down to read
=IF(INDIRECT("'"&Reference_Sheet&"'"&"!A6")="","",INDIRECT("'"&Reference_Sheet&"'"&"!A6"))

but it doesn't, it keeps the original cell reference the entire way down.

Any help is appreciated

Guy

 

[Eric W]

Perhaps:

=IF(INDIRECT("'"&Reference_Sheet&"'!"&CELL("address",A5))="","",INDIRECT("'"&Reference_Sheet&"'!"&CELL("address",A5)))

or if you are returning text data:

=INDIRECT("'"&Reference_Sheet&"'!"&CELL("address",A5))&""