+ Reply to Thread
Results 1 to 8 of 8

Alternating formulas with merged cells

  1. #1
    Registered User
    Join Date
    08-03-2015
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    38

    Exclamation Alternating formulas with merged cells

    Hello,

    I am in need of a couple formulas that allows me to alternate between odd and even cells that contain a merged cell within the formulas.

    1) Every two rows in the F column is a merged cell. Column H is not merged. I need my formula to multiply every value in the H column by a value in the F column. Below is an example of how I would need my formulas to be down "I":

    =F17*H17
    =F17*H18
    =F19*H19
    =F19*H20
    =F21*H21
    =F21*H22

    and so on.....

    I tried to do the formula =IF(ISODD(ROW()),F17*H17,F18*H18) but every even row would select the "blank" row of the merged cell in column F, instead of selecting the value of the merged cell.


    2) Similarly, both column D and column F are merged cells, whereas column K is not merged. Below is an example of how I would need my formulas to be down "L":

    =D17*K17
    =F17*K18
    =D19*K19
    =F19*K20
    =D21*K21
    =D21*K22
    and so on...

    Please help! I need this by tomorrow morning Pacific time.

    Thank you in advance!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Alternating formulas with merged cells

    Quick answer - don't use merged cells
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Alternating formulas with merged cells

    Try this...
    F
    G
    H
    I
    17
    1
    10
    10
    18
    20
    20
    19
    2
    30
    60
    20
    40
    80
    21
    3
    50
    150
    22
    60
    180
    23
    4
    70
    280
    24
    80
    320

    I17=IF(F17="",F16,F17)*H17
    copied down

    But seriously, avoid using merged cells if at all possible, they cause all sorts of problems with formulas

  4. #4
    Registered User
    Join Date
    08-03-2015
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    38

    Re: Alternating formulas with merged cells

    Thank you! That seems to work !! What about the second part of my question?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Alternating formulas with merged cells

    hmm you give me a negative rep with a comment like "Really unhelpful and rude." and expect further help?

    My initial suggestion was neither unhelpful, nor rude - it is the advice that ALL experienced excel users give, regarding the use of merged cells

  6. #6
    Registered User
    Join Date
    08-03-2015
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    38
    Quote Originally Posted by FDibbins View Post
    hmm you give me a negative rep with a comment like "Really unhelpful and rude." and expect further help?

    My initial suggestion was neither unhelpful, nor rude - it is the advice that ALL experienced excel users give, regarding the use of merged cells
    My apologies, FDibbins. I didnt realize both responses were by the same person.. and that reputation was given before you had responded a second time with a helpful answer. It was unncessary and unhelpful to tell me to just not use merged cells. You dont think I already tried that? Hence the thread on this forum.. people come here to get help. It obviously is possible, since you gave me an actual answer. Just because it is a consensus doesnt mean I shouldn't still try to if it's possible.

    I did try to remove the reputation and give you a new, good one.. but it wouldnt let me. If there is a solution to a problem, regardless of whether your advice is opposing or not, perhaps you could hold back on the unnecessary comment in the future and just respond with the answer. Nonetheless, I am sorry and I do not expect further help from you.

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Alternating formulas with merged cells

    Perhaps the same met6hod, just with adjusted references?
    =IF(D17="",D16,D17)*K17

  8. #8
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Alternating formulas with merged cells

    Rosethorn, I've removed your ungrateful neg rep given to FDibbins. Please consider that there will virtually never be a cause for you to negatively flag anyone on the forum, ever. If someone offers advice you already know or don't want, say thank you, and move on. Everyone here helps for free out of the goodness of their heart, keep that in mind at all times.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ 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. Formulas don't count merged cells
    By PinkMafia14 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-15-2015, 04:58 AM
  2. [SOLVED] Formulas with cells that are merged
    By greteberit in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-11-2014, 04:45 AM
  3. Replies: 0
    Last Post: 11-25-2014, 07:08 AM
  4. 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
  5. Replies: 1
    Last Post: 06-28-2012, 11:53 AM
  6. copy/paste formulas across merged cells?
    By juanzo007 in forum Excel General
    Replies: 2
    Last Post: 11-25-2010, 11:09 PM
  7. how do i link merged cells to a merged cell in another worksheet.
    By ibbm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-27-2006, 06:45 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