+ Reply to Thread
Results 1 to 15 of 15

How do I maintain formula in last row when table expands?

  1. #1
    Registered User
    Join Date
    07-15-2022
    Location
    Delta Canada
    MS-Off Ver
    365
    Posts
    73

    How do I maintain formula in last row when table expands?

    I have a table. In the rows there is a formula that works great to give me a time stamp when data is entered into Column "D".
    The problem is, when the table is full and it needs to expand, (type into cell below table), it expands the table by adding a row, but the formatting of the formula does not work for the newly created row.

    In this workbook data is ONLY entered by barcode scanner and data input forms.
    When you try to enter manually outside of table on row directly below it has same effect (does not work).

    01.png
    02.png
    03.png

    You can see the formula for the time goes to 12:00 instead of creating a proper time as above rows.

    Maybe, whenever there is data entered into column D it could insert new row BEFORE last row is used? Just a thought..
    I tried doing something like this, but it just looped forever.
    Thanks in advance.

    Chris
    Attached Files Attached Files

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: How do I maintain formula in last row when table expands?

    Works for me.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: How do I maintain formula in last row when table expands?

    It works the first couple of times but as soon as you enter out of Table area and go back, it stops working as it should...
    Add a sheet event...It will be more stable than the formula...

    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  4. #4
    Registered User
    Join Date
    07-15-2022
    Location
    Delta Canada
    MS-Off Ver
    365
    Posts
    73

    Re: How do I maintain formula in last row when table expands?

    HI there Sintek,
    Looks pretty good, however it overwrites the formula in "F"

    In "F" I have a formula that looks at D, when data is entered in D then it produces the time in F.
    This is the formula in "F" =IF(D10<>"",IF(F10<>"",F10,NOW()),"")

    After the sheet is complete, I need to clear the data and get ready to start again.

    After I put in your sheet event and I clear the sheet (run my clear sheet macro) the time in F still remains and does not clear. (it should clear, automatically but remain formula).

    The clear macro clears "D" which removes all times in "F"

    Range("Table4[Case Barcode]").Select
    Selection.ClearContents

    Have a look, see if you can do something.

    Also if there is random text in any cell below the table it also messes up.

    I was wondering if we can auto expand the table from data input of "D", that way there is always an extra blank row at the bottom of the table. Similar result if you hit TAB on the keyboard in the bottom right corner of the table. Do you think this could be doable?

    Thanks so much.
    Chris

  5. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: How do I maintain formula in last row when table expands?

    Does this Table only have X no of entries...when you clear are you only clearing Col D

    run my clear sheet macro
    All you need to do is change this macro too
    Last edited by sintek; 11-09-2022 at 10:09 AM.

  6. #6
    Registered User
    Join Date
    07-15-2022
    Location
    Delta Canada
    MS-Off Ver
    365
    Posts
    73

    Re: How do I maintain formula in last row when table expands?

    I just found out something very cool/////

    I found a vba to add a row to table.....
    This would help everything....

    Selection.ListObject.ListRows.Add AlwaysInsert:=False

    This line adds row to table.

    So if we run this macro everytime we add data into the table (table4) in column "D" all our problems will be fixed.

    I have a CHANGE EVENT for the sheet, but it only works on a single cell.
    Can you help to change it to all cells in that table?
    So when ever we enter data into "D" it will run macro and add a row.
    This will solve THIS problem???

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$D$10" Then
    Call ExpandTable
    End If
    End Sub

    What do you think/.
    Can you change single cell to a range?
    If Target.Address = "$D$10" Then

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: How do I maintain formula in last row when table expands?

    Making life difficult for yourself hey...You don't need the formula...
    Keep the sheet event code and just change your clear macro to...
    Please Login or Register  to view this content.
    See attached...
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    07-15-2022
    Location
    Delta Canada
    MS-Off Ver
    365
    Posts
    73

    Re: How do I maintain formula in last row when table expands?

    Wow this is amazing.....
    Done like Dinner...


    Now onto step #2 LOL

    Should I post a new thread for the second part to this one?

    I want to copy the filled table to another sheet for safe keeping, and then copy the next completed sheet to the bottom (below) the first saved data sheet. Etc?

    What do you think/???

    Chris

  9. #9
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: How do I maintain formula in last row when table expands?

    It is much easier to navigate the Forum when threads are kept to its original inquiry...
    So, in answer to your question...Yes, open new thread...
    Someone will quickly supply a solution..It's a very simple process...
    Just remember to explain in step by step detail what it is you require...Also, add a before|after expected result...
    Happy Coding...

  10. #10
    Registered User
    Join Date
    07-15-2022
    Location
    Delta Canada
    MS-Off Ver
    365
    Posts
    73

    Re: How do I maintain formula in last row when table expands?

    Oh oh oh oh...
    One more thing...

    Is it possible to delete blank rows (inside table) on "clear data"
    We should leave row 8 and 9 though.

    Is this easy?
    Thanks
    Chris

  11. #11
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: How do I maintain formula in last row when table expands?

    blank rows based on which column...We should leave row 8 and 9 though.
    Is this because of your formulas...
    Anyway...this does what you require...

    Please Login or Register  to view this content.
    Last edited by sintek; 11-09-2022 at 11:20 AM.

  12. #12
    Registered User
    Join Date
    07-15-2022
    Location
    Delta Canada
    MS-Off Ver
    365
    Posts
    73

    Re: How do I maintain formula in last row when table expands?

    I just tested your new clear data macro
    Works great Thanks so much


    Just to clean it up visually.
    If we leave just the first 2 rows it still works I tested it.
    An Yah , there is a formula in "E" that puts in a 3700 if data is entered into "D"
    So yes I think we need to keep that row as well on clear

    Thanks so much
    Chris
    Last edited by chrispikekerry; 11-09-2022 at 11:24 AM.

  13. #13
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: How do I maintain formula in last row when table expands?

    Post 11 solves...Tx for rep +

  14. #14
    Registered User
    Join Date
    07-15-2022
    Location
    Delta Canada
    MS-Off Ver
    365
    Posts
    73

    Re: How do I maintain formula in last row when table expands?

    I totally messed up.....
    Its not working at all.. It has links to my previous sheet, I am trying to fix.
    I think it has something to do with the frm input...\UGHHHH
    My Bad

  15. #15
    Registered User
    Join Date
    07-15-2022
    Location
    Delta Canada
    MS-Off Ver
    365
    Posts
    73

    Re: How do I maintain formula in last row when table expands?

    ok ALMOST DONE FIXING IT.

+ 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] Vlookup column index number keeps changing when table expands
    By randall78 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-04-2021, 12:19 PM
  2. Automatically add new row to worksheet IF dynamic table above expands?
    By aussie.lilly in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-22-2021, 09:25 AM
  3. How to change a number to increase in size as the table expands
    By LinaL in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 06-04-2018, 04:40 PM
  4. How to change a number to increase in size as the table expands
    By LinaL in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-02-2018, 09:47 AM
  5. Duplicate formula in rows as pivot table expands
    By vrinner in forum Excel General
    Replies: 1
    Last Post: 02-05-2018, 03:50 PM
  6. Formulas not added as table expands
    By mjcarman01 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-27-2017, 02:27 PM
  7. Replies: 3
    Last Post: 04-06-2016, 12:19 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