+ Reply to Thread
Results 1 to 17 of 17

Sheet to sheet search, specific word string to copy different cell

  1. #1
    Registered User
    Join Date
    08-09-2017
    Location
    Louisville, KY
    MS-Off Ver
    2010
    Posts
    8

    Exclamation Sheet to sheet search, specific word string to copy different cell

    Folks I need help ASAP, and I am sure this is the wrong forum to place this in, but I am not sure where it should go. I am trying to work on automating a report where sheet 1, looks at sheet 2 for data to copy over. Specifically, I need Cell X on sheet 1 to look at column B on sheet 2. If it can find a string of words "XXX" then it will copy Cell Y, from a different column on sheet 2 to cell X on sheet 1. If its not present, then I either want a 0 in cell x on sheet 1 or for it to be left blank.

    PLEASE HELP ME. I have been banging my head, researching this issue for days to no avail. Thank you for any and all assistance.
    Last edited by Rmccarver; 08-09-2017 at 09:39 AM.

  2. #2
    Registered User
    Join Date
    08-09-2017
    Location
    Louisville, KY
    MS-Off Ver
    2010
    Posts
    8

    Re: Need HELP, pretty sure this is the wrong forum.

    Changed title to be explanatory of issue.

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,016

    Re: Sheet to sheet search, specific word string to copy different cell

    Please read the forum rules regarding duplicate threads - do not open duplicates.

    Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it. Thanks for your understanding and patience.

    If you attach your workbook here, you might get more interest.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,261

    Re: Sheet to sheet search, specific word string to copy different cell

    Attach a sample workbook (not image).

    Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

    Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  5. #5
    Registered User
    Join Date
    07-20-2017
    Location
    milwaukee, wisconsin
    MS-Off Ver
    2010
    Posts
    11

    Re: Sheet to sheet search, specific word string to copy different cell

    put this into cell x

    =IFERROR(VLOOKUP("xxx",Sheet2!B:B,1),"0")

  6. #6
    Registered User
    Join Date
    08-09-2017
    Location
    Louisville, KY
    MS-Off Ver
    2010
    Posts
    8

    Re: Sheet to sheet search, specific word string to copy different cell

    So if I am trying to say D11 is equal to "Search for US VSP STD" on column B sheet 2, if found copy sheet 2 cell G9 to D11, but if not present place 0 in cell D11 sheet 1. Can you plug in those variables into that equation to show me how it would appear?

    Thanks so very much!

    Addendum: Also, if there were going to be multiple instances possible, but I only wanted it to accept the first entry...how would that change the formula?
    Last edited by AliGW; 08-09-2017 at 11:14 AM.

  7. #7
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,261

    Re: Sheet to sheet search, specific word string to copy different cell

    VLOOKUP will find ONLY the first entry of value "XXX"

  8. #8
    Registered User
    Join Date
    08-09-2017
    Location
    Louisville, KY
    MS-Off Ver
    2010
    Posts
    8

    Re: Sheet to sheet search, specific word string to copy different cell

    Attached Example sheet.

    So in this example I need excel to....

    1. Have Sheet 1, Cell D2 Search Sheet 2 column B for only the first instance only of the string VS-US VSP STD.
    2. If it finds the string then it will copy Sheet 2 Cell D2 to Sheet 1 Cell D2.
    3. If it does not find it, then leave cell blank or enter 0.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-09-2017
    Location
    Louisville, KY
    MS-Off Ver
    2010
    Posts
    8

    Re: Sheet to sheet search, specific word string to copy different cell

    Sorry John, I accidentally erased part of what I had asked you in writing the addendum. Could you possible write the equation out for me showing me how the values plug in using my uploaded example book, so I can repeat the process for myself? Thanks for being so helpful.

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,016

    Re: Sheet to sheet search, specific word string to copy different cell

    I've amended your previous post for you.

  11. #11
    Registered User
    Join Date
    08-09-2017
    Location
    Louisville, KY
    MS-Off Ver
    2010
    Posts
    8

    Re: Sheet to sheet search, specific word string to copy different cell

    Your Awesome Ali

  12. #12
    Registered User
    Join Date
    07-20-2017
    Location
    milwaukee, wisconsin
    MS-Off Ver
    2010
    Posts
    11

    Re: Sheet to sheet search, specific word string to copy different cell

    If im understanding properly this should work for you i think lol

    =IF(Sheet2!B:B="VS-US VSP STD",Sheet2!D:D,"0")

  13. #13
    Registered User
    Join Date
    08-09-2017
    Location
    Louisville, KY
    MS-Off Ver
    2010
    Posts
    8

    Re: Sheet to sheet search, specific word string to copy different cell

    Ok, I think I've found the problem with what I am suggesting John. My fault.

    So I need the formula to do the following.

    1. Sheet 1, Cell D2 will look at sheet 2 and search for the string "xxx" in column B. (This may be in a variable position based on reporting.
    2. If it finds string "XXX" then I need it to copy the cell 2 cells to the right of the string. (due to variable location I will never know this cell ID)
    3. I then need it to past the cell data from the cell 2 positions right of the string on sheet 2, to the cell D2 on sheet 1.
    4. Finally if no string is found, then it will return with a blank or 0.

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    23,261

    Re: Sheet to sheet search, specific word string to copy different cell

    Based on your posted file

    in D2

    =IFERROR(INDEX(Sheet2!$D$2:$D$19,MATCH("*" &$B2,Sheet2!$B$2:$B$19,0)),"")
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-20-2017
    Location
    milwaukee, wisconsin
    MS-Off Ver
    2010
    Posts
    11

    Re: Sheet to sheet search, specific word string to copy different cell

    Ok so try this formula in D2 =VLOOKUP(B2, Sheet2!B2:H19, 3, TRUE)

  16. #16
    Registered User
    Join Date
    07-20-2017
    Location
    milwaukee, wisconsin
    MS-Off Ver
    2010
    Posts
    11

    Re: Sheet to sheet search, specific word string to copy different cell

    if that doesnt work im sorry, its a tough one :/

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    62,016

    Re: Sheet to sheet search, specific word string to copy different cell

    @Rmccarver

    Rule 08: Cross-posting Without Links

    Your post does not comply with Rule 8 of our Forum RULES. Do not cross-post your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    No further help to be offered, please, until the OP has complied with this request.

+ 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. wrong forum....
    By jackf-nc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-04-2016, 09:04 PM
  2. Issue - Whats wrong with this forum?????
    By milanorsx in forum Suggestions for Improvement
    Replies: 6
    Last Post: 04-09-2015, 02:29 AM
  3. [SOLVED] Index and Match... I'm pretty sure I'm just doing it wrong
    By rstarr1 in forum Excel General
    Replies: 2
    Last Post: 10-13-2014, 05:40 PM
  4. posted my problem to wrong part of the forum
    By daletgfoster in forum Excel General
    Replies: 1
    Last Post: 02-20-2012, 08:11 PM
  5. Macro to hide/unhide columns (WRONG FORUM, PLEASE IGNORE)
    By IcemanFTW in forum Excel General
    Replies: 2
    Last Post: 03-08-2011, 11:12 AM
  6. Sorry, wrong forum
    By sglick in forum Excel General
    Replies: 2
    Last Post: 11-30-2010, 05:15 PM
  7. Sorry, wrong forum
    By gnoke in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-12-2010, 05:10 AM

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