Any Excel experts out there?

[gadgetman]

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:

='E:\Expenses\[2008-02-01 - Expenses.xls]Invoice #17 - 8th Feb 2008'!$D$23

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:

=LEFT(CELL("filename",A1),1)&':\Expenses\[2008-02-01 - Expenses.xls]Invoice #17 - 8th Feb 2008'!$D$23

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?

[MrRobin]

For problem 1, just use the INDIRECT function

e.g.

=INDIRECT("'"&A1&":\Expenses\[2008-02-01 - Expenses.xls]Invoice #17 - 8th Feb 2008'!$D$23)

And have the drive letter in cell A1. You must have the sheet you are referencing open though for this to work.

For problem 2, you can use the same approach as above, but in cell A2, say, you can just write in "2008-02-08" or whatever, or you can construct a formula that automates this depending on what the date is today. Then ammend the formula above to include cell A2 instead of the hard written [2008-02-01 - Expenses.xls]

[gadgetman]

I tried your solution but still can't get it to work.

I simplified it right back and created E:\book1.xls and E:\book2.xls

In A7 of book2.xls I placed the value 64.
In A1 of book1.xls I place the letter E.

In A10 of book1.xls I typed this:

='E:\[Book2.xls]Sheet1'!$A$7

this returned 64.

In A11 of book1.xls, following your example I typed:

=INDIRECT("'"&A1&":\[Book2.xls]Sheet1'!$A$7)

I get 'your formula contains an error'.

Both sheets are open.

Any ideas?

[MrRobin]

You're missing a " at the end of your formula, before the closing bracket.

Personally, I keep all my invoices on one workbook, just a new sheet per invoice. Makes keeping a summary page a bit easier!

[xoggoth]

For accounts purposes, not actual printing of invoice, what's wrong with one income sheet, one invoice to a line? Surely you don't have a separate sheet for every expense item?

[gadgetman]

Excellent it works now, thanks.

I keep the following spreadsheets:

1. Journey log - 1 row per journey. This tracks whether I have gone over the 10000 threshold and automatically calculates my monthly mileage claim @ 25p or 40p per mile.
2. Expenses list - 1 row per expense item (typically subsistence not paid directly from the company bank account).
3. Timesheet - 1 row per billable day.
4. Budget - all of my payment/budget calculations for the invoice period.

Each sheet contains separate worksheets (tabs), 1 per invoice period (typically a month).

The monthly figure from the journey log feeds into the expenses sheet and the timesheet and expenses files feed into the budget sheet which calculates total income, FRS VAT, potential dividend, VAT, corp and personal tax provisions.

I also use the budget sheet to track cumulative corporation and personal tax provision, VAT etc and I also keep an offline version of both company bank accounts in it.

Each week I take a copy of all 4 sheets and update the date on the filename (for backup purposes), hence my original request. I have an automated batch file for this which also creates quick launch links to each of the current files.

I don't see how I could achieve the above in a single sheet but I'm open to suggestions.