Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts

    Finding a cell . . .

    I'm not sure how to describe this, precisely, but I'll try my best.

    Cell A1 has a Name, and cell A2 has a number which is associated with A1.

    In another worksheet in the same workbook a cell, say K9, has the formula =Name, so it displays the contents of cell A1.

    I want to put in K10 the number associated with the Name that is referenced in K9, that is to say the value in A2.

    I had though it would be OFFSET(INDIRECT(K9),0,1), but clearly it isn't.

    What should the function I put into K10 be, please ?

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Martin,

    Could you please clarify.
    When you say Cell A1 has a "Name" do you mean a value like "Bruce" or does it have a Range Name like "MyName". NameEx.JPG

    If it is a range name the formula: =offset(MyName,1,0) will return the number.
    Last edited by RetiredGeek; 2012-02-03 at 19:26.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I knew it would be difficult for me to explain, and thanks for the reply.

    A1 has a Range Name. I know that the OFFSET formula will work but I want to extract MyName from K9 so that I can copy the formula elsewhere, and so it will still work when the formula in K9 changes.

    I hope that is clearer.

    PS I am wondering if it is possible to write a user-defined function, using cell.formula, stripping out the "=" at the beginning and then using the result in the OFFSET function as you described ?
    Last edited by MartinM; 2012-02-04 at 07:12.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Martin

    In cell [K10] you could use:
    =INDEX(Sheet1!B:B,MATCH(K9,Sheet1!A:A,FALSE))

    zeddy

  5. #5
    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
    Something like this?

    Code:
    Function ExtractName(rCell As Range)
      ExtractName = Mid(rCell.Cells(1).Formula, 2)
    End Function
    There is no checking to see if the formula starts with an equal or if it only contains a name, etc etc

    Steve

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks for the replies.

    Zeddy: I'm trying to avoid INDEX as, by the thousand, it seems as slow as VLOOKUP.

    Steve: I assume that if you leave out the number of characters in MID, it simply takes from character 2 to the end ?

  7. #7
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Martin

    I guess you mean avoid MATCH, as INDEX is very fast.
    There are ways to speedup MATCH and VLOOKUP by restricting the 'search range'.
    It all depends on what you are doing.

    Your assuption re: Steve's answer is correct.

    zeddy

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks everyone. This code did it beautifully, and simply:

    Code:
    Function Price(Asset As Range)
      Price = Range(Mid(Asset.Cells(1).Formula, 2)).Offset(0, 1).Value
    End Function
    It is very quick and the Workbook is both uncluttered and self-documenting.

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    No its doesn't work - properly.

    When I first enter the UDF in a cell, or many cells, the calculation works properly.

    I have an updating Macro, and after it has run every instance of the UDF returns #VALUE!

    The only way I can restore the cells is by re-entering each and every UDF formula. Other methods eg recalculate don't restore the proper cell values.

    I have debugged the Macro and narrowed the problem down to a single line:

    Code:
    Workbooks.Open Filename:=PriceFile
    This line "works" insofar as it opens the other Workbook correctly, but I don't understand why it has this disabling effect on my new UDF :-(

    Any ideas most welcome.
    Last edited by MartinM; 2012-02-04 at 14:07.

  10. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Martin

    Try including..
    Application.Volatile
    ..in the code in your UDF.

    Excel's OFFSET() is a volatile function (like RAND(), NOW() etc)
    (INDEX() became non-volatile in Excel 97 which is why it is fast)

    A Volatile Function in a formula in a cell makes that cell always recalculate at each recalculation even if it does not appear to have any changed precedents. You should avoid volatile functions wherever possible.

    zeddy

  11. The Following User Says Thank You to zeddy For This Useful Post:

    MartinM (2012-02-04)

  12. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Zeddy,

    The major re-write is complete and working as quickly as I had hoped - I knew about volatile functions but had no idea you could affect that behaviour in VB. Neither did I expect that not sorting that out would lead to complete failure.

    Your help is much appreciated.

    Martin

  13. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Your mention of volatile functions has triggered some research and I found this illuminating article: http://www.decisionmodels.com/calcsecretsi.htm which explains why the code . . .

    Workbooks.Open Filename:=PriceFile (which happened to be a .csv file, naturally !)

    . . . screwed up my Workbook:

    Opening a .CSV file

    Whenever you open a .CSV file, either through VBA or File-->Open, a recalculation of all open workbooks will be triggered, even if calculation is in Manual. Switching off .EnableEvents does not stop this recalculation. The only way I have found to prevent this recalculation from happening is to use VBA to switch Worksheet.EnableCalculation to False for each worksheet that you do NOT want to be calculated.
    Last edited by MartinM; 2012-02-05 at 12:36.

  14. #13
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,819
    Thanks
    133
    Thanked 480 Times in 457 Posts
    Hi Martin

    I presume you have now fully resolved your reported problem.

    The link was excellant - I particularly liked "conditional formats seem to be super-volatile".

    zeddy

  15. #14
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Fully resolved, thank you.

    I remain bemused why the calculation order goes wrong, but am happy to accept that I now have an efficient and working Workbook.

    And, as ever, I've learnt a lot on the way.

    Martin

Posting Permissions

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