Drag formula down and change only certain values

[Jose Bug]

Hi smart peoples,
I have the following formula and want to be able to drag it down and have it change the 'K17' to 'K18' (two locations within formula, shown in bold) and the 'WP1-0003' (two locations within formula, shown in bold) to '0004' and so on and so on through to '5000'... I want all other values within formula to remain the same..
All/ any ideas/ suggestions appreciate.

=IF(K17="green tag",'C:\Users\BUDJO\Desktop\WP1 Scaffold Gear List\[WP1-0003.xlsx]Gearlist'!$S$98,IF(K17="red tag",'C:\Users\BUDJO\Desktop\WP1 Scaffold Gear List\[WP1-0003.xlsx]Gearlist'!$S$98,""))

[Pete_UK]

You won't be able to do that using a formula.

To change the 0003 to 0004 within the filename and have Excel recognise it as a valid cell reference, you would have to use the INDIRECT function (in conjunctions with ROWS() ). However, this only works on external files that are open in the same instance of Excel (in which case you would not need to include the full path), and as you want to go up 5000 I can't see you wanting to open 5000 files.

Hope this helps.

Pete

[Jose Bug]

So by ripping apart the text string and then reassembling I was able to come up with the formulas I want:
=IF(K17="green tag",'C:\Users\BUDJO\Desktop\WP1 Scaffold Gear List\[WP1-0003.xlsx]Gearlist'!$S$98,IF(K17="red tag",'C:\Users\BUDJO\Desktop\WP1 Scaffold Gear List\[WP1-0003.xlsx]Gearlist'!$S$98,""))
=IF(K18="green tag",'C:\Users\BUDJO\Desktop\WP1 Scaffold Gear List\[WP1-0004.xlsx]Gearlist'!$S$98,IF(K18="red tag",'C:\Users\BUDJO\Desktop\WP1 Scaffold Gear List\[WP1-0004.xlsx]Gearlist'!$S$98,""))
=IF(K19="green tag",'C:\Users\BUDJO\Desktop\WP1 Scaffold Gear List\[WP1-0005.xlsx]Gearlist'!$S$98,IF(K19="red tag",'C:\Users\BUDJO\Desktop\WP1 Scaffold Gear List\[WP1-0005.xlsx]Gearlist'!$S$98,""))
=IF(K20="green tag",'C:\Users\BUDJO\Desktop\WP1 Scaffold Gear List\[WP1-0006.xlsx]Gearlist'!$S$98,IF(K20="red tag",'C:\Users\BUDJO\Desktop\WP1 Scaffold Gear List\[WP1-0006.xlsx]Gearlist'!$S$98,""))
etc etc

But for reasons beyond me it does not treat this as a formula..
So I have copy pasted into notepad, then copy pasted back into relevant cells in excel.. seems to be working