Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pass cell address or text to formula/function? (Ex

    Peter,
    I think at this point, you might as well just write a UDF to return the validation range - it will be a lot simpler than what you seem to be trying do do via formulae and defined names! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> (particularly if you need a UDF to make your formulae work!)
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  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: Pass cell address or text to formula/function? (Ex

    I know this is related to your previous post, and it will have the same problems, but the function you want is INDIRECT.

    I am not sure I completely understand what you want the "FindList" function to do that INDIRECT does not and your example does not seem to come from your workbook
    I can use eg:
    =MATCH(C2,INDIRECT("lst"&B2),0)

    without any UDF.

    Note: If you were going to use a UDF for this, I would make the MATCH part of the UDF:
    =FindItemInList(B1,B2)
    And give the index number....

    Perhaps I am just a bit confused on what you are after exactly. Do you have an example that is directly relatable to your sample file?

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass cell address or text to formula/function?

    Hi Rory

    Good.. I'm glad we agree.... so could you/anyone else give me a starter for 10 on writing such a UDF? I'm an Excel UDF 'virgin', though I have LOTS of programming experience in VB, Wordbasic, basic, COBOL, dBase/Clipper, Some others I've forgotton I knew, etc, etc.

    Cheers
    Peter

  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: Pass cell address or text to formula/function? (Ex

    Another option, since you are going the "code route" is not to use validation, but to use comboboxes instead:
    You have much more control
    You won't need to define the names: The ranges can be read directly when the combobox is called
    The dependent lists can be reset (something validation fails at!)

    <post:=537,206>post 537,206</post:> has a demo with 1 combobox doing the work of many validations whose technique could be adapted to multiple and dependent lists...

    Once you make the leap to decide that code is possible, you gain much more control by using code...

    Steve

  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

    Re: Pass cell address or text to formula/function?

    We can help write a UDF, but you will need to be specific on what you want/need

    One problem with a UDF in validation is that it can not be used directly in a validation formula. You will have to have cells with the intermediate calcs of the custom function and have the validation read these cells. If you have a lot of validations, this is many intermediate calcuations....

    Steve

  6. #6
    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: Pass cell address or text to formula/function? (Ex

    If you put in Cell C2 the following formula (with your defined "FindList")
    =MATCH(B1,FindList,0)

    It does what you "want" without a UDF...

    [Your "FindList" list looks up the name to the left of the cell the formula is in as you have currently defined it..]

    Steve

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pass cell address or text to formula/function?

    <pre>Function GetRange(varRange As String) As Range
    Set GetRange = Application.Caller.Parent.Parent.Names(varRange).R efersToRange
    End Function
    </pre>


    Note that as Steve said, you can't use this directly in DV. You can define a name as e.g. DataList and have it referto =GetRange(B2) or =GetRange("Test1") or whatever else you want. This will work with dynamic ranges too.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass cell address or text to formula/function?

    <P ID="edit" class=small>(Edited by j.peter.orourke on 22-Sep-06 17:05. Hopefully to aid clarification.... )</P>Hi Steve

    Thanks, and sorry.. I appear not to do a good job of explaining myself. Decided to start a new thread since this is specifically about how to write a formula/UDF. OK.. The MATCH example I gave is not an 'issue' as such. What I think I need to do is write a UDF that replaces the formula:-

    =INDIRECT(INDEX(tblLists,MATCH(B2,INDEX(tblLists,0 ,1),0),3))

    The ONLY issue I really have with the above formula is that it's a bit longwinded. If I had a universal formula that replaced all of the above, like so:

    =FindList(B2) - Albeit the formula needs to take cell addresses, defined names, and hard coded text as it's paremeter.

    Then it just makes it easier on the eye when you do want to do something like a MATCH, INDEX, VLOOKUP etc, etc. Currently I would write the MATCH command, for example, as:

    =MATCH(B1,INDIRECT(INDEX(tblLists,MATCH(B2,INDEX(t blLists,0,1),0),3)),0)

    Rather than:

    =MATCH(B1,FindList(B2),0)

    i.e. The formulas I have work, they're just a tad long winded. A Defined Name is inflexible because it depends on relative addressing and there is no way that I know of to pass a parameter, like the cell address B2, into a formula defined as Defined Name? So.. everything works as is.. Just looking to make it easier to read. A UDF seems the only option. Am I making myself clear yet?

    EDIT - In fact Steve, maybe I'm complicating matters by including the INDIRECT() portion of the above formula. The portion I actually want to shorten/replace is:

    INDEX(tblLists,MATCH(B2,INDEX(tblLists,0,1),0),3)

    Which is the part that finds the correct cell in the tblLists array. B2 being the part I would ideally like to pass as a parameter to a UDF.

    Cheers
    Peter

  9. #9
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass cell address or text to formula/function?

    Thanks Rory..

    I get a #NAME? error if I try to insert in a cell the command =GetRange("lstWorksheet").. Which I suspect is finger trouble on my part perhaps having put the UDF code in the wrong place? Just did Alt+F11, inserted code, closed and returned... Is there something I should be reading up to learn to do do this properly?

    Also, I suspect I've confused everyone, myself included! I'm NOT actually looking to return a cell range that is refererred to via a Defined Name. I'm looking to return the contents of a specific cell in column 3 of the array/Defined Name tblLists. Although I use this formula to point at the correct cell:

    =INDIRECT(<font color=red>INDEX(tblLists,MATCH(<font color=blue>B2<font color=red>,INDEX(tblLists,0,1),0),3)<font color=black>)

    It's actually the portion in red that needs reducing to a UDF, and the part in blue that I'd like to pass as a parameter.

    Apologies for any confusion.

    Cheers
    Peter

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Pass cell address or text to formula/function?

    Peter,
    That function will return the range referred to by whatever you pass to it, whether that be a cell containing a range name or an actual range name - it was based on my original point that if you are using a UDF, you might as well bypass all these formulae and simpy use it to return the range you are interested in (rather than using INDIRECTs and OFFSETs). That is why you can't simply use it in a cell like that.
    And yes, you have definitely confused me as to what exactly you are after and why! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass cell address or text to formula/function?

    <P ID="edit" class=small>(Edited by j.peter.orourke on 24-Sep-06 09:33. Fixed typo!)</P>Thanks Rory... Well. perhaps if I can get the UDF to work I can demonstrate my 'confused' solution... Any pointers/other threads worth looking at to help me understand why I'm getting #NAME? when I try to call the UDF?

    As an aside.. this model does exactly what I want it to do already - self maintaining extendable lists which can easilly be linked as nested sub lists - the UDF thing is just a refinement to aid clarity, it won't alter/improve functionality.

    Cheers
    Peter

  12. #12
    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: Pass cell address or text to formula/function?

    You get #name error when you have a name that it can not find. Where is the UDF located? If the UDF, for example, is in your personal.xls file and you don't preface it with the filename it will get this error.

    Steve

  13. #13
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass cell address or text to formula/function?

    Hi Steve

    Located in..... entirely the wrong place! i.e. Finger trouble on my part. Found another thread User Defiend Functions that helped - Insert Module... Doohh..

    The UDF doesn't appear to do what I 'think' i want it to do, which doubtless has more to do with my inability to explain what I want... So, I think I'll leave this for now - the model does what I set out to achieve anyways - and come back to it when I've had time to learn more about VBA.. Any good tutorial/reference links out there? I'm far from being a 'virgin' programmer - just not overly familiar with VBA, though I did fo a lot of VB stuff up to version 2 and WordBasic, back in the mists of time I know!

    Cheers and thanks again to all for your help and support. Fantastic place!

    Cheers
    Peter

  14. #14
    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: Pass cell address or text to formula/function?

    Check out <post:=320,321>post 320,321</post:> it has some links to articles.

    I would presume that your "virgin-ness" is more to inexperience with what the excel object model is and not so much the programming itself. My recommendation for that is to use the recorder to do some things and look at the code to get an idea of the object model. I think once you see code written, if you have VB experience, you will see what it is doing: it is just learning the object model.

    I still find, on many occasions, recording a macro can get me 50-80% complete. Editing existing code is often easier than creating...

    Steve

  15. #15
    3 Star Lounger
    Join Date
    Oct 2002
    Location
    Ross-on-Wye, Herefordshire, United Kingdom
    Posts
    212
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Pass cell address or text to formula/function?

    Thanks Steve.. Much appreciated.

    Cheers
    Peter

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
  •