Drag formula down and +1 to a value mid formula

[Jose Bug]

I have a formula that links to a cell in another work book.
I am searching for a solution to drag that formula down and add the new number of the linked work book.

='S:\FOLDER LOCATION\FOLDER LOCATION\[2231.xlsm]Modular'!$S$97

Want to be able to drag the above formula down so I get the below example and still have the linking formula work:
='S:\FOLDER LOCATION\FOLDER LOCATION\[2231.xlsm]Modular'!$S$97
='S:\FOLDER LOCATION\FOLDER LOCATION\[2232.xlsm]Modular'!$S$97
='S:\FOLDER LOCATION\FOLDER LOCATION\[2233.xlsm]Modular'!$S$97
='S:\FOLDER LOCATION\FOLDER LOCATION\[2234.xlsm]Modular'!$S$97
='S:\FOLDER LOCATION\FOLDER LOCATION\[2235.xlsm]Modular'!$S$97

Any help is greatly appreciated

[Croweater]

Not sure if this will work for you, but you could try using the row number

="'S:\FOLDER LOCATION\FOLDER LOCATION\[223" & ROW() & ".xlsm]Modular'"&"!$S$97"

[Jose Bug]

Hi,
Thanks for the reply.
So that works for changing the work sheet numbers for sure.. but excel does not seem to recognise the output as a formula, just text..

[Glenn Kennedy]

You need to use INDIRECT, a volatile function... but when you have to, you have to.

=INDIRECT("'S:\FOLDER LOCATION\FOLDER LOCATION\[22"&ROWS($1:31)&".xlsm]Modular'!S97")