MS Excel 2007

[bloggingjedi]

Hi


James here, this is my first post. Its been a few years since i have used ms excel, but i'm wanting to help a friend sort out his past dues.


My mate is a mechanic, he has last years worth of work that he did for his clients, one excel sheet per client. He wants to setup a master sheet that will look at all his files so that he can filter the information he needs.


I've used the ~if command so that it looks at file01.xlsx takes from Surname A2 field and shows it in the master file, file01 to file100 would be 100 clients. What i dont want to do is for him to have to write in every column the file name he has, just to put the file name in colum A$1$ down to 100 so that column b c d e f and g will pull in

that name that he has inputted there.


='[test1.xlsx]Office Input'!$B$6 is the formula

i tried ='[=A1]Office Input'!$B$6


As i said its been a few years since i played around with all of this, im working through a few dvds to relearn it all again.


ANY help appreciated.


James

 

[Luke M]

Initial thought is that you would want to use INDIRECT to build the reference, as incrementing the number in the file name would be a piece of cake then. Unfortunately, INDIRECT won't work on a closed external file (boo!).


With that in mind, the fastest way might be to create a short macro like this:

[pre]

Sub CreateLinks()
Dim LinkPrefix As String
Dim LinkSuffix As String

'Change as desired
LinkPrefix = "='[test"
LinkSuffix = ".xlsx]Office Input'!$B$6"

'Quickly builds 100 links
For i = 1 To 100
Cells(i, 1).Formula = LinkPrefix & i & LinkSuffix
Next i

End Sub

[/pre]
Playing around with the absolute/relative references in the formula, perhaps your friend can get the results he needs?