+ Reply to Thread
Results 1 to 3 of 3

Indicate cells within a range that contain one or more values dependent upon cell ref

  1. #1
    Registered User
    Join Date
    06-19-2014
    Location
    Canberra, Australia
    MS-Off Ver
    2010
    Posts
    2

    Indicate cells within a range that contain one or more values dependent upon cell ref

    Hi All,

    Any help appreciated by this excel numpty.

    The data in question is a column of text with multiple values in each cell (O7 to O15). Outside of this range, two cells (O2 & O3)allow the user to select values (also present in the data) to be referenced in the following queries. This set up is copied across to another sheet to support two different queries.

    Sheet 1 query purpose
    To filter to show rows that contain (but not exact match) both values as selected in the referenced cells.

    So far I have tried the following but the cell referencing hasn't worked although the formula doesn't bounce;

    =IF(SUM(COUNTIF(O7:O15,{"$O$2","$O$3"})),"TRUE","")

    Otherwise I have tried out different filtering opens but have had problems with getting excel to both reference values in different cells and use CONTAINS as opposed to EXACT match.

    Sheet 2 query purpose
    To filter to show rows that contain one value but not contain the other.

    Thanks.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Indicate cells within a range that contain one or more values dependent upon cell ref

    Hi delphne and welcome to the forum,

    You formula appears to be somewhat off. First, you can't include cell references as your criteria into braces {}, however you can include single items like {"book","map"} so, this would be your countifs formula =COUNTIFS(O7:O15,">="&O2,O7:O15,"<="&O3) but you also can use COUNTIF combination (see below)

    =IF(SUM(COUNTIF(O7:O15,O2),COUNTIF(O7:O15,O3)),"TURE","")

    Try them one and see if they worked for you.
    Last edited by AlKey; 06-21-2014 at 08:08 AM.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    06-19-2014
    Location
    Canberra, Australia
    MS-Off Ver
    2010
    Posts
    2

    Re: Indicate cells within a range that contain one or more values dependent upon cell ref

    Hi AlKey,

    Apologies for my tardy reply - it's lurgy season here in Australia at the moment.

    Thankyou for your contribution. Unfortunately, neither formula seems to distinguish (in relation to the values in the two reference cells O2 & O3) between the different sets of values in the column (O7:O15). As I probably failed to explain the situation accurately, here's another go with some pretend data;

    O7 = Apples
    Oranges
    Lemons
    O8 = Apples
    Lemons
    Limes

    If I select 'Apples' as the value in O2 and 'Oranges' as the value in O3, I would expect the formula (for the 1st query) to show TRUE for O7 but not O8. Currently TRUE appears where only one of the values selected is present as well, but I want it to show TRUE only where both values are contained (but other values may also be present).

    I do think the formula has potential though so will continue to work upon it but happy to hear any further brainwaves.

    Thanks. Steph.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 7
    Last Post: 07-30-2012, 01:31 PM
  2. [SOLVED] 2nd value in cell range dependent on changing values other cells (dynamic?)
    By RUJedi in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-16-2012, 07:34 PM
  3. Automatically emailing a range of cells if the cell values = other cell values.
    By Apollon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-14-2012, 01:40 AM
  4. Replies: 2
    Last Post: 07-12-2009, 05:14 PM
  5. Cell value dependent on the values of other Cells.
    By FShaw in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-18-2009, 01:40 PM

Tags for this Thread

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