+ Reply to Thread
Results 1 to 7 of 7

Help with a reference formula

  1. #1
    Registered User
    Join Date
    10-23-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    4

    Help with a reference formula

    Hi, I'm new here but I have been wracking my brain trying to get this spreadsheet to work how I want it.

    In column A of one worksheet I have a list of names ranging from Cell A1 to A155. On that same worksheet in Column M I will have a few different associated statuses that will change frequently. I want to make it so on another worksheet I can have a particular status being listed and whomever has it at that time from the name list can be referenced.

    Not sure if I am explaining it right.

    I tried doing LOOKUP but it would always pick the last person who had anything at all in the status column instead of who had the proper word, (ie Active,Inactive)

    Any help would be greatly appreciated.

  2. #2
    Valued Forum Contributor jj72uk's Avatar
    Join Date
    03-22-2008
    Location
    Essex, England
    MS-Off Ver
    Work - Office 2000, Home - Office 2007E
    Posts
    360

    Re: Help with a reference formula

    How about making a pivot table?

    http://peltiertech.com/Excel/Pivots/pivotstart.htm

  3. #3
    Registered User
    Join Date
    10-23-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Help with a reference formula

    I never thought of, nor done one of those before. I will try it out. Thanks for the suggestion.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Help with a reference formula

    Is there only one name to pull with Active status?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    10-23-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Help with a reference formula

    Yes, only one name for each status I put in there. Active, Inactive, Missing, etc... I tried the Pivot table and it doesn't look like it will work for what I am trying to do.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Help with a reference formula

    Try

    =INDEX('Sheet1'!$A$1:$A$155,Match(A1,'Sheet1'!$M$1:$M$155,0))

    where Sheet1 is the name of the other sheet and A1 on current sheet holds "Active" without quotes. Adjust as necessary

    You can copy formula down.

  7. #7
    Registered User
    Join Date
    10-23-2009
    Location
    New Jersey
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Help with a reference formula

    NICE!!! That worked like a champ!! Thanks a million!

+ 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