+ Reply to Thread
Results 1 to 16 of 16

Formulas and Merged Cells

  1. #1
    Registered User
    Join Date
    03-03-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    11

    Formulas and Merged Cells

    Hi All,

    First post.. I have an excel sheet with different tabs. I have a list of numbers on one sheet that moves onto another sheet. I have used this formula
    =CONCATENATE('Doc List'!G4,"",G51)

    The problem i have is the Formula goes into a merged cell. When i pull down the formula to the next merged cell it miss the next number and goes to the 5th cell. As the merged cell using 4 cells but 1 box.


    At the moment it does this
    =CONCATENATE('Doc List'!G4,"",G55)
    =CONCATENATE('Doc List'!G8,"",G55)
    =CONCATENATE('Doc List'!G12,"",G59)
    =CONCATENATE('Doc List'!G16,"",G63)

    I want it to ready like this below into merged cells
    =CONCATENATE('Doc List'!G4,"",G51)
    =CONCATENATE('Doc List'!G5,"",G55)
    =CONCATENATE('Doc List'!G6,"",G55)
    =CONCATENATE('Doc List'!G7,"",G55)

    I would like to know how i can correct this but need to keep the merged cells.. I have added an example to show what am talking about
    Attached Files Attached Files
    Last edited by NP2020; 03-09-2020 at 07:12 AM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,589

    Re: Formulas and Merged Cells

    Hi and welcome
    Merged cells are to be avoided whenever possible. Use "Center across selection" as formatting instead and try again

  3. #3
    Registered User
    Join Date
    03-03-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    11

    Re: Formulas and Merged Cells

    Thank you for your welcoming. Not really helpful. Sorry but as i have stated i need to keep the merged cells. I have attached the document for an example

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,102

    Re: Formulas and Merged Cells

    In C5

    =CONCATENATE(OFFSET('Sheet 1'!$G$4,INT((ROW()-ROW($C$5))/4),))

    In D5

    =CONCATENATE(OFFSET('Sheet 1'!$H$4,INT((ROW()-ROW($D$5))/4),))

    Copy down both.
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Registered User
    Join Date
    03-03-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    11

    Re: Formulas and Merged Cells

    Really appreciate your help!! Thank you

  6. #6
    Registered User
    Join Date
    03-03-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    11

    Re: Formulas and Merged Cells

    HI kvsrinivasamurthy

    Would you be able to help me again?

    From the formula you created above is there a way that it can ignore certain text. on Sheet 1 column J We would like to ignore anything that has Bid in that cell

    Thank you again for your help

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,102

    Re: Formulas and Merged Cells

    Pl upload a sample file showing the required result.

  8. #8
    Registered User
    Join Date
    03-03-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    11

    Re: Formulas and Merged Cells

    Hi kvsrinivasamurthy i have just uploaded the file on the main post up above

  9. #9
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,102

    Re: Formulas and Merged Cells

    In C6 then copied across.
    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    03-03-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    11

    Re: Formulas and Merged Cells

    Thank you again, your a star

  11. #11
    Registered User
    Join Date
    03-03-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    11

    Re: Formulas and Merged Cells

    Hey kvsrinivasamurthy

    I was wondering if you could help me out again with the formulas you gave me before.
    =IFERROR(INDEX('Doc List'!F$4:F$183,AGGREGATE(15,6,ROW('Doc List'!$F$4:$G$183)/( 'Doc List'!$I$4:$I$183<>"Not Sent"),1+INT(((ROW()-ROW($C$5)))/4))-ROW($G$4)+1),"")

    On my first tab

    I have all the information i need that i type in or use a drop down box to pick what i want. (This document is my testing document drop down boxes won't be on here)

    In the phase part(First Tab, Column I) I have sent or not sent, this transfer all the date to another sheet called Second Tab.

    On the Second tab it puts all the sent(First tab) dater into the table. But when i change Not sent to Sent, the text in the table(Second tab) all moves down. But i have other text in the table that needs to match that i have insert.

    It hard to explain but when you look at the document and change Not sent to Sent you everything moved down in the table apart from the right hand side of the table with text in. Can any help me fix this please.
    Attached Files Attached Files

  12. #12
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,102

    Re: Formulas and Merged Cells

    I am not clear what you want.Some examples are given below.It may help. Any further clarification is welcome.

    Not to select Not sent

    ( 'Doc List'!$I$4:$I$183<>"Not Sent")

    Not to select Sent

    ( 'Doc List'!$I$4:$I$183<>"Sent")

    To select Not sent

    ( 'Doc List'!$I$4:$I$183="Not Sent")

    To select Sent

    ( 'Doc List'!$I$4:$I$183="Sent")

    To select both Not sent and sent

    (( 'Doc List'!$I$4:$I$183="Not Sent")+( 'Doc List'!$I$4:$I$183="Sent"))
    Last edited by kvsrinivasamurthy; 10-06-2020 at 07:58 AM.

  13. #13
    Registered User
    Join Date
    03-03-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    11

    Re: Formulas and Merged Cells

    I have upload a new excle sheet, with comment inside to explain.
    Attached Files Attached Files

  14. #14
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,102

    Re: Formulas and Merged Cells

    Do you want formula in Column I.
    What is the meaning of "Should match with Test 1".
    In I5,I9,I13 etc what values in the first sheet required.

  15. #15
    Registered User
    Join Date
    03-03-2020
    Location
    London
    MS-Off Ver
    2016
    Posts
    11

    Re: Formulas and Merged Cells

    In the Frist tab Column F Text moves to Second Tab C
    In the Frist tab Column G Text moves to Second Tab Column D
    In Frist tab Column J Text moves to Second Tab Column B.

    They only move across when you turn Column I (First Tab, Phase) to Sent.

    As on the second Tab you see Test 1, Test 7, Test 8 & Test 14 in column D The text in columns F to N does drop down when you Add Test 2 in.

    To add Test 2 in, If you go to First Tab go to Column I line I5 turn it to Sent and go back to Second Tab everything moved down

    I have taken screen shoots for you
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,102

    Re: Formulas and Merged Cells

    F5 insecond tab gets value from which cell in first tab as you are telling it should match with Test 1.

+ 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. Alternating formulas with merged cells
    By rosethorn5 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-13-2015, 09:34 PM
  2. Formulas don't count merged cells
    By PinkMafia14 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-15-2015, 04:58 AM
  3. [SOLVED] Formulas with cells that are merged
    By greteberit in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-11-2014, 04:45 AM
  4. Replies: 0
    Last Post: 11-25-2014, 07:08 AM
  5. automatically fit an image into merged cells for full width of merged cells
    By Marcin4111 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 11-18-2014, 03:12 PM
  6. Replies: 1
    Last Post: 06-28-2012, 11:53 AM
  7. copy/paste formulas across merged cells?
    By juanzo007 in forum Excel General
    Replies: 2
    Last Post: 11-25-2010, 11:09 PM

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