+ Reply to Thread
Results 1 to 2 of 2

Empty cell, no formula, no Macro, no formulas referencing cell - Automatically fills up wi

  1. #1
    Registered User
    Join Date
    10-13-2012
    Location
    Zagreb, Croatia
    MS-Off Ver
    Excel 2007
    Posts
    1

    Angry Empty cell, no formula, no Macro, no formulas referencing cell - Automatically fills up wi

    I really hope someone understands how can this work (file attached).
    So, cell is empty in Col A (like A7), and when numbers are entered in all cells B7:E7, excel copies formula from cell above (B6).
    At first I thought - right, easy to do with VBA, but... There are no macros, nothing in worksheet code, there are no custom functions, and now for few hours can't figure out how is Excel doing this. It is not tables, I can tell that (red one similar post).

    Also, try deleting A7 to E7 contents, and then enter numbers again in B7 to E7, A7 gets formula from cell above automatically. I copy/pasted whole range to another Workbook, and it works too.

    This looks really cool, the only thing is I can't understand how it works and how to replicate it.
    Any feedback or thinking would be appreciated,
    Tom
    Attached Files Attached Files

  2. #2
    Forum Expert Moo the Dog's Avatar
    Join Date
    09-28-2012
    Location
    Wisconsin
    MS-Off Ver
    Office 365 (PC & Mac)
    Posts
    1,845

    Re: Empty cell, no formula, no Macro, no formulas referencing cell - Automatically fills u

    It's because there are forumlas in cells A3:A6, and when you enter similar data in succeeding rows, Excel copies the same formula down for the new row.

    If you delete all of the data in cells B3:E6, what will remain are 0's in A3:A6 (=B3+C3+D3+E3, =B4+C4...)

    Sorry to spoil the magician's tricks.

    - Vince
    Last edited by Moo the Dog; 10-14-2012 at 12:26 AM.

  3. #3
    Registered User
    Join Date
    10-13-2012
    Location
    Zagreb, Croatia
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Empty cell, no formula, no Macro, no formulas referencing cell - Automatically fills u

    Thanks Vince, I thought the same, that's ok...formula does what it supposed to, but to fully understand what I am talking about, please do like this:
    1. Clear A6 cell formula - so, no more formula to Sum cells on the right
    2. Clear B6
    3. Enter some number in B6
    - same formula shows up in A6 - the one you just deleted in step 1

    Or, in attached file, check content of A7 cell - nothing. Then enter some number to B7 and formula shows up in A7.

    See what I am talking about? What created that formula from nothing again without VBA?
    Regards
    Tom
    Last edited by Cutter; 10-14-2012 at 12:03 PM. Reason: Removed whole post quote

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

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