Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment a filename
Hi
I have a spreadsheet which links to information in another spreadsheet. example ='c:\production\reports\jan\[jan 010107.xls]production'!$a$1 The column is filled using the same formula but different spreadsheet filename. Each cell is a different date filename. example ='c:\production\reports\jan\[jan 020107.xls]production'!$a$1 example ='c:\production\reports\jan\[jan 030107.xls]production'!$a$1 etc.... until the end of the month Is there a way i can use the autofill and increment the filename instead of manually changing the date for each day? I have to do this for every month. At the moment i autofill the formula and go back and change the date. Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment a filename
If the other file is not open then you won't be able to use INDIRECT.
A slightly quicker way of doing it than what you are doing at the moment is to highlight the cells with the formulae in and do Find & Replace (CTRL-H): Find what: jan 020107 Replace with: jan 030107 Then click Replace All. Adjust the dates as appropriate. Hope this helps. Pete On May 18, 10:10 am, alistew wrote: Hi I have a spreadsheet which links to information in another spreadsheet. example ='c:\production\reports\jan\[jan 010107.xls]production'!$a$1 The column is filled using the same formula but different spreadsheet filename. Each cell is a different date filename. example ='c:\production\reports\jan\[jan 020107.xls]production'!$a$1 example ='c:\production\reports\jan\[jan 030107.xls]production'!$a$1 etc.... until the end of the month Is there a way i can use the autofill and increment the filename instead of manually changing the date for each day? I have to do this for every month. At the moment i autofill the formula and go back and change the date. Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment a filename
Something like:
=INDIRECT("'C:\[Book" & ROW() & ".xls]Sheet1'!$C$20") in row 1 will pickup Book1 in row 2 will pickup Book2, etc. This is a method to increment either a filename or directory name as you copy down the column. -- Gary''s Student - gsnu200722 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment a filename
thats perfect.
thanks so much "Gary''s Student" wrote: Something like: =INDIRECT("'C:\[Book" & ROW() & ".xls]Sheet1'!$C$20") in row 1 will pickup Book1 in row 2 will pickup Book2, etc. This is a method to increment either a filename or directory name as you copy down the column. -- Gary''s Student - gsnu200722 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increment a filename
I found this very interesting, as I did not think it possible. I copy and
edit links daily to change the file name. I was not able to figure out how to make this formula work. I would really appreciate your assistance. My example is: ='\\Algfilesrv\users\Accounts Receivable\SHARE\09-10 FY\MISC\[AR Backlog Report - JULY 10, 2009.xls]AR Backlog Report'!$D$45 The only thing that changes is the date ='\\Algfilesrv\users\Accounts Receivable\SHARE\09-10 FY\MISC\[AR Backlog Report - JULY 11, 2009.xls]AR Backlog Report'!$D$45. I also have one that the tab name changes =JUL10!C$86 =JUL11!C$86 Please advise. "Gary''s Student" wrote: Something like: =INDIRECT("'C:\[Book" & ROW() & ".xls]Sheet1'!$C$20") in row 1 will pickup Book1 in row 2 will pickup Book2, etc. This is a method to increment either a filename or directory name as you copy down the column. -- Gary''s Student - gsnu200722 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell("filename") doesn't update to new filename when do save as. | Excel Worksheet Functions | |||
Increment Text | Excel Discussion (Misc queries) | |||
set filename to <filename-date on open | Excel Worksheet Functions | |||
increment a value by 1 | New Users to Excel | |||
need to increment value | Excel Discussion (Misc queries) |