Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show Cell ref of cell in another cell (2000 )

    Lets say cell A1has =B3. And B3 has a text value of W.
    I would like to put a formula in C3 that shows me that A1 is based on the value of B3
    So the result would be B3 is W, A1 returns the value W and C3 returns the value A1

    Thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    I think you can do this with the PRECEDENTS property, though I haven't been able to get it to work while a function is running (so it is difficult to use it in a function).

    Maybe someone else can steer me in the right direction. Is there something special about "precedents" that is does not work while functions are running??

    I was trying to create a function containing the line similar to:
    Range("a1").Precedents.Address(False, False)

    This will list all the addresses referenced in A1. The command works (as expected) in the immediate window when the function is NOT running, but when the function is running it only lists the cell itself ("A1") as a precedent, even in the immediate window.

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    Just as a consolation: I ran into the same problem.

    Added: it reminds me of the thread starting with <post#=205475>post 205475</post#>, where a user-defined function used cells in the current worksheet, even if a range in a different worksheet was passed to it.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    It is not a worksheet issue.

    If you enter into A1:
    +a2+a4

    Then go to VB and enter in immediate window (alt-f11 ctrl-g):
    ?Range("a1").Precedents.Address(false,false)
    And you will get (which is what I want and expect)
    A2,A4
    Now create the function (or any function):
    <pre>Function play()
    play = 5
    End Function
    </pre>


    and add a break point in it.

    Go into the worksheet that you entered the formula in A1 and in some cell (it doesn't matter), enter:
    =play()
    The function will STOP at the breakpoint.
    Go to the immediate window and enter:
    ?Range("a1").Precedents.Address(false,false)
    and you will get (NOT what I expect):
    A1

    Without going back to the spreadsheet, hit the stop button in VB (to get out of the function) and then once again enter in immediate window:
    ?Range("a1").Precedents.Address(false,false)
    And you will get (which is what I want and expect)
    A2,A4

    The precedent property does NOT work while a FUNCTION is running, but I don't understand why NOT: it does NOT even have to be PART of the function itself. You can get the value of the correct cell and other properties but NOT the precedents.

    I am SURE that is some kind of documented "feature" [read with "hint" of sarcasm], with a logical reason, but I just don't get it.

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    The User Defined Function below should give you what you want:

    <pre>Public Function GetPrecedents(oCell As Range) As String
    GetPrecedents = oCell.Precedents.Address(False, False)
    End Function
    </pre>

    Legare Coleman

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    What version of XL are you running. It works fine for me in XL2K.
    Legare Coleman

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    Legare,

    I am using Excel 97 SR2. Your function works OK when run from the immediate window: if A2 contains the formula =A1, running ? GetPrecedents([A2]) returns A1, as it should be. If A2 doesn't contain a formula, it raises error 1004 - no cells found.
    But entering the formula =GetPrecedents(A2) always returns A2, whatever the contents of A2 - it can be empty, or a constant, or a formula, the result is A2.

    If it works OK for you in Excel 2000, it looks like this was a bug in Excel 97 that has been fixed in Excel 2000. So Hetty's problem has been solved (2000 is mentioned in the subject).

  8. #8
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    Hi Legare

    What this shows me is precedents. I am more looking at dependents. Perhaps I did not explain it very well at the beginning. So I have attached a spreadsheet giving an example. Apologies for the childlike colouring on the spreadsheet

    Hetty
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    If you replace Precedents by (surprise, surprise) Dependents in the macro, it should do what you want.<pre>Public Function GetDependents(oCell As Range) As String
    GetDependents = oCell.Dependents.Address(False, False)
    End Function</pre>

    Of course, you must then put the formula =GetDependents(E3) in E4 etc.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    If your precedents/dependents have precedents/dependents and you ONLY want the DIRECT references, then use
    directprecedents or directdependents for the properties.

    [You still might want to test the code, to ensure that are some]

    Steve

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    <P ID="edit" class=small>(Edited by sdckapr on 05-Feb-03 12:02. OOPS, EXIT function (not END function). That's what I get for not checking. FIXED in code)</P>This does NOT work in XL97
    This is what I tried to do (and failed) in XL97.

    Must be an XL97 bug (or Feature?).

    The code might need to check for no precedents (I can't test):
    <pre>Public Function GetPrecedents(oCell As Range) As String
    If oCell.Precedents.Count = 0 Then
    GetPrecedents = "No Precedents"
    Exit Function
    End If
    GetPrecedents = oCell.Precedents.Address(False, False)
    End Function
    </pre>


    Steve

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    Good point, Steve, but just like you, I can't test it here <img src=/S/sad.gif border=0 alt=sad width=15 height=15>

  13. #13
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    Unfortunately, that does not seem to work in XL2K. If I have a worksheet which contains:

    <pre>A1: =B3
    B3: W
    C3: =GetDependents(B3)
    </pre>


    Then C3 displays "B3" where is should display "A1,C3". If I use ?ActiveSheet.Range("B3").Address(False,False) in the immediate window when the function is not active, it returns "A1,C3" as it should. This looks like another bug.
    Legare Coleman

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    This is getting stranger and stranger. In Excel 97, Dependents, Precedents, DirectDependents and DirectPrecedents don't work correctly in a worksheet function, although they do return correct results in the Immediate window. From what you describe, it would seem that Precedents works OK in a worksheet function in Excel 2000, but Dependents doesn't. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> How about DirectPrecedents and DirectDependents?

  15. #15
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    London, United Kingdom
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show Cell ref of cell in another cell (2000 )

    <P ID="edit" class=small>(Edited by Hetty on 05-Feb-03 17:23. Just got post from Legare)</P>I am definitely doing something wrong

    "Of course, you must then put the formula =GetDependents(E3) in E4 etc"
    Followed the instructs as a test
    And got E3 <img src=/S/confused.gif border=0 alt=confused width=15 height=20> I was expecting A3

    Just got a post from Legare -
    I think I am going to have to enter the info manually . Pass the Port (sorry, don't like beer)
    Thanks to everyone for racking their brains for me.

Page 1 of 2 12 LastLast

Posting Permissions

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