+ Reply to Thread
Results 1 to 2 of 2

Vlookup?

  1. #1
    Registered User
    Join Date
    08-23-2009
    Location
    US
    MS-Off Ver
    Excel 2007
    Posts
    10

    Vlookup?

    Is this possible? if so, how?

    I have a workbook where sheet1 is a list of accounts available for use. Sheet 2 is a list of accounts currently being used.

    Is there a way in sheet1 (I tried vlookup unsuccessfully but may not have been using it correctly) that I can distinguish between the accounts available for use and the accounts currently being used by simply using one formula?

    example. if sheet1 contain (a2:10) 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 as available

    and sheet 2 contains (a2:a5) 2,3,6,7,8 as being used how can I differentiate in sheet1 the accounts that appear in sheet2?

    If I only had 10 accounts it wouldn't be an issue, but I have many, many, many, many more and it would take me hours, hours and many more hours to determine which accounts were not being used.

    Anybody know if this is possible?

    Thanks
    G-
    Last edited by guyel; 08-25-2009 at 12:37 PM. Reason: solved

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Vlookup?

    1) You can use a formula in an adjacent cell. If the account numbers are in column A on both sheets, put this in B2 and copy down the whole set of account numbers, the ones that are available will be flagged:

    =IF(COUNTIF(Sheet2!A:A,A2)>0,"Used","Available")

    2) Using the same approach, you can use conditional formatting to cause the used account numbers on sheet1 to "grey out" all by themselves.

    - on sheet two, highlight column A and type "USED" into the Name box (just to the left of the Formula bar).
    - on sheet one, highlight the account numbers all the way down, let's say you highlighted A2:A1000
    - Click on Format > Conditional Formatting
    - Set Condition1: Formula Is: =COUNTIF(USED,A2)>0
    - Set the Format... > Patterns: color to grey

    I don't use Excel 2007 so you'll have to locate the conditional format controls if they moved them.

    NOTE:
    Don't think "Vlookup?" is very helpful thread title. Write your titles after you write the content of your question, I'm sure you'll get much more accurate titles that way. "Cross referencing account numbers between two sheets?"
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ 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