+ Reply to Thread
Results 1 to 5 of 5

Condicional concatenation using values on separate sheets

  1. #1
    Registered User
    Join Date
    09-19-2011
    Location
    Brisbane
    MS-Off Ver
    Excel 2003
    Posts
    2

    Question Condicional concatenation using values on separate sheets

    Hi all!
    I have been doing some research but can't figure this one out. I must say I'm an excel average user and know just the basics.

    The scenario is as follows:
    I have two sheets within the same excel file, let's call them A and B.
    In sheet A, I have colums 1, 2 and 3 .
    Column 1 has a fixed text that can be "D", "M" or "N"
    Column 2 has a drop down with options "G" and "B".

    These initials would stand for, day, morning, night, good and bad, and I have assigned this in sheet B.

    Now, is there a way to use the concatenate function (or any other that may be suitable) in order to concatenate words in colum 3 according to the values in 1 and 2?
    i.e.
    D, G --> column 3 should say "DayGood".
    M, B --> column 3 should say "MorningBad"

    I hope it makes sense to someone!
    Thanks in advance!
    Jon.

  2. #2
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Condicional concatenation using values on separate sheets

    Perhaps...
    =IF(OR(A1="",B1=""),"",CHOOSE(MATCH(A1,{"D","M","N"},0),"Day","Morning","Night")&CHOOSE(MATCH(B1,{"G","B"},0),"Good","Bad"))

    Beau Nydal

  3. #3
    Forum Moderator 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: Condicional concatenation using values on separate sheets

    Hi,

    I'm not sure about the relevance of Sheet 2. Perhaps

    Please Login or Register  to view this content.
    Regards

  4. #4
    Registered User
    Join Date
    09-19-2011
    Location
    Brisbane
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Condicional concatenation using values on separate sheets

    Hi Guys,

    Thanks a lot, that worked lilke a charm. One additional question now:

    When using this conditional formating, if the condition refers to a string:
    if (cellX="winner",good,bad)

    How can I get the "good" output, for any word in cellX starting with "win" and not only for the word "winner"?

    Thanks a lot again!

  5. #5
    Forum Contributor
    Join Date
    01-28-2010
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2003
    Posts
    157

    Re: Condicional concatenation using values on separate sheets

    One way would be to replace cellx="winner" with ISNUMBER(SEARCH("win",cellX))

    Beau Nydal

+ 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