+ Reply to Thread
Results 1 to 21 of 21

Formulas in table will not copy as table grows

  1. #1
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Formulas in table will not copy as table grows

    I have a table with some formulas that does not copy all of the formulas when I click on the Tab with the cursor in the last cell. I am at a loss as to what might the problem be?

    The attached file is a sample and it is doing the same thing as my original file. Place the cursor in the last cell in column AA, click and the next row is added but not all of the formulas. Any suggestions?

    Jim O
    Attached Files Attached Files
    Last edited by JO505; 08-05-2013 at 07:14 PM.

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Formulas in table will not copy as table grows

    I think the file is corrupt.

    I had to end the session of excel otherwise the file didn't close.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    08-04-2013
    Location
    Davis, CA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Formulas in table will not copy as table grows

    I am getting a lot of unresolvable references when I Tab, so I can't really determine what is a problem and what is an artifact.

  4. #4
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Formulas in table will not copy as table grows

    oeldere & Mike, Thanks to both of you for your responses,

    The file seems to work fine, I have been using it for a couple of years now but I keep adding to it.

    I have made tables in the file on other sheets in the same file and I have hade no issues. This doesn't appear to be a problem, at least not yet. At worse it's just annoying. I only enter data once a day in this sheet and I just enter the row and copy the formulas manually.

    Any ideas on how to proceed from here? Just ignore it or will (may) it cause problems down the line?

    Jim O

  5. #5
    Registered User
    Join Date
    08-04-2013
    Location
    Davis, CA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Formulas in table will not copy as table grows

    I am always leery of unexplained behaviors. There are a lot of debugging options on the Developer ribbon, some I have used more than others. I would try to identify what makes the formulas that are no longer appearing "special". I might even attempt to move something from a working sheet to a pristine test environment (new workbook) so that I can eliminate settings and formatting that might be causing issues yet are hard to detect.

    One thing that comes to mind is that when moving some formulas the references include old file folders hierarchies that may have changed.

    Good luck.

  6. #6
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formulas in table will not copy as table grows

    @JO505

    See if the attached file acts any differently for you...

    If okay, what I did was recreate the table in a new workbook.

    With Tables, you have to be careful to not turn off "autofill formulas" in the popup menu... Excel is pretty skittish about providing a means to turn it back on.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Formulas in table will not copy as table grows

    jhren,

    Your file works fine but the file I am working with has nearly 400 rows. How do I check to see if I inadvertently turned off a switch? I assume it is through the file tab, options .... area. I don't want to just go and flip switches with no idea of what I am doing.

    Jim O

  8. #8
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formulas in table will not copy as table grows

    Jim...

    See if this gets you there...

    http://office.microsoft.com/en-us/ex...#_Toc295810736

  9. #9
    Registered User
    Join Date
    08-04-2013
    Location
    Davis, CA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Formulas in table will not copy as table grows

    This is what I get in the formulas and it will not resolve for me:

    Please Login or Register  to view this content.
    As well as some that refer to sheets 'Fluid Log' and 'Blood Pressure' - so I really haven't been able to evaluate it.
    Last edited by MikeNDavis; 08-04-2013 at 08:21 PM.

  10. #10
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formulas in table will not copy as table grows

    @Mike

    You can negate that problem a couple ways...

    When you download and open the file you should be getting two warnings, the first is Protected View... ; the second is Security Warning.. automatic update of links disabled. Click "Enable Content" button. An app' dialog should appear about links. Click "Edit Links..." option. Edit Links dialog appears. Click Break Link.... and Break Links in message then opt out of Edit Links dialog.

    If you are already past that point, Data ribbon, Connections section, click Edit Links. Proceed as above.

    As for getting the Table to autofill again, insert new row below (only available from bottom row). If you didn't break links, cancel out of all the Update Media dialogs. Select row above, Copy, go back to bottom row and Paste Formulas (same routine if links not broke). Their should be a Table icon for the leftmost cell. Click and select only option...

  11. #11
    Registered User
    Join Date
    08-04-2013
    Location
    Davis, CA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Formulas in table will not copy as table grows

    Thanks for the pointers - once I break links as you suggested I get autofill for all the columns where there are formulae in the cells above. By breaking links columns 22 to 26 no longer have only #VALUE! and #REF!. I am pressing Tab when in the lower right corner of the table to get the next row.

  12. #12
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formulas in table will not copy as table grows

    Quote Originally Posted by MikeNDavis View Post
    ... I am pressing Tab when in the lower right corner of the table to get the next row.
    When I do that, I get the next row, left most bottom cell selected... but no formulas autofilled.

  13. #13
    Registered User
    Join Date
    08-04-2013
    Location
    Davis, CA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Formulas in table will not copy as table grows

    I have just found this old post on this message board - which I am afraid is a little cryptic, maybe it will mean more to you

    "Formula autofill problem when new row created in table"

  14. #14
    Registered User
    Join Date
    08-04-2013
    Location
    Davis, CA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Formulas in table will not copy as table grows

    I am starting to think that Excel is actually assessing the formulas in a column to determine if the formulas consistently change for each row. I have been able to create a situation where when the formulas are slightly modified as you come down the column the autofill uses =1 rather than the formula. To test this I would make sure that the first row has valid formulas , especially in those columns which are not autofilling. Create a table with only that row and the headers and see if it won't propagate new rows correctly. I did find a discrepancy in columns 15 and the one labelled 92 (between 9 and 10).

  15. #15
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Formulas in table will not copy as table grows

    I re-entered the data into a new sheet and eliminated all of the unnecessary formulas and it still won't copy all of the formulas, see columns K,L & M in the attachment.

    I have made other tables in the same workbook and they all work fine, for some reason their is a problem with this one.

    Jim O
    Attached Files Attached Files

  16. #16
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formulas in table will not copy as table grows

    Jim O...

    Excel is finicky on the formulas all being [relatively speaking] the same.

    The formulas in K, L, and M all reference cells above them. When I do the paste formulas "trick" to get the Table menu icon and invoke the overwrite all cells in this column with this formula, the formulas at the top of the column end up with #REF in the formula because the relative cells do not exist.

    Take for instance (after testing and creating a few rows to figure this out), part of the formula in K349 is G348-G345. When the same formula is copied all the way up to K4, the formula part becomes G3-#REF, because there is no G0. It doesn't show a cell error because it's the true argument of an IF function, for which the condition evaluates to FALSE.

    What I've done as a workaround in these situations is use an argument which will not error the references. For example, in K4 I replaced the mentioned part with IF(ROW()<7,CHOOSE(ROW()-3,0,$G$4,$G$5-$G$4),G3-INDIRECT("G"&ROW()-3), then copied down to bottom. After that, when I add a new row to the table, column K autofills the formula.

    *doubt that's exactly how you want it evaluated... just an example!!!
    Last edited by jhren; 08-05-2013 at 02:42 PM.

  17. #17
    Registered User
    Join Date
    08-04-2013
    Location
    Davis, CA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Formulas in table will not copy as table grows

    Very interesting. I made the changes that jhren suggested in Col K and I still didn't get the autofill to work. I copied and pasted the column into J, which had been exhibiting the autofill property and it worked, so it appeared to be an artifact of the column and not the formula (or both). This was consistent with an earlier experiment when I took a working autofill column (j) and copied it into K where it stopped working. I eliminated the first column but that did not seem to have an effect. I may be overlooking something or doingit improperly, but these are my results.

  18. #18
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Formulas in table will not copy as table grows

    jhren,

    In playing around with this table, in the back of my mind I was thinking along similar lines, that the formulas could be an issue. I noticed that some of the cell references were an issue and I was trying to come up with a workaround solution using helper columns, which worked okay until I tried to put it into a "Table". So now the question is how to proceed? Worse case as I see it is I just copy the few formulas down as new data is entered (one or two daily). The other solution is to put a formula into the table that meets all of the criteria. I would prefer the latter both as a learning situation and that is just the way it should work.

    What I want to do is to return the change in weight from 1 day ago, 3 days ago and 7 days ago. I would like to look at the current date and find the weight value for yesterday and 3 days ago as well as a week ago, as shown by date, not 1, 3 or 7 rows above. I di this in my original sheet because I was not sure it could be done any other way or if it was possible I had no idea of how to go about it.

    One problem is that not all dates are concurrent and in a few cases their is more than one entry for the same date. If there is no data available as in the first few rows (gaps of months or years) a blank or some null value is acceptable. In those cases with multiple data for the same date, the closest date will do. i.e. If we are looking for the weight form 3 days ago, and an entry is made in row 50 on Mon and all of the dates are sequential any of the values from 3 days ago is fine weather it is in row 47 or46. I have been using it as it is for a while now and anything will be an improvement.

    I tried your formula and it seems to work but as you said, not quite how I wanted to evaluate the data.

    I hope this is clear enough, if not let me know.

    Thanks

    Jim O

  19. #19
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formulas in table will not copy as table grows

    Added 6 columns to right of table. Sheer speculation on my part. Worst case, food for thought
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    05-10-2011
    Location
    Central Ohio
    MS-Off Ver
    Excel 2000, and 2010
    Posts
    654

    Re: Formulas in table will not copy as table grows

    jhren,

    I think that does the trick. I have just started to work with "Tables" and I had a bit of trouble trying to copy and paste from one workbook to another but I got it. Now to digest just what is going on in that formula.

    Thank you very much for your help with this, and thanks to all who added their $0.02.


    Thanks all

    Jim O

  21. #21
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Formulas in table will not copy as table grows

    Here's a condensed explanation of the formulas...

    First, I used the Index/Match method of lookup. The 1, 3, and 7 column indexes the Date column of the Table, and so indicated by the structured names (automatically used by Excel when selecting target Table columns and same row cells). The first Match looks for an exact match for the row date minus 1, 3, and 7 [days] respectively. If matched, returns row number for the index function to return the value, which is then subtracted from the row date. The LOOKUP or VLOOKUP functions could have been used for this, also. Next, the Iferror function wrapper kicks in if no exact match is found, using the next Index/Match argument, which is identical except the match is set for type 1 lookup, which is nearest and greatest value found that is the less than the lookup value (i.e. the closest date before 1, 3, and 7 days, respectively)... and again subtracted from row date.

    The Day(s)1, -3, and -7 columns use the same formulas except the indexed column is the "dressed" column, returning the weight difference between row weight and matched date weight (i.e. gain/loss).

+ 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: 1
    Last Post: 06-21-2013, 05:48 AM
  2. Create New row in a table and copy/update formulas VBA
    By whitenoise22 in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 07-25-2012, 03:07 AM
  3. Copy formulas outside pivot table
    By MARKSTRO in forum Excel General
    Replies: 3
    Last Post: 03-15-2012, 07:42 PM
  4. Replies: 2
    Last Post: 05-19-2010, 05:14 AM
  5. pivot table to ingore formulas in data table
    By nicko54 in forum Excel General
    Replies: 0
    Last Post: 07-10-2008, 10:30 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