+ Reply to Thread
Results 1 to 5 of 5

match a cell value to a range and when found alter the value (underscore) in the range

  1. #1
    Registered User
    Join Date
    07-22-2015
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    3

    match a cell value to a range and when found alter the value (underscore) in the range

    I have been looking at formulas to figure out how to solve my problem, but have nothing yet. My spreadsheet is used for Fantasy Football. I have a worksheet that contains, by row, all the teams and associated player names according to position (column headings). I have another worksheet that contains my ranking of teams (1-32) with the team name (same as in worksheet 1, but different order). When I mark a team taken on worksheet 1 (I have a second column that I use to indicate a team/player is chosen), I want the formula to search through the range of names in worksheet 2 and when matched, mark the name (strikethrough or color) indicating that it is taken.

    Example is:
    worksheet 1 has all the 32 NFL team names in an order
    worksheet 2 has all the 32 NFL teams names in a range determined by some criteria (not in the same order as they are in worksheet 1)
    When I mark a team has been selected in worksheet 1, I want the formula to match the NFL name from worksheet 1 in the range of NFL names in worksheet 2 and when it matches to strikthrough (or make the cell a color, some way to indicate the the team has been selected) the name.

    I am continuing to research, but any help would be appreciated. Thanks.

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: match a cell value to a range and when found alter the value (underscore) in the range

    Quote Originally Posted by cuban1957 View Post
    When I mark a team has been selected in worksheet 1...
    And how do you do that?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    07-22-2015
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    3

    Re: match a cell value to a range and when found alter the value (underscore) in the range

    I have 2 cells to all information; 1st cell is to mark the team/player as taken by another team (i place an "x" in the cell) and the other cell is adjacent and contains the name of the team/player. When either i or another team select a player my conditional formatting puts a strikethrough or blanks out the name so it is easy for me to know who is already taken. My problem is that i then want a formula to "go to" the other worksheet and strikethrough/color the team/player that was just taken (marked taken by me).

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: match a cell value to a range and when found alter the value (underscore) in the range

    Sounds like you just need to apply the same conditional formatting on the other sheet.

    If that's not what you need perhaps you could post a SMALL sample file and show us what you want.

  5. #5
    Registered User
    Join Date
    07-22-2015
    Location
    Chicago, IL
    MS-Off Ver
    2013
    Posts
    3

    Re: match a cell value to a range and when found alter the value (underscore) in the range

    I started to do just that (conditional formatting), but i believe there is a more efficient way. I cut down my worksheet to just defense and QB. 1st worksheet: contains the lists of teams/players per position. In this worksheet i mark which team/players are selected as the draft progress, thus allowing me to easily see who is left on what team. To the left of each name is where i indicate whether the team/player was selected ("x"). Then the team/player has a strikethrough. On the other worksheets i have the teams/players listed in the order i want to select them (my priority). So when a team/player is selected during the draft i want it to be indicated on my team/player priority worksheet lists. That way i would just select the highest player on the priority list that is not marked (strikethrough, colored, whatever i can do to indicate they are taken). I currently have conditional formatting, but it is tedious to maintain and i also have running backs and wide receivers to do. And there could be 64-90 running backs and wide receivers in my priority lists. So i don't want to have to create conditional formatting for all of them, thus just matching the player entry in the Team worksheet to the same player in say Running Back worksheet. I believe there is a way to use some kind of match formula to locate the player match between the Team worksheet and Running Back worksheet and then mark the Running Back entry as taken (strikethrough for example). Maybe using conditional formatting with some other match formula that indicates which one matched. Something like that. I have attached a small version of my excel. Thanks Tony for the help.
    Attached Files Attached Files

+ 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: 4
    Last Post: 07-20-2015, 06:44 AM
  2. code to alter 3 cells through range of % reductions and paste dependant cell results
    By j.farr3ll in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-26-2015, 05:25 PM
  3. [SOLVED] Return Value from string if it Match is Found in Range
    By Jonathan78 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-17-2015, 10:05 AM
  4. Replies: 4
    Last Post: 08-04-2014, 04:48 AM
  5. [SOLVED] reference date time range, return the value into all cell that match datetime range
    By Jarvco13 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-22-2013, 10:39 PM
  6. Search Range for IF then alter cell based on another
    By nhotte in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-15-2011, 10:05 PM
  7. Replies: 6
    Last Post: 10-14-2009, 03:01 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1