+ Reply to Thread
Results 1 to 11 of 11

Link, VLookUp, Choose and Concatenate?

  1. 07-20-2015, 04:51 PM #1
    sxh678
    sxh678 is offline
    Registered User
    Join Date
    07-20-2015
    Location
    London
    MS-Off Ver
    MS 2010
    Posts
    4

    Link, VLookUp, Choose and Concatenate?

    Hi,

    I am trying to link two worksheets together. However, I want Sheet 2 to only show the rows of Sheet 1 when Column C in Sheet 1 has the letter M in it. Moreover, when another M is added at a later date to Sheet 1 Column C, I want that to be added automatically to Sheet 2. I also do not want empty cells/rows in Sheet 2 for whenever there is no M in Column C of Sheet 1, but for example when Row 3, 8, and 10 have a M in column C in Sheet 1, then Sheet 2 should show that as Row 1,2 and 3.

    I am unsure how to go about this, any help, hints or tips?

    Thank you!
    Reply With QuoteRegister To Reply
  2. 07-20-2015, 05:02 PM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Link, VLookUp, Choose and Concatenate?

    Your Q qas a bit ambiguous. Does C contain M or contain a string that contains M (e.g. docuMent). I have assumed it's just M, pending hearing from you...
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 07-20-2015, 05:03 PM #3
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Link, VLookUp, Choose and Concatenate?

    I should have said, this is an array formula.

    Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

    You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly braces yourself - it won't work...
    Reply With QuoteRegister To Reply
  4. 07-20-2015, 05:09 PM #4
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Link, VLookUp, Choose and Concatenate?

    Here's a non-arrayed solution that requires a helper column. Column J on Sheet1 is used to create unique id's for all rows containing M in col C. Then Sheet2 uses INDEX/MATCH to match up only these unique ID's
    Attached Files Attached Files
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  5. 07-20-2015, 05:16 PM #5
    sxh678
    sxh678 is offline
    Registered User
    Join Date
    07-20-2015
    Location
    London
    MS-Off Ver
    MS 2010
    Posts
    4

    Re: Link, VLookUp, Choose and Concatenate?

    A B C D

    123 fvf M xyz
    456 fvfv asdg
    568 fvfvf M aher
    998 fvfvff M wefv
    567 fvfv erht


    (sorry for the bad representation, limited access at the moment) - so for this I would want Sheet 2 to only show rows 1, 3 and 4 as C has an M in them but without empty rows inbetween. If there is no M, the cell is empty, however an M could be added at a later date at which point I would want that to be part of Sheet 2 in the correct order (so if an M is added to Row2ColumnC I would want it to show in Row 2 in Sheet 2 and move former rows down by one).
    Reply With QuoteRegister To Reply
  6. 07-20-2015, 05:18 PM #6
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,053

    Re: Link, VLookUp, Choose and Concatenate?

    Better still, a non-array alternative with no helper...
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 07-20-2015, 05:46 PM #7
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Link, VLookUp, Choose and Concatenate?

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

    Thanks again for all your hard work here!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  8. 07-21-2015, 08:49 AM #8
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Link, VLookUp, Choose and Concatenate?

    Both Glenn's and my solutions do exactly what you are requesting.
    Reply With QuoteRegister To Reply
  9. 07-21-2015, 02:41 PM #9
    sxh678
    sxh678 is offline
    Registered User
    Join Date
    07-20-2015
    Location
    London
    MS-Off Ver
    MS 2010
    Posts
    4

    Re: Link, VLookUp, Choose and Concatenate?

    Hi! This was really helpful, and exactly what I needed. I went with the non-array with no helper, as the one with a helper column only gave me M0 values?

    My original sheet also has color fills, is there a way to incorporate that into the formula? So if ColumnA in Sheet1 has cells that are filled with different colors according to the Letter in them (G for Green, R for Red)?

    Thank you again, you two are great.
    Reply With QuoteRegister To Reply
  10. 07-21-2015, 02:55 PM #10
    sxh678
    sxh678 is offline
    Registered User
    Join Date
    07-20-2015
    Location
    London
    MS-Off Ver
    MS 2010
    Posts
    4

    Re: Link, VLookUp, Choose and Concatenate?

    Lastly, I am also experiencing a problem that the formula to link the two sheets always returns 0s when there is no value in the original sheet within that row . Any tricks regarding that?
    Reply With QuoteRegister To Reply
  11. 07-21-2015, 03:03 PM #11
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Link, VLookUp, Choose and Concatenate?

    Q1: There is no formula in Excel 2010 that can identify colors in a cell.

    Q2: You can look first with an IF statement (IF(formula = 0, "", formula) but that might get very long or you can format zeros not to show
    General;General;
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Choose + vlookup + if(iserror)
    By meckel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-15-2014, 04:48 PM
  2. [SOLVED] Link different workbooks (cells) and choose file through a cell content
    By wilver in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-05-2013, 01:14 PM
  3. [SOLVED] Is it possible to use concatenate to create a dynamic link to another worksheet?
    By ajava in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-21-2012, 01:55 PM
  4. [SOLVED] Vlookup & Choose
    By timbo1957 in forum Excel General
    Replies: 8
    Last Post: 03-22-2012, 10:21 AM
  5. Excel 2007 : When should I choose IF, AND, VLOOKUP?
    By Garethrowe in forum Excel General
    Replies: 1
    Last Post: 02-01-2011, 08:11 AM
  6. concatenate to create a cell link
    By wapita in forum Excel General
    Replies: 3
    Last Post: 02-01-2010, 06:34 AM
  7. [SOLVED] Vlookup or choose or?
    By Dean in forum Excel General
    Replies: 1
    Last Post: 01-12-2006, 02:25 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1