Thanks Thanks:  0
Likes Likes:  0
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 33

Thread: spreadsheet Links

  1. #21
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Tom

    going back to your first comment, my problem is in creating the link in the first place not changing the reference.

    re your recent comment; there are not any $ references relevant to the data reading range.

    Please have a look at Chris's formula
    =INDIRECT("["&$A$1&"Data.xls]Sheet1!"&ADDRESS(1,COLUMN(A:A),4,1))
    and comment if you can

  2. #22
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I can't comment on the formula. I know very little about functions and formulas just yet.
    Sorry.
    I was under the impression that your links were already setup and you wanted an easy way to change them for a new month???
    If your entire master workbook is refering to FebData.xls then the macro would go through every sheet and replace Feb with Mar.
    Tom

  3. #23
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry Peter,

    I overlooked the "1" that was anchoring the row number

    try :

    =INDIRECT("["&$A$1&"Data.xls]Sheet1!"&ADDRESS(row(),COLUMN(A:A),4,1))



    _________________
    Hope this helps,
    Chris
    (Excel '97, Windows ME)

    Edit : I should add that there's a couple of volatile fucntions there which may slow your workbook down and swell its size detrimentally. Please try those VBA solutions which may well be more desireable.

    [ This Message was edited by: Chris Davison on 2002-05-13 06:06 ]

  4. #24
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    CHRIS YOU ARE A GENIUS

    Haven't encountered the problems you mentioed yet but will bear it in mind.

    If you are still game, how about really making it rock & roll.

    Assuming cell A1 contains "Jan" (no quotes) and cell A1 has data validation selecting from a list of Jan Feb Mar etc.

    how can we make the workbook upon being opened automatically open xxx.data.xls where xxx is the text in Sheet 1, cell A1. Then when the value of A1 is changed open the relevant xxx.data.xls workbook.


  5. #25
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-13 07:08, Peter100 wrote:
    CHRIS YOU ARE A GENIUS

    Haven't encountered the problems you mentioed yet but will bear it in mind.

    If you are still game, how about really making it rock & roll.

    Assuming cell A1 contains "Jan" (no quotes) and cell A1 has data validation selecting from a list of Jan Feb Mar etc.

    how can we make the workbook upon being opened automatically open xxx.data.xls where xxx is the text in Sheet 1, cell A1. Then when the value of A1 is changed open the relevant xxx.data.xls workbook.

    ahh, yes, ermmm, physically opening a file based on a cell content needs some VBA coding
    , and this means a different type of genius....

    Maybe Tom can help if he's keeping an eye on this thread....



    (I see why you ask though, INDIRECT won't work with closed workbooks...)



    _________________
    Hope this helps,
    Chris
    (Excel '97, Windows ME)


    edit : you could pop a hyperlink next to the dropdown cell if you want.... in which case, Yogi's earlier suggestions would work perfectly.... have a play around and see what you can come up with

    [ This Message was edited by: Chris Davison on 2002-05-13 08:05 ]

  6. #26
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can make Chris' formula work with closed workbooks by supplying a filepath:

    =INDIRECT("'c:temp["&$A$1&"Data.xls]Sheet1'!"&ADDRESS(1,COLUMN(A:A),4,1))

    Which isn't a bad idea.

    In Tom's absence, I'll give you an event procedure to open the file, where B1 holds one of your formulae. Change all [B1] instances to an address that holds the formula if necessary (e.g., [b1] to [c4])

    Right-click on the sheet in question, click 'view code' and paste the following code.


    Private Sub Worksheet_Change(ByVal Target As Range)
    'macro recorded by Nate
    Dim n As Integer, p As String, o As Integer, s As Integer
    Dim z As String
    If Target.Address = "$A$1" Then
    n = InStr([b1].Formula, ":")
    p = Mid([b1].Formula, n - 1, 1)
    o = InStr([b1].Formula, "[")
    s = InStr([b1].Formula, "]")
    ChDrive (p)
    ChDir Mid([b1].Formula, n - 1, (o - (n - 1)))
    z = Application.Substitute(Mid([b1].Formula, _
    o + 1, s - 1 - o), ".xls", "") & ".xls"
    Workbooks.Open [a1].Value & Application.Substitute(z, _
    """&$A$1&""", "")
    End If
    End Sub


     

    You should be set.

    Chris get's the MVP nod. Nice work , notice a few got called up.


    Edit: When I post with BB, $A$1 is being truncated to $A erroneously. Having [big] trouble with BB as of late.
    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-13 10:51 ]

  7. #27
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-13 09:30, NateO wrote:
    You can make Chris' formula work with closed workbooks by supplying a filepath:

    =INDIRECT("c:temp 2002-05-13 09:34 ]
    interesting ! I shall have a tinker with this toute de suite

    thanks Nate

  8. #28
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You'll need to insert single ""'s where appropriate. They got crushed during my edit.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-13 09:42 ]

  9. #29
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Alrighty. The indirect doesn't like the path (doesn't mind it if the file's open, but isn't responsive when closed, defeating the purpose), which the code above is based on, so I'm going to rephrase this.

    If you use an indirect formula method, the target file has to be open. To open the file with a vba procedure, use the following code with Chris' formula. Right-click on the sheet with your Data Validation list, click 'view code' and paste the following code:


    Private Sub Worksheet_Change(ByVal Target As Range)
    'Macro recorded by Nate
    Dim o As Integer, s As Integer
    Dim z As String
    If Target.Address = "$A$1" Then
    o = InStr([b1].Formula, "[")
    s = InStr([b1].Formula, "]")
    ChDrive ("C") 'hardcode your target drive letter
    ChDir ("C:tempxltest") 'hardcode your target file path
    z = Application.Substitute(Mid([b1].Formula, _
    o + 1, s - 1 - o), ".xls", "") & ".xls"
    Workbooks.Open [a1].Value & Application.Substitute(z, _
    """&$A$1&""", "")
    End If
    End Sub


    However, you can set this up with a pure vba solution that will allow you to update your information without having to open the target workbook.

    Setting up the link is the easy part. Open your master file and one of the monthly files (e.g., JanData.xls). Now in the master click +, then move to the JanData.xls, click on the cell of choice file and press enter. You've now established your link. Remove the $ signs to remove absolute addressing.

    Now, if you right click on the sheet with your validation list, click view code, paste the following code in:


    Private Sub Worksheet_Change(ByVal Target As Range)
    'Macro recorded by Nate
    Dim Worksheet As Worksheet, cl As Range, frm As String
    Dim z As String, o As Integer, s As Integer, FrmCls As Range
    If Target.Address = "$A$1" Then
    For Each Worksheet In ThisWorkbook.Worksheets
    Set FrmCls = Nothing
    On Error Resume Next
    Set FrmCls = Worksheet.[a1].SpecialCells(xlFormulas)
    If FrmCls Is Nothing Then GoTo 1
    For Each cl In FrmCls
    frm = cl.Formula
    If InStr(frm, "[") Then
    o = InStr(cl.Formula, "[")
    s = InStr(cl.Formula, "]")
    z = Mid(cl.Formula, o + 1, s - o)
    cl.Replace What:=z, Replacement:=[a1].Value & "Data.xls]", _
    MatchCase:=False, lookat:=xlPart
    End If
    Next cl
    1:
    Next Worksheet
    End If
    End Sub


    If you change Jan to Feb in cell a1, every link your workbook that had JanData.xls will be updated to FebData.xls

    However you want to work it, I think the 2nd's a little easier to handle from an end-user perspective.

    Hope this helps.

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-13 14:21 ]

  10. #30
    Board Regular
    Join Date
    Apr 2002
    Posts
    765
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry Nate

    Can't seem to make that one work.

    Chris, any success ?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •