+ Reply to Thread
Results 1 to 18 of 18

Search for name find value

  1. #1
    Registered User
    Join Date
    07-27-2015
    Location
    British Columbia, Canada
    MS-Off Ver
    2007
    Posts
    89

    Search for name find value

    Hello All
    I was trying to use V & H lookups but can't have it look for the players name and then check if a "LF" allocation has been done.

    In my case example I have a player that can't be allocated to "LF"

    I was looking for a way to highlight say the players name if he gets a allocation of "LF"

    I have uploaded a Jpeg action and the Excel file

    John
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    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: Search for name find value

    Why not just use a Conditional format?
    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.

  3. #3
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Search for name find value

    You can setup a conditional format in D4 using this formula...

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Regards,
    Stephen

    If you feel someone has helped you please thank them and click on the star on their post to add reputation.
    Please ensure that you use code tags where relevant, and mark solved threads as solved.
    Most of all please be respectful and understanding of others.

  4. #4
    Registered User
    Join Date
    07-27-2015
    Location
    British Columbia, Canada
    MS-Off Ver
    2007
    Posts
    89

    Re: Search for name find value

    Hello Richard and Stephen
    I tried to use "Condition"
    The part that I can't get is the Player "Lukas" can be placed in any slot from D4:D14. What doesn't change is the allocations from E4:J15
    So I need a way to scan down D4:D15 and stop on Lukas and then read the values to the right E:J and flag "LF"

    The Players name is selected from another "=D60" for Lukas.

    John

    John

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Search for name find value

    Quote Originally Posted by spitfireblue View Post
    You can setup a conditional format in D4 using this formula...

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    What is that supposed to do? I could also not get it to work
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Search for name find value

    Quote Originally Posted by JLeith View Post
    In my case example I have a player that can't be allocated to "LF"

    I was looking for a way to highlight say the players name if he gets a allocation of "LF"
    How would we know they cannot have LF?

  7. #7
    Registered User
    Join Date
    07-27-2015
    Location
    British Columbia, Canada
    MS-Off Ver
    2007
    Posts
    89

    Re: Search for name find value

    In this example. The coach would indicate that this player is better at say CF not LF.
    I just was looking to see if there was a way to scan down for a player and then check the allocation on the right don't cause a player to be in a position say as "P" pitcher if he is not a pitcher.

    I could have a button to activate a macro that could scan selected players and ensure they are not playing a position.

    John

  8. #8
    Registered User
    Join Date
    07-27-2015
    Location
    British Columbia, Canada
    MS-Off Ver
    2007
    Posts
    89

    Re: Search for name find value

    I normally place notes in D23:D27 so I can search manually. Just looking to see if there was a auto calculations.

    John

  9. #9
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Search for name find value

    Dont forget that (1) members here may have no clue what CF or LF or ZF might be (I know I dont), and (2) we also probably have no clue what you are trying to do here (again, I have no idea). So you need to explain this in such as way that complete noob's have some idea of what you are doing and what you want

  10. #10
    Registered User
    Join Date
    07-27-2015
    Location
    British Columbia, Canada
    MS-Off Ver
    2007
    Posts
    89

    Re: Search for name find value

    I thought my Jpeg drawing would help and in the Excel program they can see the "LF".
    The Excel is a Baseball postions allocation program I built.

    I have added a little more imfo on the Jpeg

    Does this help

    John
    Attached Images Attached Images

  11. #11
    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: Search for name find value

    You need to give us some rule for highlighting an LF value since presumably you don't want to identify every LF value.
    For instance is there a hierarchy of C, CF, LF....etc and you're looking to see whether LF precedes/succeeds another particular value.

  12. #12
    Registered User
    Join Date
    07-27-2015
    Location
    British Columbia, Canada
    MS-Off Ver
    2007
    Posts
    89

    Re: Search for name find value

    Hello Richard

    I just noticed that this vision of my might not be possible. The way the program runs is at D57:D68 the players batting postition are establised.
    Then up in D4:D15 there is a copy of the info in D57:D68. Lukus can be placed in one of the D4:D15.
    In fact the Name Lukus is not named on the D4:D15.
    Excel list his name and this is realy just for printing.

    Maybe I will just have to do a Condition and list all "LF" and then look across and match the name to a high light.
    The E4:J15 is a constant and the players names is a variable.

    Your thoughts Richard ?

    John

  13. #13
    Registered User
    Join Date
    07-27-2015
    Location
    British Columbia, Canada
    MS-Off Ver
    2007
    Posts
    89

    Re: Search for name find value

    I forgot the image

    John
    Attached Images Attached Images

  14. #14
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Search for name find value

    It can be done using conditional formatting using the following formula, which will highlight Lukas' name if he has LF in the fields next to his name.
    You just need to apply the formula to D4 and fill the formatting down to D14.

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-27-2015
    Location
    British Columbia, Canada
    MS-Off Ver
    2007
    Posts
    89

    Re: Search for name find value

    Hello Spitfireblue
    Wonderful work. The formala worked 100%.
    I just had 1 glitch I placed the format on the other players and if they can play LF.

    I guess I will need a way to isolate Lukas name only if he is playing LF.

    But Lukas real name is down in D60.

    John
    Last edited by JLeith; 01-23-2017 at 02:26 AM.

  16. #16
    Valued Forum Contributor spitfireblue's Avatar
    Join Date
    01-29-2015
    Location
    Adelaide, Australia
    MS-Off Ver
    2007,2010,2016
    Posts
    611

    Re: Search for name find value

    I guess I will need a way to isolate Lukas name only if he is playing LF.
    But Lukas real name is down in D60.
    Hi John,

    Not sure what you are after here. The formula already does this. It checks both his name (from D60) and if he is playing LF in any innings.

  17. #17
    Registered User
    Join Date
    07-27-2015
    Location
    British Columbia, Canada
    MS-Off Ver
    2007
    Posts
    89

    Re: Search for name find value

    Hello Stephen
    Yes you are correct if I have "=IF(AND(COUNTIF($E4:$J4,"LF")>0,$D4=$D$60),1,0)" this works 100%.

    The glitch is if Lukas is in D2 the D60 has to change to 58.

    Also if I have say "Cole" playing "LF" and he is allowed to play "LF"

    I took you Condition format and developed a Macro to find Lukas and other players
    I press the button and it finds the player then I have to press the "clear" to review another player

    Excel Test included
    Attached Images Attached Images
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    08-09-2012
    Location
    British Columbia
    MS-Off Ver
    Excel 2010
    Posts
    189

    Re: Search for name find value

    Your player names are really coming from the player Master List (via validation)
    I would add the same positional data validation you have for D4:J15 to column E next to that master list.

    Then attach a macro like this to a button and/or call it from the Worksheet_Change event of D4:J15 cells
    Please Login or Register  to view this content.

+ 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. [SOLVED] search and find a value
    By frnije in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-13-2014, 07:17 PM
  2. Match data and find data following search several search criterias
    By maxtymo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-05-2013, 08:18 PM
  3. Truncate text strings using LEFT and FIND/SEARCH for multiple search terms
    By ngdoherty in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-17-2013, 07:51 PM
  4. Excel FIND/SEARCH Find Name Hours Worked
    By staceynix in forum Excel General
    Replies: 8
    Last Post: 02-01-2013, 01:39 AM
  5. Find or Search
    By tangomj in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-22-2007, 03:32 PM
  6. FIND / SEARCH find nonnumeric
    By Barry Staples in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-03-2006, 06:34 PM
  7. Replies: 1
    Last Post: 01-31-2006, 06:25 PM
  8. search a string withing a string : find / search hangs
    By itarnak in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-24-2005, 11:05 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