+ Reply to Thread
Results 1 to 7 of 7

Need to allow users to insert a row to a table, but retain the formulas

  1. #1
    Registered User
    Join Date
    08-15-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    4

    Need to allow users to insert a row to a table, but retain the formulas

    I have created a table to enter order shipment data. Formulas are used in several columns H, J, and K. Users will need to insert new rows at the end of the table, this needs to be done whilst the new row uses the formulas in each column. I would like to use radio button to do this. This will save the user having to manually update the columns in each new row. I'm not an expert in VBA at all but I feel I could do this if I had step by step help. I've attached the workbook. Thank you!
    Attached Files Attached Files

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

    Re: Need to allow users to insert a row to a table, but retain the formulas

    BHi, welcome to the forum

    What am I missing here? If you are using a "structured table", then the formulas should be added automatically when you start a new row. There should be no need to a macro or anything else
    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
    08-15-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    4

    Re: Need to allow users to insert a row to a table, but retain the formulas

    Beats me. It is a structured table from which I've successfully created a few pivot tables. But when I insert a row, two of the columns don't copy down the formula. Actually Column "I" does copy down.

  4. #4
    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: Need to allow users to insert a row to a table, but retain the formulas

    column I is "shipping date", it contains no formulas, only entered dates.

    I notice that row 158 seems to have some sort of (extra?) filter to it. Delete that entire row, and when you start entering data into the 1st empty row (the new row 158), the formulas now copy down

  5. #5
    Registered User
    Join Date
    08-15-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    4

    Re: Need to allow users to insert a row to a table, but retain the formulas

    Thanks for all of your help! You're right, sorry wrong column. Any all seems to be working except for one column actually. When I insert a row column J should show the value in column H and the new row, instead it changes the formula. The formula should be =IF(AND(H113<>""), 1,) for row 114 but instead copies as this =IF(I114 =DATEVALUE, 1). If you just copy it down manually it renders fine!

  6. #6
    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: Need to allow users to insert a row to a table, but retain the formulas

    out of time right now, will look again soon

  7. #7
    Registered User
    Join Date
    08-15-2015
    Location
    Dallas, TX
    MS-Off Ver
    2013
    Posts
    4

    Re: Need to allow users to insert a row to a table, but retain the formulas

    Hi there. I wanted to report the problem solved. You were right all the formulas should have copied down without a problem What I realized though is anytime a formula is altered during the process of tweaking the spreadsheet, You have to convert the table to a normal data range. make the changes and reformat the data table. At least that is what worked for me. Hey thanks for all of your help.

+ 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] Insert table row not copying formulas correctly
    By dr01allen in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-24-2015, 11:38 AM
  2. Insert new line in a table with formulas
    By rowena229 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-26-2015, 02:26 PM
  3. Insert Word Document with a macro, retain original formatting
    By dwhims in forum Word Programming / VBA / Macros
    Replies: 2
    Last Post: 11-26-2013, 06:05 PM
  4. How to add new sheet and retain formulas
    By kevphil in forum Excel General
    Replies: 2
    Last Post: 03-15-2013, 01:19 PM
  5. Replies: 2
    Last Post: 12-18-2012, 05:22 AM
  6. Insert new row into invoice and retain formulas and formatting
    By korrigan in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-07-2010, 05:48 AM
  7. [SOLVED] retain table formatting when writing excel table to a txt file
    By deanop in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 09-03-2005, 01:05 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