Indirect Formula to change row bumber when coping it from from left to Right

[howard]

I have the following formula in cell C2 which yields the correct result

 =INDEX(INDIRECT("'C:\My Documents\["&$B$2&"]"&$H$1&"'!d4"),0,ROWS($D4:D4))

when i copy it in cell C3 the formula must look at Cell D5 in the source workbook. when i copy it in cell C4 the formula must look at Cell D6 in the source workbook etc

It would be appreciated if someone could kindly assist me

 

[Toadstool]

Hi Howard,

Try

=INDEX(INDIRECT("'C:\My Documents\["&$B$2&"]"&$H$1&"'!d"&ROW()+2),0,ROWS($D4:D4))

 

[howard]

Thanks for the help

My Apologies for giving you the incorrect info

The Formula below in C3 refers to Cell D4 in the source workbook. when Copied to D3 it must refers to D5 , when copied to E3 , it must refer to D6 etc

=INDEX(INDIRECT("'C:\My Documents\["&$B$2&"]"&$H$1&"'!d"&ROW()+2),0,ROWS($D4:D4))

Kindly amend your code

 

[Toadstool]

Does this do what you ask?

=INDEX(INDIRECT("'C:\My Documents\["&$B$2&"]"&$H$1&"'!d"&COLUMN()+1),0,ROWS($D4:D4))

 

[howard]

thanks very much for your help. Code works perfectly

 

[Toadstool]

You're welcome!
Thanks for the feedback.