+ Reply to Thread
Results 1 to 6 of 6

Referencing all cell of merged cell as individual

  1. #1
    Registered User
    Join Date
    02-01-2021
    Location
    Ahmedabad
    MS-Off Ver
    2013
    Posts
    4

    Exclamation Referencing all cell of merged cell as individual

    I am facing problem while referencing all cell of merged cell as a individual cell, because its take first col first row of merged cell as reference cell in any formula.

    Ex. in Attachment example case i have some data in column A, I have formula in Column B as RC[-1], and result showing as column B, but i want output as column C.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Referencing all cell of merged cell as individual

    Here's the best advice anyone can give: do NOT used merged cells. Get rid of them.

    In B2 copied down:

    =IF(A2="",B1,A2)
    Last edited by AliGW; 09-23-2021 at 10:01 AM.
    Ali


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

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Registered User
    Join Date
    02-01-2021
    Location
    Ahmedabad
    MS-Off Ver
    2013
    Posts
    4

    Re: Referencing all cell of merged cell as individual

    Thank You madam!!, but in detail sheet I have blank cell also in Column D as per attested excel TEST CASE 2. i am getting output as per Column E and I want Output as per column F.
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Referencing all cell of merged cell as individual

    Well, that’s a completely new sample sheet - I could not have known that you also have completely blank cells from your original sample. Did you expect me to guess this?

    My original advice remains the same: ditch the merged cells.

  5. #5
    Registered User
    Join Date
    02-01-2021
    Location
    Ahmedabad
    MS-Off Ver
    2013
    Posts
    4

    Re: Referencing all cell of merged cell as individual

    Mam, You are absolutely Right, that in my previous example i did not mentioned that i have also some blank cells in list, and You obviously not guess that...

    and mam, the requirement of merged cell is there because there is a box of some items, so we can not remove it...

    please guide for any better solutions, which consider blank cell also and i can get output as per column F, it will becomes a great help to me...

    Thanks In Advance

    Paras

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: Referencing all cell of merged cell as individual

    Sorry - I made the decision some time ago not to help where merged cells were non-negotiable. There is NEVER a good or legitimate reason for using them.

    Good luck.

+ 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. VBA for changing cell colour (individual and merged)
    By Phenomi in forum Excel General
    Replies: 2
    Last Post: 08-31-2020, 12:17 AM
  2. Referencing an Individual Cell in a Closed Workbook Excel 2010
    By kschmit1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-24-2017, 11:17 AM
  3. HLOOKUP referencing a merged cell coming up with 0
    By Ron Fisher in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-03-2013, 04:39 PM
  4. lookup value when referencing a merged cell
    By lwflip in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-20-2012, 01:09 AM
  5. referencing value from a cell that was merged
    By aretai in forum Excel General
    Replies: 1
    Last Post: 07-16-2010, 08:09 AM
  6. Referencing one cell to individual cells in a large set
    By mremmenga in forum Excel General
    Replies: 2
    Last Post: 10-08-2008, 06:49 PM
  7. [SOLVED] Referencing a Merged cell
    By Mariano in forum Excel General
    Replies: 4
    Last Post: 01-31-2006, 10:35 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