+ Reply to Thread
Results 1 to 4 of 4

Table formulas replication

  1. #1
    Registered User
    Join Date
    06-10-2012
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2016
    Posts
    28

    Question Table formulas replication

    Tables can be a godsend when I have to build large grids where column values are derived from other table columns. Each formula is so clearly referenced. I go the extra mile trying to set formulas so every row in a column gives the proper result using exactly the same formula.
    This is all nice and great when Excel works as intended, automatically replicating formulas and formatting for every table column as soon as I start a new row.

    But all is not sunny on one of my largest tables. I have columns with matrix formulas, regular formulas, values.

    For some unknown reason, some columns won't get populated with formulas when I add a new row...or will not inherit the same formatting (i.e. date or % format) as the cell above.

    So first question: how do I tell Excel that every cell in a specific table column should be kept with the same formula and formatting?

    To make matters more complex, I have one column where I would like to have unique formulas on each cell (i.e. A1=NOW(); A2=NOW()+7; A3=NOW()+14.....). But on this particular column, Excel insists on keeping consistent formulas, and won't allow me to enter unique ones.

    So second question: how do I tell Excel that cells in a specific table column are to be unique, and to stop copying the formula to all cells in the column enter every time I enter a change to a specific cell or create a new record/row?
    Last edited by Dominicus; 11-04-2016 at 02:41 AM.

  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,926

    Re: Table formulas replication

    1. Have you entered/deleted anything manually in a column where you have formulas above? This can mess with Table's formatting

    2. Rather than a bunch of NOW's, which are volatile and can slow your file down, if used excessively, put that function in its own cell off to the side somewhere and reference it.
    Also, if you only need a date (I have a feeling thats what you want) and not time, use TODAY() instead of NOE
    If all you want to do is add a week (7 days) to the date above, try =TODAY()+(ROW(A1)-1)*7 (remember to put TODAY in it;s own cell and reference it)
    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
    Registered User
    Join Date
    06-10-2012
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2016
    Posts
    28

    Re: Table formulas replication

    1. Have you entered/deleted anything manually in a column where you have formulas above? This can mess with Table's formatting
    My table has blocks of 20 inter-related columns which provide a result on the right-most column. I will then add an additional block of 20 columns, and copy/paste the formulas from the prior block.
    Once I copy the formulas, I do a search-replace-all to update the column references.

    I'm not manually entering each formula. But, yet, the formatting and auto-fill settings have gotten messed up. How do I un-mess them?


    use TODAY() instead of NOW
    Whichever function I choose, it doesn't seem to matter. This column is doing its auto-fill thing very well, and just updates every cell in the column with the same formula as soon as I edit any row in the column.

    How do I make it stop synchronizing all cells, just in this particular table column?

  4. #4
    Registered User
    Join Date
    06-10-2012
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2016
    Posts
    28

    Re: Table formulas replication

    I'm on to something:
    When I do some changes to a cell, I sometimes get a floating icon with a lightning bolt. Opening this allows me to set (or unset) the automatic formula synchronization for the column I'm in.

    But I still have not figured out how I can select an existing column and view (and possibly change) this setting for a group of columns (or even one-by-one).
    Any ideas if there's a menu-path to access this?

+ 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] replication
    By weidlh46 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-30-2013, 02:29 PM
  2. Automatic Replication
    By Dr Stupid in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 02-08-2008, 09:05 AM
  3. [SOLVED] Replication of Excel formulas [automatic cell update feature]
    By Generic Usenet Account in forum Excel General
    Replies: 4
    Last Post: 03-22-2006, 09:50 AM
  4. record replication ?
    By qhorse in forum Excel General
    Replies: 0
    Last Post: 08-02-2005, 05:12 AM
  5. [SOLVED] Excel self replication - help !
    By Gniff! in forum Excel General
    Replies: 5
    Last Post: 05-12-2005, 10:06 AM
  6. [SOLVED] Excel self replication - help !
    By Gniff! in forum Excel General
    Replies: 1
    Last Post: 05-08-2005, 06:06 PM
  7. #name? error replication - HELP!
    By caerus in forum Excel General
    Replies: 0
    Last Post: 04-28-2005, 09:33 AM
  8. Replication of formula
    By Ken Scanlon in forum Excel General
    Replies: 9
    Last Post: 02-20-2005, 02:33 PM

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