msp1

Here's a tester for you. I have an Excel sheet with the following data
in one of the cells. I would like to fill the series so that it
increments the file path by 1. ie 060204 in the path will become
070204 in the next cell and so on.

value in cell 1

='P:\Shift reports\Line 1\Finishing\TEP Reports\FEB
04\[060204.xls]Data input'!$J$6


Value in cell 2

='P:\Shift reports\Line 1\Finishing\TEP Reports\FEB
04\[070204.xls]Data input'!$J$6

and so on

='P:\Shift reports\Line 1\Finishing\TEP Reports\FEB
04\[080204.xls]Data input'!$J$6

Sadly it's beyond my IT department so any solutions would be greatly appreciated as it's going to save me hours every month.

Cheers
Mike

TopBanana

Try having a hidden column with just the date in it, then using the FORMAT and CONCATENATE functions to format the date as ddmmyy and to concatenate that date with the rest of your path string respectively

markr1963

Agree with jlanng except if you leave the hidden column formatted as a date then the CONCATENATE function uses Excel's date serial number instead. So 010204 becomes 38018 as a date serial number. You can get aorund this by adding another column that uses the text function.
If A1 is 010204 as date then B1 could be =TEXT(A1,"ddmmyy") and returns the date in A1 as a text string.
Then in C1: =CONCATENATE("='P:\Shift reports\Line 1\Finishing\TEP Reports\FEB 04\[",B1,".xls]Data input'!$J$6")
Hide columns A and B

HTH
Mark

msp1

Thanks for the help guys. Tried it at home and seems to work. Will put it to the test tomorrow at work and report back.

Mike

msp1

Hi Mark

Thought that was going to work but unfortunately not. Using the concentate function gives me ='P:\Shift reports\Line 1\Finishing\TEP Reports\FEB 04\[020304.xls]'Data input'!$J$6

but not the actual vale of the cell being referenced.

Any ideas

Mike