+ Reply to Thread
Results 1 to 10 of 10

Find first non-blank cell ABOVE formula cell in column. Possible without VBA?

  1. #1
    Registered User
    Join Date
    09-02-2013
    Location
    Segonzac (16), France
    MS-Off Ver
    Excel 2007
    Posts
    3

    Find first non-blank cell ABOVE formula cell in column. Possible without VBA?

    Hi everyone.

    I'm trying to create a worksheet which mimics a bank statement, so that the balance only appears against the last of several rows of entries for a single date.

    To calculate a new balance after the day's entries I need to find the first non-blank cell above the result of the calculation in the Balance column: this will be the old balance. I can cope with the formula for making cells blank for all but the last of the rows containing the day's entries but I'm stumped by the new balance calculation.

    I am reasonably comfortable working with formulae but I have absolutely no experience of VBA. Can I do what I'm trying to do without VBA?

    Thanks for your help.
    Last edited by RichardLynn; 09-04-2013 at 12:48 PM.

  2. #2
    Forum Contributor
    Join Date
    05-02-2013
    Location
    Indiana
    MS-Off Ver
    Excel 2016
    Posts
    190

    Re: Find first non-blank cell ABOVE formula cell in column. Possible without VBA?

    Richard, I think an example would be helpful. Could you post more information?
    redsab

  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: Find first non-blank cell ABOVE formula cell in column. Possible without VBA?

    Hi and welcome to the forum

    What comes to mind, is to compare "this" date with the 1 below, if it is the same, show nothing, if it is different, show the balance. And seeing as the entry below the last entry will be blank, that "date" will not be the samd as the last row's date - it will show the balance
    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

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find first non-blank cell ABOVE formula cell in column. Possible without VBA?

    Maybe something like this...

    Data Range
    A
    B
    C
    D
    1
    Date
    Debit
    Credit
    Balance
    2
    8/1/2013
    15000.87
    3
    8/1/2013
    4697.22
    19698.09
    4
    8/5/2013
    100.31
    5
    8/5/2013
    28.00
    6
    8/5/2013
    55.17
    19514.61
    7
    8/11/2013
    102.50
    19412.11
    8
    8/15/2013
    77.36
    9
    8/15/2013
    245.66
    10
    8/15/2013
    1003.24
    11
    8/15/2013
    4697.22
    22783.07

    D2 = opening balance

    Enter this formula in D3 and copy down:

    =IF(A3=A4,"",LOOKUP(1E100,D$2:D2)-SUMIF(A$3:A3,A3,B$3:B3)+SUMIF(A$3:A3,A3,C$3:C3))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  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: Find first non-blank cell ABOVE formula cell in column. Possible without VBA?

    Hey Biff, you really do love your new toy huh?

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find first non-blank cell ABOVE formula cell in column. Possible without VBA?

    You bet'cha!

  7. #7
    Registered User
    Join Date
    09-02-2013
    Location
    Segonzac (16), France
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Find first non-blank cell ABOVE formula cell in column. Possible without VBA?

    Hi Biff and Everyone

    Thanks for your help and I’m sorry about not including a sample worksheet.

    Biff, your suggestion does exactly what I want but I’m puzzled by a few things, (I’m starting to realise just how inexperienced I am!):

    First, in my copy of Excel 2007 your suggested formula “auto-corrects” to

    =IF(A3=A4,””,LOOKUP(1E+100,D$2:D2) ….. but it still works!

    I’ve looked at Microsoft Help for LOOKUP and I realise that this is the array form of the function but I’m sorry I don’t understand what the lookup_value in the formula, 1E100 (or 1E+100), is and why 1E+100 works and 1E100 doesn’t. I wondered if 1E100 might be a range name but 1E+100 works fine without me defining a range. I must be very dim!

    According to Microsoft Help LOOKUP is included for compatibility with earlier versions, the suggestion being that it is preferable to use VLOOKUP or HLOOKUP. What are your thoughts on this. I tried briefly to change the formula to suit but it didn’t work.

    Finally, I must say what a wonderful resource Excel Forum is and I’d like to thank everyone who makes this possible so that idiots like me have somewhere to turn to for help.

    Cheers everyone

    Richard

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find first non-blank cell ABOVE formula cell in column. Possible without VBA?

    Quote Originally Posted by RichardLynn View Post

    According to Microsoft Help LOOKUP is included for compatibility with earlier versions, the suggestion being that it is preferable to use VLOOKUP or HLOOKUP. What are your thoughts on this. I tried briefly to change the formula to suit but it didn’t work.
    LOOKUP is a very useful function. It works differently from the other H/VLOOKUPs. LOOKUP can do things the the other "flavors" can not.

    How it works in this application...

    1E100 (or 1E+100) is scientific notation or a "shorthand" method of expressing very long numbers.

    1E100 is the number 1 followed by 100 zeros. That is one gigantic number!

    We're using the LOOKUP function to find the last (bottom-most) number in a progressively increasing range.

    Consider this example:

    Data Range
    A
    B
    2
    22
    22
    3
    30
    30
    4
    30
    5
    18
    18
    6
    18
    7
    18
    8
    1000
    1000
    9
    10000
    10000
    10
    10000

    This formula entered in B2 and copied down:

    =LOOKUP(1E+100,A$2:A2)

    The breakdown is as follows:

    B2 = the last number in A2:A2
    B3 = the last number in A2:A3
    B4 = the last number in A2:A4
    B5 = the last number in A2:A5
    B6 = the last number in A2:A6
    B7 = the last number in A2:A7
    B8 = the last number in A2:A8
    B9 = the last number in A2:A9
    B10 = the last number in A2:A10

    If the lookup_value (1E+100) is greater than EVERY number in the referenced range then the LOOKUP function will return the last (bottom-most) number in the referenced range. To ensure that this happens we use a gigantic number that is guaranteed to be greater than any number in the referenced range. Since I don't know the typical number values that you deal with I use the arbitrary gigantic number 1E100 as the lookup_value.

    In fact, the lookup_value simply needs to be greater than any number in the referenced range. So, if we know that the numeric values we're dealing with will never be greater than 100, then we just need a lookup_value that is greater than 100. Like, 101:

    =LOOKUP(101,A$2:A2)

    I like to use the bowling score example.

    We know that the maximum possible bowling score is 300. So, if we wanted to know our most recent score we could use 301 as the lookup_value:

    =LOOKUP(301,A$2:A20)

    The numbers (bowling scores) in the range can not possibly be greater than 300 so all we need as the lookup_value is any number greater than 300 like 301.

  9. #9
    Registered User
    Join Date
    09-02-2013
    Location
    Segonzac (16), France
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Find first non-blank cell ABOVE formula cell in column. Possible without VBA?

    Biff, thank you for such a detailed response.

    I realise the forum is meant to be a medium for learning from solutions posted by other members and not a computer based training system so I'm extremely grateful to you for taking the time to reply with an actual lesson rather than a bare solution. Thank you so much.

    Cheers

    Richard

  10. #10
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Find first non-blank cell ABOVE formula cell in column. Possible without VBA?

    You're welcome. Thanks for the feedback!

+ 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. Find blank cell, insert formula to find median of above cell range
    By lilyeye in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-05-2013, 11:58 AM
  2. [SOLVED] find first non-blank cell in column
    By drfarmkid in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-05-2012, 02:01 AM
  3. How to find the next first blank cell after the last cell used in the column?
    By Beginner Level in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 05-14-2012, 08:42 PM
  4. macro to find first blank cell in a column cut the value from left adjacent column
    By willykin in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-01-2012, 09:23 AM
  5. Find first blank cell in column if rightmost cell is non blank
    By Mothman in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 09-19-2010, 02:22 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