+ Reply to Thread
Results 1 to 5 of 5

Can In-Equalities such as [ <=,>=,<>,= ] be dynamically used in a code?

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Can In-Equalities such as [ <=,>=,<>,= ] be dynamically used in a code?

    Can In-Equalities such as [ <=,>=,<>,= ] be dynamically used in a code?

    Now, I would want to try using these Inequalities such as [ <=,>=,<>,= ] as in code dynamically...

    Ex: In COUNTIF,COUNTIFs we write the code as
    [ = COUNTIF(Range, Sign & Value ) where Sign is [ <=,>=,<>,= ] and Value is any Number.

    In a traditional MultiLookup Formula to get Multiple Values:


    =INDEX($A$2:$A$5,SMALL(IF($B$2:$B$5=$B$2,ROW($A$2:$A$5)-ROW($A$2)+1),ROWS($A$2:$A2)),1)
    Can we use the "=" dynamically in the expression above $B$2:$B$5=$B$2, so I want to store "=" in a cell and use it dynamically...

    In reality I have several criterions which are falling in Ranges and therefore I need to use them as [ <=,>=,<>,= ] ..

    SO how DO i do that..the above formula is for explantion purpose..

    How do I use it in a a Multiple Lookup Scenario?

    Regards
    E
    Last edited by e4excel; 01-17-2011 at 02:35 AM. Reason: More Details!

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Can In-Equalities such as [ <=,>=,<>,= ] be dynamically used in a code?

    You can only really use the operators dynamically in functions such as COUNTIF, eg:

    Please Login or Register  to view this content.
    where B1 may contain the criteria as a string (eg >10, <>apple)

    Inserting a COUNTIF into your Array is not viable
    (edit: not impossible [pending criteria set up] but it would require your Array become Volatile c/o OFFSET)

    So given the above restriction you're really left with either

    a) using an Evaluate type approach such that you can build a formula string and have it evaluated
    think of the Evaluate route as being INDIRECT-esque only here the string is being converted to a formula rather than a Range
    (note: this requires use of either i] VBA or ii] old XLM4 call via Defined Name)

    b) using a CHOOSE type approach such that you have all bases covered in the formula and choose the most appropriate.

    Please Login or Register  to view this content.
    Last edited by DonkeyOte; 01-17-2011 at 06:57 AM. Reason: typo

  3. #3
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Can In-Equalities such as [ <=,>=,<>,= ] be dynamically used in a code?

    Thanks DO,

    I jsut have the right person answering my query as I have build more criterions into one of your SOLUTIONS where you had helped with a code on MULTI_LOOKUP + MULTIPLE CRITERIONS + SORTING..

    And its working, the only thing is I wanted to increase the functionality of the same values with different INEQUALITIES..


    b) using a CHOOSE type approach such that you have all bases covered in the formula and choose the most appropriate.

    Please Login or Register  to view this content.

    Can the Criterions be used a Name Defines..

    Like if I want to use or not use certain criterions then can then be used in Name Defines?


    Donkey Ote's Code for MULTILOOKUP + SORTING:

    The Blue Font are additional Criterions added and I want to adda few more and keep it still dynamic..

    Please Login or Register  to view this content.


    Want to add more criterias such as Premium Ranges and Sum Assured Ranges in such a way that if there are no values in Upper Limit then only the criteria becomes just One-Way as in (INDIRECT("'"&$C$2&"'!$J$2:$J$"&LastRow)>=$C$6) and if its filled then
    (INDIRECT("'"&$C$2&"'!$J$2:$J$"&LastRow)>=$C$6)8(INDIRECT("'"&$C$2&"'!$J$2:$J$"&LastRow)<=$D$6)..

    And if just one Lower limit is shown then can How can I keep the Inequalities Dynamic in this ?


    Regards
    E
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Can In-Equalities such as [ <=,>=,<>,= ] be dynamically used in a code?

    I am adding one more functionality like Famil in which all the policies in the same family should come together..

    Please see the new attachement.. with the new Sheet Clent Data Family..

    This entire concept is from the Insurance Agent's perspective who wants to help his clients raise money in the Premium Due mOnths for Individual and Group policies in a family..
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Can In-Equalities such as [ <=,>=,<>,= ] be dynamically used in a code?

    Quote Originally Posted by DonkeyOte View Post
    (edit: not impossible [pending criteria set up] but it would require your Array become Volatile c/o OFFSET)

    So given the above restriction you're really left with either

    a) using an Evaluate type approach such that you can build a formula string and have it evaluated
    think of the Evaluate route as being INDIRECT-esque only here the string is being converted to a formula rather than a Range
    (note: this requires use of either i] VBA or ii] old XLM4 call via Defined Name)

    b) using a CHOOSE type approach such that you have all bases covered in the formula and choose the most appropriate.

    Please Login or Register  to view this content.
    How would I use the below in the approach..I am not able to understand using the CHOOSE function..can u please help in this?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1