Help with links to other files

[Jordon]

I hope I can make this clear without being confusing.

I've set up payroll spreadsheets for our business that
include hours worked. There's one spreadsheet for each
week. Each spreadsheet is named for the date of that
payday (which is always on a Tuesday), in the following
format...

YYYY-MM-DD.xls (2009-10-27.xls)

In each workbook are sheets named for each employee.

I'm trying to get a recap of hours worked for any
one employee over any date range, as easily as
possible.

I know I can enter a couple of Tuesdays dates in
adjacent cells and drag to get a whole column of
dates. What I need help with is extracting from
those dates the names of the files.

Ultimately I'd like to have a template where I
can enter an employees name, create a column of
dates and have it display a column of hours worked
for all those dates.

For instance if I type the name Jordon in A1 and in
A2 is the date 10/27/2009 then in B2 the formula
would produce something like the following...

'N:\[2009-10-27.xls]Jordon'!$C$9

Any ideas?

TIA

--
Jordon

[Pete_UK]

Presumably you would like to fetch the data from C9 of that file. You
would normally use INDIRECT to do this, but that function will only
work if the "sending" file is open. However, there is a free add-in
morefunc that you can download (do a Google search for sites), and
this add-in has the function INDIRECT.EXT, which will work with closed
workbooks. You would use it like this:

=INDIRECT.EXT("'N:\["&TEXT(A2,"yyyy-mm-dd")&".xls]"&A1&"'!C9")

Hope this helps.

Pete

[Dave Peterson]

You're gonna have to use a macro.

If you were going to try to use a formula, you'd want to use the =indirect()
function and that means that the sending workbook has to be open.

I'd have a macro assigned to a button from the Forms toolbar that would populate
the cells with the formula I wanted.

Option Explicit
Sub testme()

Dim myNameCell As Range
Dim myPath As String
Dim wks As Worksheet
Dim myRng As Range
Dim myCell As Range
Dim myAddress As String

Set wks = ActiveSheet

myPath = "N:\" 'include the trailing backslash
myAddress = "C9"

With wks
Set myNameCell = .Range("A1")
Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))

For Each myCell In myRng.Cells
'trying for:
'='N:\[2009-10-27.xls]Jordon'!$C$9
myCell.Offset(0, 1).Formula _
= "='" & myPath & "[" _
& Format(myCell.Value, "yyyy-mm-dd") & ".xls" _
& "]" & myNameCell.Value & "'!" & myAddress
Next myCell
End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

--

Dave Peterson

[Jordon]

That worked perfect. Thanks for that. The only down side I
can think of is if someone else wanted to use it, in which
case I may have to go with Dave Peterson suggestion. It
may be easier than explaining to someone how to install
just about anything.

Thanks again.

--
Jordon

[Jordon]

That also worked perfect. Did you just type all that out
on the fly or did you modify an existing macro? If you
did it from scratch I am indebted to you. If you're ever
planning on being in the Seattle area shoot me an email.
I'll buy you a beer.

--
Jordon

> & "]"& myNameCell.Value& "'!"& myAddress

[Pete_UK]

You're welcome, Jordon - thanks for feeding back.

Pete

> >> Jordon- Hide quoted text -
>
> - Show quoted text -

[Dave Peterson]

It was just for you.

And thanks for the offer!

--

Dave Peterson