Increment a filename

[alistew]

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

 

[Pete_UK]

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

 

[Gary''s Student]

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.

 

[alistew]

thats perfect.

thanks so much

 

[katagrga]

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.