+ Reply to Thread
Results 1 to 6 of 6

Help needed with a quicker way of referencing values in if-then formulas

  1. #1
    Registered User
    Join Date
    04-05-2013
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    13

    Help needed with a quicker way of referencing values in if-then formulas

    I have a formula that looks like this:

    =if(OR(B$1=2,B$1=5,B$1=33,B$1="b",B$1="m",B$1="z"),"Ok","Check Value").

    In reality, however, instead of having only 6 reference values (2, 5, 33, b, m,and z), I actually have a hundred of these non-changing reference values and 1,000 data to check/compare.

    Is there a way of collecting all of these reference values in one cell (for example, in A1) and have a simple formula reference A1 while satisfying the conditions? If not possible, is there any other simpler method than having using the OR function with 100 different reference values (I have not tried if this is possible or if there is a limit in OR functions).

    Thanks in advance.

  2. #2
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Help needed with a quicker way of referencing values in if-then formulas

    Create a list of values that you want to compare with B1. I made a list in column M then enter this formula where you want the result:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  3. #3
    Registered User
    Join Date
    04-05-2013
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Help needed with a quicker way of referencing values in if-then formulas

    Quote Originally Posted by newdoverman View Post
    Create a list of values that you want to compare with B1. I made a list in column M then enter this formula where you want the result:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Thanks newdoverman. Your formula worked for me. However, I had to remove the $ sign in B$1 because I needed to copy the formula down. Thanks again for your help.

  4. #4
    Registered User
    Join Date
    04-05-2013
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Help needed with a quicker way of referencing values in if-then formulas

    This is the "new" formula adapted from newdoverman:

    =IF(COUNTIF($M$1:$M$6,B1)=1,"OK","Check Value")

  5. #5
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Help needed with a quicker way of referencing values in if-then formulas

    You're welcome.

    The only reason that B$1 was in my formula was because you indicated in your original formula that it had some significance that you didn't explain.

    Thanks for the feedback.

  6. #6
    Registered User
    Join Date
    04-05-2013
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: Help needed with a quicker way of referencing values in if-then formulas

    Ok, got it!

+ 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. Formulas for conditionally replicating values needed
    By schill2 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-11-2013, 09:00 PM
  2. Formulas for conditionally replicating values needed
    By schill2 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-05-2013, 07:27 AM
  3. Formula needed for referencing column values. IF, MATCH, VLookup?
    By Oil Depot in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-20-2013, 04:52 PM
  4. Replies: 0
    Last Post: 11-13-2007, 06:58 AM
  5. Replies: 2
    Last Post: 07-14-2006, 03:50 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