+ Reply to Thread
Results 1 to 14 of 14

Set of cells (in 1 column) won't add formula when I insert row

  1. #1
    Forum Contributor
    Join Date
    10-23-2004
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    227

    Question Set of cells (in 1 column) won't add formula when I insert row

    Column A has product numbers (A1:A50)

    Column C & D have a formula.
    When I want to add a row somewhere between A1 - A50, the formula that I have in columns C & D don't get added in.

    What do I have to do in the Sheet, File, etc. so that if I add a row, the formulas that I have in C:C and D:D will be added to the new row?

  2. #2
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Set of cells (in 1 column) won't add formula when I insert row

    Perhaps create a table..??

    http://office.microsoft.com/en-001/e...010359200.aspx
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,968

    Re: Set of cells (in 1 column) won't add formula when I insert row

    By itself, excel wont "just add in formulas" from anywhere, you need to convert the table that you have to a TABLE, as suggested by Ace
    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

  4. #4
    Forum Contributor
    Join Date
    10-23-2004
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    227

    Re: Set of cells (in 1 column) won't add formula when I insert row

    I converted my table to a TABLE, and it still won't do it. I did remove the drop down filters too because I didn't/don't (at least I think) need it. Do I need to create a TABLE from scratch? Would it work if I convert an existing set of rows & columns as a cell? Did I need to leave the filters on on the header of the TABLE?

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,968

    Re: Set of cells (in 1 column) won't add formula when I insert row

    1 of the reasons the forum asks for the excel version you use, is so that members can tailor suggestions based on that. Please update your profile with the version you are using

  6. #6
    Forum Contributor
    Join Date
    10-23-2004
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    227

    Re: Set of cells (in 1 column) won't add formula when I insert row

    No problem. Profile updated.

  7. #7
    Forum Contributor
    Join Date
    10-23-2004
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    227

    Re: Set of cells (in 1 column) won't add formula when I insert row

    So does this mean that because I am using Excel 2010, when I convert a set of cells to a Table, it won't add the formula to the new row when I insert a row that's within the "TABLE" of cells?

  8. #8
    Forum Contributor
    Join Date
    10-23-2004
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    227

    Re: Set of cells (in 1 column) won't add formula when I insert row

    Anybody have this issue?

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Set of cells (in 1 column) won't add formula when I insert row

    Quote Originally Posted by JPWRana View Post
    So does this mean that because I am using Excel 2010, when I convert a set of cells to a Table, it won't add the formula to the new row when I insert a row that's within the "TABLE" of cells?
    Yes it will, but I believe there is a minimum # of rows that must PRE-EXIST with these formulas before that behavior will occur.
    I think the minimum is 3, but definately not more than 5.

  10. #10
    Forum Contributor
    Join Date
    10-23-2004
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    227

    Re: Set of cells (in 1 column) won't add formula when I insert row

    Is there a way to turn the pre-requisite off?

    When I did my "test" insert row, it was on about row 30. The formula for the cells in the table change only by shifting 1 cell downward. Does the formula have to be EXACT (down to the cell) even though I don't use F4 to fix the cells?

  11. #11
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,968

    Re: Set of cells (in 1 column) won't add formula when I insert row

    Im not sure that you can INSERT a (blank?) row into a table and have it copythe formulas down to that blank row. The way the table functions is, once you have it set up, if you add data to the 1st empty row after your last row of data, excel will combine that as a new (last) row in that table, and copy all the formulas to it

  12. #12
    Forum Contributor
    Join Date
    10-23-2004
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    227

    Re: Set of cells (in 1 column) won't add formula when I insert row

    Everywhere I would search (not just on excelforums) it would say "oh you need to turn that set of cells into a table" without any further explanation of what version of excel you need, how many cells need to have the formula, or any other restrictions.

    Is it then possible to... when inserting a row in between a TABLE, the formula(s) will copy down just like one uses the formula on A1 and drags it down to A100 (except without having to highlight and drag down)?

  13. #13
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,968

    Re: Set of cells (in 1 column) won't add formula when I insert row

    OK I just tested on a dummy file, and inserting a row INSIDE a table, does add the formulas

  14. #14
    Forum Contributor
    Join Date
    10-23-2004
    Location
    Los Angeles
    MS-Off Ver
    2010
    Posts
    227

    Re: Set of cells (in 1 column) won't add formula when I insert row

    Oh wow! I also opened up a brand new sheet and created a Table. It doesn't do it for me :-(
    Does anyone know of how to make my screen turn into video so then I can youtube upload it?
    That way I can be guided as to what I'm doing wrong so that I can have the formula added when I insert a row, just like FDibbins did.

+ 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