+ Reply to Thread
Results 1 to 8 of 8

Returning a value in another cell, when merged cells are being used.

  1. 07-09-2012, 05:20 AM #1
    Slyman
    Slyman is offline
    Registered User
    Join Date
    07-09-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    21

    Returning a value in another cell, when merged cells are being used.

    Hi, Slyman here and new to the forum.

    Have had several hours of fruitless youtube searching for clues to my predicament.

    I have a spreadsheet with multiple merged columns and am trying to make a pretty standard form into one which can be automated by adding a selection column to the options.

    I wish to have an 'X' placed into a column, which is adjacent to the value (letter) I wish to return into a different cell on the form.

    The form attached is something close to what I need to produce. I have tried match, if, index formulas, but cannot get them to work.

    Would appreciate some assistance. It may be so easy for some but I have lost hours over this.

    excel example.xls
    Reply With QuoteRegister To Reply
  2. 07-09-2012, 05:46 AM #2
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Returning a value in another cell, when merged cells are being used.

    Hi and welcome to the forum.

    Do you mean something like this in O5 and copy down?

    =IF(SUMPRODUCT(($A$5:$A$12=E5)*($B$5:$B$12=J5)*($C$5:$C$12="X"))>0,"X","")
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  3. 07-09-2012, 05:56 AM #3
    npamcpp
    npamcpp is offline
    Valued Forum Contributor
    Join Date
    04-30-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    547

    Re: Returning a value in another cell, when merged cells are being used.

    Don't use merged cells.

    Again.

    Don't. Use. Merged. Cells.

    Especially in your example: Why have a dozen columns with a narrow size and then merge cells to display legible text? What is the purpose of the narrow columns? Merged cells create more headaches than there is aspirin in a pharmacy. Don't use them.
    Like a post? Click the star below it!
    Reply With QuoteRegister To Reply
  4. 07-09-2012, 06:04 AM #4
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Returning a value in another cell, when merged cells are being used.

    oh just in case it doesn't get mentioned,don't us merged cells
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  5. 07-09-2012, 08:31 AM #5
    Slyman
    Slyman is offline
    Registered User
    Join Date
    07-09-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Returning a value in another cell, when merged cells are being used.

    Guys, I have tried to use the formula which Fotis1991 provided. I cannot get it to work.

    I have amended the spreadsheet, this time with cell references and my objective.

    Is this a nested 'if' using the 'sumproduct' function?

    any help, gratefully received.

    excel example2.xls

    cheers
    Reply With QuoteRegister To Reply
  6. 07-09-2012, 08:34 AM #6
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Returning a value in another cell, when merged cells are being used.

    =index(b5:b11,match("x",c5:c11,0))
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir
    Reply With QuoteRegister To Reply
  7. 07-09-2012, 09:06 AM #7
    Slyman
    Slyman is offline
    Registered User
    Join Date
    07-09-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Returning a value in another cell, when merged cells are being used.

    BANG!

    That's it!
    Thank you so very much!!

    So it's an index formula. I'll work with that.

    many many thanks.
    Reply With QuoteRegister To Reply
  8. 07-09-2012, 09:13 AM #8
    vlady
    vlady is offline
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2021
    Posts
    4,361

    Re: Returning a value in another cell, when merged cells are being used.

    welcome.
    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)

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