+ Reply to Thread
Results 1 to 6 of 6

Using the Search Function to Find Values Not in a List

  1. 05-20-2019, 12:43 PM #1
    EdwardSnowden
    EdwardSnowden is offline
    Forum Contributor
    Join Date
    05-17-2017
    Location
    Tallahassee
    MS-Off Ver
    15.32
    Posts
    206

    Using the Search Function to Find Values Not in a List

    Hi! I hope you are all having a good day. I am trying to see if formulas ever reference a cell in a different row. I have 40 rows and I have an index from 1 to 40 to indicate which row the formula belongs to. I have converted formulas to text with the FORMULATEXT function. How would I search for all numbers between 1 and 40, excluding the current row number which is specified by the index, within the value of the cell?

    Example:

    A3 has the formula
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    . I want to be able to search for all values that are not 3 within
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    which would find that C2 was an inaccurate inclusion.
    Reply With QuoteRegister To Reply
  2. 05-20-2019, 12:54 PM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Using the Search Function to Find Values Not in a List

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  3. 05-20-2019, 01:32 PM #3
    EdwardSnowden
    EdwardSnowden is offline
    Forum Contributor
    Join Date
    05-17-2017
    Location
    Tallahassee
    MS-Off Ver
    15.32
    Posts
    206

    Re: Using the Search Function to Find Values Not in a List

    See attached. Note that the field "Other Reference" is the only way I can find the mistake in B3. Column H is the field of interest. If it is non-zero than we are potentially capturing a cell in the wrong row.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  4. 05-20-2019, 02:25 PM #4
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Using the Search Function to Find Values Not in a List

    Hi

    Try this formula in H2
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 05-20-2019, 03:58 PM #5
    EdwardSnowden
    EdwardSnowden is offline
    Forum Contributor
    Join Date
    05-17-2017
    Location
    Tallahassee
    MS-Off Ver
    15.32
    Posts
    206

    Re: Using the Search Function to Find Values Not in a List

    Jose, this worked for me! However, is there any way you can break down how this is working? Thanks again!

    Edit: I now understand it. Sorry about that! Thanks a million :D
    Last edited by EdwardSnowden; 05-20-2019 at 04:03 PM.
    Reply With QuoteRegister To Reply
  6. 05-20-2019, 04:10 PM #6
    José Augusto
    José Augusto is offline
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Using the Search Function to Find Values Not in a List

    You are welcome.

    Do not forget to mark this topic as SOLVED.
    Use the Thread Tools in the menu link to do this.
    Thank you.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Can ISNUMBER + SEARCH function find values NOT in a cell
    By jmbelly in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-27-2019, 06:36 AM
  2. [SOLVED] Complex Function: search vertical column list to correspond numbers from another list
    By Jeremy Forsythe in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-07-2018, 08:07 AM
  3. I need to club two column values and search with it to find duplicate values
    By Abhimanyu.bade in forum Hello..Introduce yourself
    Replies: 2
    Last Post: 09-21-2018, 10:31 AM
  4. Function to search a list (unsorted) and find location of a value close to a specified var
    By mvan231 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 02-20-2015, 09:29 AM
  5. If function + Find Function to search for multiple values
    By HabsFan89 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-21-2015, 12:11 PM
  6. [SOLVED] Search list find two greatest values
    By quickheart in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-23-2013, 07:16 AM
  7. search cell values based on list of values in other sheet and add color to row
    By darkbraids in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-10-2012, 08:35 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1