Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: How to show which worksheet a cell is linked to? Return Valu

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I have a spreadsheet with a summary page with a list of values that are links to various values on different sheets. Is there an excel formula or macro that I can use to list out which sheets the list of values are linked to. I want it to look like this

    Column A Column B
    3.45 Sheet 1
    6.54 Sheet 2
    1.23 Sheet 4
    8.96 Sheet 9
    4.54 Sheet 2
    3.64 Sheet 3
    3.33 Sheet 1

    So, is there an Excel formula or Macro that can accomplish this?

    While on the subject, is there anyway to make a macro/formula to look up a specific cell on a specific sheet. E.g,

    Column A Column B
    Sheet 1 D4

    Is there a formula or macro that I can use so that the value in D4 on Sheet 1 in my workbook is put into Column C? How about a link to ‘Sheet 1’D4? That would be helpful.

    THANKS!!

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Got this code from NateO:

    Sub showRng()
    Dim cl As Range
    Set cl = ActiveCell
    Application.ScreenUpdating = False
    For Each cell In Selection
    ActiveCell.Offset(0, 1).Value = Mid(ActiveCell.Formula, 2, 255)
    ActiveCell.Offset(1, 0).Select
    Next
    cl.Select
    Application.ScreenUpdating = True
    End Sub

    It copies the formula in Column A, copies everything but the = (i.e.,the first character) and pastes it into the next column. So while it does show which sheet it came from, it also shows which cell. This'll work for me and probable for someone else, but I was hoping to only have the sheet name.

    Consolidating all the info from different sheet into one sheet and then having a pivot table would work elegantly but I need to use a macro for my case.

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    Instead of NateO's following line:

    ActiveCell.Offset(0, 1).Value = Mid(ActiveCell.Formula, 2, 255)

    You might want to try this:

    ActiveCell.Offset(0, 1).Value = Mid(ActiveCell.Formula, 2, InStr(Range("K11").Formula, "!") - 2)


  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Wellington
    Posts
    115
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry again, should have check I am sending:

    ActiveCell.Offset(0, 1).Value = Mid(ActiveCell.Formula, 2, InStr(activecell.Formula, "!") - 2)

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    BabyTiger,

    Once again you've come through! THANKS!

    I had tried this,

    ActiveCell.Offset(0, 1).Formula = "=MID("&ActiveCell&",1,FIND(" & """"&"!"&""""&","&ActiveCell&",1)-1)"

    to follow an example from the Excel Help file on using the MID, but again I think I screwed up on using the cursed "s. Or maybe the FIND formula doesn't work in VBA. Oh, well.. so much for trying to learn by trial and error.

    Thanks!

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
  •