+ Reply to Thread
Results 1 to 35 of 35

Fill down formulas

  1. #1
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Fill down formulas

    I could use a hand on this.

    I don't know how many rows I will use so I would rather not drag a formula down the page. I would like the formula(s) in column E Row 5 to fill down to Row 6 only if I enter the next corresponding number down in Col A.

    So, if enter 4 into A6, then formula in E5 fills down to E6.

    Would this have to be one with a code?

    Much appreciated.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    01-03-2018
    Location
    Fukuoka, Japan
    MS-Off Ver
    Excel 2013
    Posts
    123

    Re: Fill down formulas

    I think the code below would work.

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    Great.

    I don't think I set it up correctly. See attached. Newbie code guy.
    -I clicked F11 Insert module, pasted code.
    -on worksheet clicked F8 but no macro shows so couldn't run it.

    Also, I forgot to mention one other thing. Is there a way to have a running sub total adding Column E. So,
    F3 = E3
    F4 = E3+E4
    F5 = F4+E5
    etc.
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    1) Why not convert range to a Table? So that it automatically inserts formula.

    2) if not table
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    That works great! I tried convert range to a table (first time). I found that Col H formula carried down but not Col I running total.
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    1) Select A1:H8 (Whole range including header)
    2) got o [Insert] - [Table]

    That will convert range to a table and you will see the formula will be inserted automatically when you add new data.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    The problem is with Col I running total. I can't get it to work..
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    OK, didn't notice that your formula in I2, try change the formula in I2 to =SUM(I1,H2).

  9. #9
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    That got it. Thanks very much for your help.

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    You are welcome and thanks for the rep.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  11. #11
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    I wonder if I can ask one last thing on this. If I want to delete row data how can I keep the fill formulas?

  12. #12
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    What do you mean?

    If you clear A:G, formula in H:I should still remain...

    Am I missing something?

  13. #13
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    I built a whole new table using your details and it works well. If I want to delete or clear the rows to enter new date the formulas in Col's P and Q are gone. I tried highlight row, format, Protection, uncheck Locked. Then, clicked Review, Protect Sheet and checked insert row and delete row but it wasn't working. The attached sheet is without those things.
    Attached Files Attached Files

  14. #14
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    If you delete/clear formula, it will not re built the formula by itself.

    How about protecting worksheet?
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    I like the way you protected it so that col's P and Q are protected. I had built an =IF argument into P and Q so it would be blank if no value in Column A. It still works except when I clear rows. Is there a way to fix this?
    Last edited by Peterino; 02-20-2018 at 09:22 AM.

  16. #16
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    Hi jindon

    Did you see my revised reply here?

  17. #17
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    Then need to do it via VBA...
    Please Login or Register  to view this content.
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    Wow! That is exactly what I was wanting.

    If I delete all rows to enter fresh data then the first row 2 changes and the formulas are gone. To stop this I thought of protecting cell A2 so that the rest of the row and fills remain. I tried doing it myself but couldn't seem to make it work properly. I promise this is the last request.

  19. #19
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    Didn't think about it...
    Please Login or Register  to view this content.
    Attached Files Attached Files

  20. #20
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    That is 100% perfect. Thank you for all your help.

    Peter

  21. #21
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    I spoke too soon. I have one more request provided I haven't run out of allowable requests.
    I need to be able to sort the rows, mostly by date Col C but maybe by Col J. When I do this Col A gets mixed up. Is there a way to keep Col A in order beginning A2 =1 etc.?

  22. #22
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    Change to
    Please Login or Register  to view this content.
    In order to reflect the change, select all data cells in col.A and click on one cell then Ctrl + Enter for the first time.

  23. #23
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    That works except if I delete a row and then decide I want to keep it the undo button is grayed out.
    I did: File-options-save-unchecked Save AutoRecover but the undo is still unavailable.
    Attached Files Attached Files

  24. #24
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    You can"t undo.
    That's a nature of vba.

  25. #25
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    Ok. Thanks very much for all your help.

  26. #26
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    Hi Jindon
    I am not sure if I a able to ask another question after marking this thread as solved.
    I am unable to fill a cell color. Is this because of the code? Something I can fix? Thank you.

  27. #27
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    When conditional format is applied, it overrule, so you can not change the color.

    If you don't want the conditional formatting (coloring alternative rows).
    change
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  28. #28
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    Hi Jindon

    Is it ok for me to ask another question? If so I need to make a few adjustments but I don't know how. I tried but ran into issues.
    I added columns F,G and H. Col F is Col S - Col E
    Attached Files Attached Files

  29. #29
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    Is it just this?
    Please Login or Register  to view this content.

  30. #30
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    Re: With Range("a2", Range("a" & Rows.Count).End(xlUp)).Resize(, 20) You changed (, 17) to (,20). This is to resize the column?

    My question relates to the added Cols F, G, and H.
    1. Did I mess up the code?
    2. Can it be adjusted so that any data in Col F equals Col S - Col E? My attached sheet shows a value in E4 and so F4 would be S4 - E4 = $6. Can this be done?

  31. #31
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    Are you asking formula in Col.F?
    Please Login or Register  to view this content.

  32. #32
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    You go it! I really need to learn how to do this. Thanks again.

  33. #33
    Forum Contributor
    Join Date
    10-22-2014
    Location
    Ontario
    MS-Off Ver
    365
    Posts
    222

    Re: Fill down formulas

    Hi Jindon

    If you are still able to help i would like to know how to be able to insert a row or if possible drag a row to a different location.
    IE: If I want the 9th row A10 to become row 1 A2, how would I do that? Is it possible?

    Thank you. Peter
    Attached Files Attached Files

  34. #34
    Forum Expert kersplash's Avatar
    Join Date
    11-22-2016
    Location
    Perth
    MS-Off Ver
    Home 2016 (Windows 10)/Work 2013 Pro Plus (Windows 10)
    Posts
    2,012

    Re: Fill down formulas

    Sorry for the off-topic, but when you delete all the rows in a table any existing formulas will be maintained even though the table appears to be empty.

    Once you insert a new piece of data to the first blank row (not in one of the formula fields) then you will see the formulas are still there.

  35. #35
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Fill down formulas

    Do not drag, only Cut and Insert copied row(s).
    Please Login or Register  to view this content.

+ 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. Auto fill / flash fill not working with formulas?
    By mhorga in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-18-2016, 12:54 PM
  2. How to fill down formulas?
    By bitzy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-20-2013, 11:03 PM
  3. Insert cells, fill formulas down, set formulas for dynamic range
    By Snickers65 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2013, 01:43 PM
  4. Fill Formulas
    By Brian Matlack in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-03-2009, 06:15 PM
  5. How do I fill in formulas?
    By jojorumpus in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 06-13-2007, 10:41 PM
  6. Fill in Formulas
    By snax500 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-25-2005, 12:05 PM
  7. can't get formulas to fill down. help!!
    By jimmy_reed in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-30-2005, 06:43 PM

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