I have a fairly basic Excel knowledge and am trying to make some of my regular accounting spreadsheets a little more automated than they are at present.
Problem 1:
I have a reference to a cell in a worksheet in another Excel file as follows:
These files are on a USB pen drive so I want to make the drive letter dynamic using LEFT(CELL("filename",A1),1)
However the resultant formula does not work:
I've tried various permutations of quotes, double quotes etc but no joy. Anyone spot the error?
Problem 2:
I create new copies of each Excel file at the start of each week. The file names consist of the date in US format (so it is sortable) plus the name of the file e.g. as above "2008-02-01 Expenses.xls".
Each week I have to manually update the various links so that they point to the new versions of the files. What I'd like is to make the link dynamically obtain the newest file name. So next week for example the link would change to point to the cell in "2008-02-08 Expenses.xls".
Is there a fairly simple way to achieve this?
Problem 1:
I have a reference to a cell in a worksheet in another Excel file as follows:
Code:
='E:\Expenses\[2008-02-01 - Expenses.xls]Invoice #17 - 8th Feb 2008'!$D$23
However the resultant formula does not work:
Code:
=LEFT(CELL("filename",A1),1)&':\Expenses\[2008-02-01 - Expenses.xls]Invoice #17 - 8th Feb 2008'!$D$23
Problem 2:
I create new copies of each Excel file at the start of each week. The file names consist of the date in US format (so it is sortable) plus the name of the file e.g. as above "2008-02-01 Expenses.xls".
Each week I have to manually update the various links so that they point to the new versions of the files. What I'd like is to make the link dynamically obtain the newest file name. So next week for example the link would change to point to the cell in "2008-02-08 Expenses.xls".
Is there a fairly simple way to achieve this?
Comment