Today's Excel Q

I have workbooks with filenames in reverse date format ie 2010-01-01.xlsx and so on. I need to add up values from the same cell in a series of them.

Is there an easy way to do this, or am I going to have to resort to faffing about with VBA iterating through dates, converting to strings and all that crap?

use INDIRECT to build the address from text values.

i.e. if you have:

year in column A, month in column B, day in column C, then in Column D (D2) paste:

=INDIRECT(“‘[“&A2&”-“&B2&”-“&C2&”.xls]Sheet1’!$A$1″ )

it will return the value in A1 on Sheet 1 in the book with the date specified.

NB if your date is 2010-01-02 use an apostophe in the cell to maintain the 0 i.e. ’01 in the month cell.

Thanks 🙂

All the workbooks involved have to be open tho according to the help…. Could cause a drain on my system, we’ll see 🙂

they only need to be open once.

Id open 10 at a time, then close and the next batch. Or write a VBA script if it’s `1000’s of files.

Import them all into access and do it there man

FFS!

I try typing in [c:\blah\book.xslx]Sheet1 into a cell and Excel decides to mangle it up with [c:\blah\[book1.xslx]book1.xslx]Sheet1]Sheet1] and then complains about it.

Well it wouldn’t be f*cking well wrong if you hadn’t f*cked about with it WOULD IT?!! 👿

use an apostrophe at the front perhaps to get it to treat it as text?

Gah.. right.. got it.. the pathname has to be outside the [] but the filename inside it – brilliant that 🙄

However, it still doesn’t work. I’ve got the syntax of the link working; I’ve got it to build that link as a string in a cell dynamically, but when I use INDIRECT it just says #REF! even though the syntax looks right.

That just does not bloody well work.

Linking between sheets is completely rubbish in Excel.

Distinctly unimpressed. How unreasonable a request is this? All I want to do is sum expense claims over a period, when each expense claim is a separate workbook…

How many different workbooks have you got?

I’m guessing that the number of files will change over a period. So rather than maintain a list in a worksheet to refer to each one I’d reserve a directory for these specific files. Then use vba to refer to this directory and design a loop to retrieve each file and grab the data. There is a FileOpen vba command (i’ll dig it out) you can use with a mask to ensure the file names are in the correct format and using that command will load the filenames found into an array for your vba code to process. This way you only have to place the files you want to include in the process in the directory and the vba code won’t care how many files there are and just process what it finds.

Bout 30.

And yes I could’ve opened each one and added it up with a calculator, but I shouldn’t have to.

use vba

Yeah, I was hoping to avoid this, cos I hate it. But you may be right. If the INDIRECT function wasn’t so crap I would not have to 🙁

molgrips – it honestly works perfectly well, you just need to be bang on with the syntax and address.*

use the =TEXT( xxxxxxx , )
function to test your address formula. Dont forget the “,” at the end. xxxxxxx is your adress forumla.

* or are you suffering from fat-finger syndrome? 😉

INDIRECT is a superb function when building master/slave workbook arrangements where the slave is based on a template. I wont have you slagging off darling little INDIRECT!

use something like this:

With Application.FileSearch
.NewSearch
.LookIn = “C:\My Expense Files”
.SearchSubFolders = False
.FileName = “nnnn-nn-nn”
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox “There were ” & .FoundFiles.Count & _
” file(s) found.”
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
****Open the file****
****Grab the data****
*** Close the file****

Next i
Else
MsgBox “There were no files found.”
End If
End With

Oooh, lots of help 🙂

I am sure I got the address syntax right – I broke it down and printed out the string so I know it’s being constructed correctly. I then pasted the string into a cell and it linked correctly (after asking me to point at the file in a file chooser despite me having specified an absolute path). However when I use indirect pointing at a cell that contains the exact same text, it dunna work.

However when I use indirect pointing at a cell that contains the exact same text, it dunna work.

Probably a wetware error 😉

Yeah thanks.. I’m well aware of the perils of syntax being a professional developer in proper languages (ie not this VBA crap 🙂 )

It’s the combination of INDIRECT plus linking to a different workbook that seems to be the issue. The help for INDIRECT clearly states that the other workbook has to be open otherwise you get !REF# but that doesn’t seem to work for me.

I’ve got it set up so I can create the date range with autofill and it pics it up from there, which is perfect.

Indirect works fine for me. Here’s a nice example of one which I use alot..

=INDIRECT(ADDRESS(MID(PERSONAL.xlsm!getformula(S203),FIND(
“$”,PERSONAL.xlsm!getformula(S203),FIND(“$”,PERSONAL.xlsm!getformula
(S203),1)+1)+1,99),1,1,,”S:\Plans\[developmentplan.xls]2010″))

I will try it with ADDRESS.

Doesn’t work.

This is my problem formula:

=INDIRECT(“‘C:\Users\Ben\Documents\Work\Expenses\IBM\[” & TEXT(YEAR(C4), “00”) & “-” & TEXT(MONTH(C4), “00”) & “-” & TEXT(DAY(C4), “00”) & “.xslx]Expenses’!AL37”)

Drop the filepath – the formula only wants the filename\sheet…?

use the text function to check your address but I think you’re missing an apostrophe at the front.

Still doesn’t work even if I use the file path. But the examples of workbook linking have the file path in just like that. And it works if I use that exact same syntax for explicit linking, just not with indirect.

And I want to use the absolute path since I don’t want to have to a) open each file or b) point to each one in a file browser.

The apostrophe’s not missing off the front. TEXT reveals a string that looks exactly like the one that, if I type it into a cell directly with = in front of it, works perfectly.

Indirect seems not to be working. The target cell is locked tho – that may be messing it up. Although like I say I can link to it directly.

molgrips – Im pretty certain you need to add an apostrophe to the front like: =INDIRECT(“‘”&…)

.xslx!

should be .xlsx

Also, files you are referencing need to be open for the INDIRECT to work.

.xslx!

❗

FFS!

Damn my XML trained fingers.

Interestingly, I made the same mistake on all my links, even the ones that work. At first it popped up a dialog box and seems to have cached that reference. So you can refer to a completely non-existent file without realising it.

Christ on a bike, MS.

Also, files you are referencing need to be open for the INDIRECT to work.

Yes, I got that – they were.

Shall we share the bag of toffees Stoner? 🙂

It works now.. *cough*

However.. I do wonder if there’s a way to copy the data from the target cell instead of just link to it…

It works now.. *cough*

However.. I do wonder if there’s a way to copy the data from the target cell instead of just link to it…

The simplest way i find to do this is record a macro that does what you want to do, then hack the resultant macro to do what you want it to do.

VBA is sometimes an easier way to solve a problem than functions.

OP sounds like he needs a database

I agree, I’d bash them all into access and then query

I do infact regularly with this code which sucks in all files in one directory provided they are of the same format:

Private Sub Command0_Click()

Dim InputDir, ImportFile As String, tblName As String

InputDir = “m:\data\imports\”

ImportFile = Dir(InputDir & “\*.csv”)

Do While Len(ImportFile) > 0
‘tblName = Left(ImportFile, (InStr(1, ImportFile, “.”) – 1)) ‘This is to import each file into single tables.
tblName = “TblDailyFiles” ‘This is to import all files into one table.

DoCmd.TransferText acImportDelim, , tblName, InputDir & ImportFile, True

ImportFile = Dir
Loop

I do not need a database for 30 numbers 🙂

Thanks for your help folks.