Help with links to other files

  • Thread starter Thread starter Jordon
  • Start date Start date
J

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
 
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
 
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.)
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
Click to expand...
 
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

Pete_UK said:
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

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
Click to expand...
Click to expand...
 
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

Dave said:
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.)
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
Click to expand...
Click to expand...
 
You're welcome, Jordon - thanks for feeding back.

Pete

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



Pete_UK said:
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")
Click to expand...

Hope this helps.
Pete
Click to expand...
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
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
It was just for you.

And thanks for the offer!
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

Dave said:
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.)
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top