=('W:\Materijalno\PAPIR\2025\FLUTING\[FLUTING.xlsm]ZBIRNA'!$Y$28)

This is a link in my Reportbook to some other workbook that pulls data to my Reportbook. Note the folder 2025 in the link which represents year. There are obviously many year folders with same structured files. So in the Reportbook I have cell F4 containing desired year, based on which folder would data be pulled from! Something like this (doesn't work) I would like:

=('W:\Materijalno\PAPIR\& F4 &\FLUTING\[FLUTING.xlsm]ZBIRNA'!$Y$28)

=('W:\Materijalno\PAPIR\2025\FLUTING\[FLUTING.xlsm]ZBIRNA'!$Y$28)

This is a link in my Reportbook to some other workbook that pulls data to my Reportbook. Note the folder 2025 in the link which represents year. There are obviously many year folders with same structured files. So in the Reportbook I have cell F4 containing desired year, based on which folder would data be pulled from! Something like this (doesn't work) I would like:

=('W:\Materijalno\PAPIR\& F4 &\FLUTING\[FLUTING.xlsm]ZBIRNA'!$Y$28)

• excel
• excel-formula

• 3 INDIRECT would normally be the approach, except that it requires the target file to be open. – BigBen Commented Mar 17 at 14:30
• 4 Using INDIRECT would be the only way with formula, but it would require that the target workbook to be open for it to work. Otherwise you will need code in a Worksheet_change event, that will change the connection, open and close the workbook. – Scott Craner Commented Mar 17 at 14:30

1 Answer 1

\PAPIR\' & cstr(F4) & '\FLUTING\[FLUTING.xlsm]ZBIRNA'!$Y$28)

1. Put the ' sign before and after the variable

2. Convert the number to string by formul CStr in VBA