Who's good at Excel
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 :) |
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
|
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 |
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 ;) |
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 |
All times are GMT +1. The time now is 10:19 PM. |
© 2024 MH Sub I, LLC dba Internet Brands