spreadsheet Links

[Peter100]

I have a workbook named Master.xls which is a master template.
I have workbooks for each month named JanData.xls - Dec.xls

I want to link the data from cell A1 in JanData.xls into cell A1 of
Master.xls which is simple enough.

The link would look like ='[JanData.xls]Sheet1'!$A$1

But what I want to be able to do is replace the [JanData.xls] part of the link
with a cell reference in Master.xls which would be a text entry so that the link
could be changed to read from FebData.xls - MarData.xls etc as desired.

Anyone any Ideas ?

 

[Chris Davison]

Interesting one Yogi, my reading was that he actually wanted the data bringing back though based on what month reference was inputted somewhere in a sheet :

"What I want to do is bring in the relevant months data into all 31 sheets by simply changing the text entry from JanData.xls to FebData.xls"

My ugly method achieves it, but I like your lateral thinking : I don't like indirect needing open workbooks, that bothers me for some reason

cheers
Chris

 

[Peter100]

Hi Yogi

could you show me how you would enter the sheet and Cell reference in C1 of your example

Thanks

 

[Peter100]

Hi Chris
YES your understanding of what I want is correct.

I will try your method over the next twenty four hours.

Keep an eye open for my response

Many thanks

Peter

 

[Chris Davison]

Peter,

I took the liberty of assuming you'd just want to type Jan or Feb as the pointer rather than the full filename (as it's just easier to type....)

if that's a problem, you should be able to amend the formula appropriately, but if can't, shout at me and I'll do it properly

:wink:

 

[Yogi Anand]

On 2002-05-11 06:05, Peter100 wrote:
Hi Yogi

could you show me how you would enter the sheet and Cell reference in C1 of your example

Thanks

Hi Peter:
The way I used the HYPERLINK function is as follows:
What I want to get as an argument to the HYPERLINK function is the following:
"[c:FoldersubFolderFileName.xls]SheetName!CellRef]"

So, I broke this argument down into thre components ...

in cell A1 ... [c:FoldersubFolder
in cell B1 ... FileName.xls]
in cell C1 ... SheetName!CellRef

then I rebuilt my formula as

=HYPERLINK(A1&B1&C1)

since the file path, and file name will generally remain the same, now I can just change my SheetName!CellRef
and the Hyperlink is changed accordingly.

Please post back if it works for you ... otherwise explain a little further and let us take it from there.

Regards!

 

[NateO]

If you want to experiment with a vba procedure, try something like the following. I've added some error testing procedures which have been commentized as if the workbooks that are linked are open, you won't necessarily see the .xls extension. It's a starting point:

Sub Links2()
'Macro recorded by Nate
Dim ws As Worksheet, cl As Range, frm As String
Dim FrmCls As Range, p As String, o As String
p = InputBox("File name to replace")
'If Right(p, 3)<> "xls"
'Then MsgBox "Forgot the extension"
'end
'end if
o = InputBox("New Filename")
'If Right(o, 3)<> "xls"
'Then MsgBox "Forgot the extension"
'end
'end if
For Each ws In ActiveWorkbook.Worksheets
    On Error GoTo 1
    Set FrmCls = ws.Range("a1").SpecialCells(xlFormulas)
    For Each cl In FrmCls
        frm = cl.Formula
        If InStr(frm, "[") Then
            cl.Replace What:=p & "]", Replacement:=o & "]", _
            MatchCase:=False
        End If
    Next cl
1:
    Set FrmCls = Nothing
    Next ws
End Sub

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-11 12:31

 

[Peter100]

CHRIS
Great that works a treat, except it is copying absolute rather than relatively.

When copying down the rows, it is alright copying across the columns.
Go on "make my day" and tell me how
This message was edited by Peter100 on 2002-05-13 02:32
This message was edited by Peter100 on 2002-05-13 02:41

 

[Legacy 98055]

This might be a useful macro as well...
Assuming the filename without the extension is in A1.<pre>

Sub UpdateLink()
'Before the first run, enter the current filname(without extension)
'into cell A1 Ex. JanData or FebData, ect...
Dim sh As Worksheet, NewLink
NewLink = InputBox("Enter the name of the file to link to your " & _
"workbook. Do not enter the period or file extension. " & _
"For example: You might enter FebData to change all of your " & _
"current links to FebData.xls")
If NewLink = "" Then Exit Sub
For Each sh In Worksheets
sh.Cells.Replace What:=Cells(1, 1), Replacement:=NewLink, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Next
Cells(1, 1) = NewLink
End Sub</pre>

Tom
This message was edited by TsTom on 2002-05-13 03:03

 

[Peter100]

HI Tom

I'm pondering what youv'e said, and will come back on it.
Chris's way is doing just what I want if only I can get it to copy relatively down the rows.

 

[Legacy 98055]

Did you remove the $ if any?
Tom