+ Reply to Thread
Results 1 to 12 of 12

leaving blank cells blank in dragging formula, combining different formulas in one cell.

  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    Dar es salaam Tanzania
    MS-Off Ver
    2010
    Posts
    27

    leaving blank cells blank in dragging formula, combining different formulas in one cell.

    I can't modify my formula to leave blank cells blank when dragging it down, Also, I've got two formulas that i need to combine. Please view the comments I've put in cells E4, F2,F3,H2 and I2 to understand clearly what am seeking. See the attached worksheet.
    Attached Files Attached Files

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: leaving blank cells blank in dragging formula, combining different formulas in one cel

    Please see attached file with formulas.
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,540

    Re: leaving blank cells blank in dragging formula, combining different formulas in one cel

    Please find the attached sheet to see if this works as per your requirement.
    Note that col. H and col. I contain Array Formulas. Since these are array formulas to you need to confirm them with Ctrl+Shift+Enter instead of just Enter. Normally with the regular formula, you type the formula in the cell and press Enter to confirm the formula, but in case of an array formula, you need to hold down Ctrl+Shift together then press Enter to confirm it. When an array formula is entered correctly, you will notice in the formula bar that the formula gets surrounded by the Curly Brackets and if you don't see the curly brackets in the formula bar, press F2 (Function Key), hold down the Ctrl+Shift and press Enter to re-confirm the formula as an array formula.
    Attached Files Attached Files
    Last edited by sktneer; 08-02-2014 at 11:45 PM.
    Regards
    sktneer


    Treat people the way you want to be treated. Talk to people the way you want to be talked to.
    Respect is earned NOT given.

  4. #4
    Registered User
    Join Date
    06-26-2014
    Location
    Dar es salaam Tanzania
    MS-Off Ver
    2010
    Posts
    27

    Re: leaving blank cells blank in dragging formula, combining different formulas in one cel

    Thanks much mate, is the helper column necessary to be displayed? will the formulas work if I hide it?

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,904

    Re: leaving blank cells blank in dragging formula, combining different formulas in one cel

    Yes, you can hide it or you can also make column invisible (highlight the entire range, go to Format Cells, Custom and type in: ";;;" (without " ") This will make content invisible in the cells.

  6. #6
    Registered User
    Join Date
    06-26-2014
    Location
    Dar es salaam Tanzania
    MS-Off Ver
    2010
    Posts
    27

    Re: leaving blank cells blank in dragging formula, combining different formulas in one cel

    Wooow, thanks sktneer, you have saved me from a very time consuming worksheet with over 250 trucks, going through each truck was very boring, thanks much, n u too Alkey, thanks for your assistance guys, your time meant a lot to me.

  7. #7
    Registered User
    Join Date
    06-26-2014
    Location
    Dar es salaam Tanzania
    MS-Off Ver
    2010
    Posts
    27

    Re: leaving blank cells blank in dragging formula, combining different formulas in one cel

    Ok, thanks mate.

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: leaving blank cells blank in dragging formula, combining different formulas in one cel

    non array versions
    =IF(A2="","",SUM(OFFSET(E2,0,0,IFERROR(MATCH(TRUE,INDEX(A3:$A$10000<>"",0),0),10000)))) =IF(A2="","",SUM(OFFSET(F2,0,0,IFERROR(MATCH(TRUE,INDEX(A3:$A$10000<>"",0),0),10000))))
    "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

  9. #9
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,540

    Re: leaving blank cells blank in dragging formula, combining different formulas in one cel

    You're welcome. Thanks for the feedback.

  10. #10
    Registered User
    Join Date
    06-26-2014
    Location
    Dar es salaam Tanzania
    MS-Off Ver
    2010
    Posts
    27

    Re: leaving blank cells blank in dragging formula, combining different formulas in one cel

    Hello Guys, I'm having troubles pasting one the formulas in other columns. The formula is in G2 and I want to paste it in M2. Please see the attachment below.
    Attached Files Attached Files

  11. #11
    Forum Guru sktneer's Avatar
    Join Date
    04-30-2011
    Location
    Kanpur, India
    MS-Off Ver
    Office 365 ProPlus
    Posts
    9,540

    Re: leaving blank cells blank in dragging formula, combining different formulas in one cel

    Not sure exactly, what are you trying to achieve as I don't understand your comments in G2 and M2. Both the comments are same.

    If your formula in col. M relies on the cols. I and K instead of cols. C and E, in that case your formula should be like this........
    Please Login or Register  to view this content.
    If this is not what you are trying to achieve, please fill the desired output manually in col. M with an explanation that how do you expect that desired output there.

  12. #12
    Registered User
    Join Date
    06-26-2014
    Location
    Dar es salaam Tanzania
    MS-Off Ver
    2010
    Posts
    27

    Re: leaving blank cells blank in dragging formula, combining different formulas in one cel

    Thanks mate, it worked.

+ 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. Replies: 13
    Last Post: 02-05-2013, 09:29 AM
  2. Replies: 8
    Last Post: 01-28-2013, 01:10 AM
  3. Replies: 2
    Last Post: 07-28-2011, 07:00 AM
  4. Replies: 4
    Last Post: 05-27-2009, 09:34 AM
  5. leaving cell blank as a result of two blanks cells
    By Mister Big Shot in forum Excel General
    Replies: 3
    Last Post: 03-24-2007, 03:10 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