+ Reply to Thread
Results 1 to 11 of 11

How is the formula automatically get filled up here?

  1. #1
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    29

    How is the formula automatically get filled up here?

    Check attached excel sheet Test.xlsx

    - Goto G12 -> it's an empty cell, doesn't contain any formula
    - Now go to E12 - fill in a number - say 15000.
    - Go to F12 - fill in another number - say 1550.

    As soon as you press tab or enter after filling the number at F12, G12 automatically gets populated with the formula copied from above & shows the value.

    I am very happy that this happens but it doesn't happen in other excel worksheets I have seen. So how to activate this feature on a column?
    Attached Files Attached Files
    Last edited by ExcelBoy90; 07-01-2020 at 02:42 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: How is the formula automatically get filled up here?

    No, not happening here.

    Excel 2016 (Windows) 32 bit
    E
    F
    G
    7
    10000
    1005
    10.05%
    8
    11000
    1105
    10.05%
    9
    12000
    1210
    10.08%
    10
    13000
    1315
    10.12%
    11
    14000
    1425
    10.18%
    12
    15000
    1550
    Sheet: Sheet2

    This usually happens if the range has been converted to a table.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    29

    Re: How is the formula automatically get filled up here?

    Hmm, it still happens for me if I download it back from my post & test it.

    If it were because of range converted to table, it will happen for you also, right?

    It's also not some property turned on globally for me because if I create new data like this, it doesn't happen. It only happens in this sheet or if I copy/paste this range of cells into another sheet.

    I am running of Office Home & Student 2016.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: How is the formula automatically get filled up here?

    Where did you get the sheet from? The range is not a table - I checked that.

  5. #5
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    29

    Re: How is the formula automatically get filled up here?

    It's from a sheet I made with lots of columns. I noticed this behaviour in only one of the 25 columns in that sheet. So I copied that column & the 2 columns it refers to a fresh sheet & I could still repro the behaviour. The other columns in the original sheet with similar formulas don't behave this way.
    Last edited by AliGW; 07-01-2020 at 03:46 AM. Reason: Please don't quote unnecessarily!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: How is the formula automatically get filled up here?

    I vaguely remember a similar thread some time ago, but can't find it now. It will have to remain a mystery, I think.

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.

  7. #7
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,011

    Re: How is the formula automatically get filled up here?

    The other suggestion is is the original file xls or xlsm. If so some vba code could be populating the cell. But in saving the example as xlsx you remove the vba

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: How is the formula automatically get filled up here?

    I wondered that, but the comments in post #3 seem to negate it.

  9. #9
    Registered User
    Join Date
    12-17-2019
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    29

    Re: How is the formula automatically get filled up here?

    Quote Originally Posted by davsth View Post
    The other suggestion is is the original file xls or xlsm.
    It's xlsx. There is no VBA code in it.

  10. #10
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    2010
    Posts
    2,011

    Re: How is the formula automatically get filled up here?

    Ok in 2010 so can not speak for your version in file_options_advanced_editing options there is an extend data range formats and formulas, I suspect this it ticked
    Attachment 685040

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    Office 365 (Win 10 - Work) & Office 365 Subscription Insider (Win 10 - Home)
    Posts
    39,454

    Re: How is the formula automatically get filled up here?

    Well spotted, Dave - that makes the difference here.

+ 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. [SOLVED] Cells automatically filled
    By Bluesound in forum Excel General
    Replies: 3
    Last Post: 11-25-2012, 04:15 PM
  2. Replies: 2
    Last Post: 10-11-2012, 04:49 PM
  3. Replies: 7
    Last Post: 11-09-2011, 11:42 AM
  4. Automatically filled in form
    By Xathrax in forum Excel General
    Replies: 4
    Last Post: 11-17-2010, 06:53 AM
  5. Replies: 1
    Last Post: 10-29-2009, 02:30 AM
  6. Automatically count how many boxes are filled in
    By Feline2001 in forum Excel General
    Replies: 2
    Last Post: 11-10-2008, 09:38 PM
  7. Replies: 2
    Last Post: 09-29-2006, 07:04 AM
  8. Replies: 2
    Last Post: 11-15-2005, 07:25 AM

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