+ Reply to Thread
Results 1 to 6 of 6

Hit Tab button to insert new line and preserve previous lines formulas

  1. #1
    Registered User
    Join Date
    02-23-2014
    Location
    Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    7

    Hit Tab button to insert new line and preserve previous lines formulas

    Hello All,

    I am brand new and I don't know a ton about macros and programming in Excel.

    What I'm trying to do here in this attached spreadsheet is at the end of inputting my line, I want to hit the tab (marked in red to make it clear where) button in the cell and have it automatically create another blank line but keep the formulas the same.

    Basically I just want to avoid having to right click and insert line and then drag the formulas column by column down which is a pain.

    Any help would be appreciated.

    Thanks!

    P.S. See attached
    Attached Files Attached Files

  2. #2
    Registered User
    Join Date
    11-25-2013
    Location
    Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: Hit Tab button to insert new line and preserve previous lines formulas

    I'd recommend investigating Excel's Tables functionality, as this does exactly what you want without any coding. In your spreadsheet, you'd set up each month as a separate table. Here's a quick version of how to do it, but it's worth Googling "Excel Tables", because once you understand their capabilities you'll probably find a whole more uses for them.

    1. Highlight everything excel the headers and totals for the month, click Format as Table on the Home tab, and pick a style
    2. On the Table Tools Design tab, tick the Total Row checkbox. this will give you a total row with dropdowns to select Sum, etc. Set this to give the required totals, and then delete your original total row as it is no longer required
    3. Now, when you place the cursor in the last cell of the last row in the table and press tab, as new row will be inserted above the totals, with the formulas automatically added
    4. Do the same for the other months

  3. #3
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Hit Tab button to insert new line and preserve previous lines formulas

    Hello to both eighch and larena and welcome to the forums, I hope you get the same great help that I have received and as your confidence grows, that you can in turn assist others

    @larena, you have a good point, I personally have no idea what tables can do so I am off to attend the DR Google academy as you suggest. Your outline suggestion sounds like a good and workable solution.

    It may have helper OP (original poster in shorthand) if you ad taken their attachment, made the suggested changes and then reposted it. You will see from many posts, most of us are much more likely to respond to a post that has an example (as eighch did) and, speaking for myself as a learner still, the solution can be one thing when explained, a totally different thing when you see the actual result.

    Just a little two cents worth from my point of view, and again, welcome to both of you

    Cheers

    Jmac1947

    1. Please consider clicking on the * Add Reputation if you think this post has helped you
    2. Mark your thread as SOLVED when question is resolved

  4. #4
    Registered User
    Join Date
    02-23-2014
    Location
    Kentucky
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Hit Tab button to insert new line and preserve previous lines formulas

    Hi to both of you.

    Yes, an example would be great if you wouldn't mind. I did try to follow the instructions, but I got lost along the way.

    Thanks so much in advance for your help.

  5. #5
    Valued Forum Contributor
    Join Date
    01-19-2010
    Location
    Melbourne Australia
    MS-Off Ver
    latest is Excel 2016. have older versions
    Posts
    624

    Re: Hit Tab button to insert new line and preserve previous lines formulas

    Hi eighch,

    As a part of my learning experience I also tried to follow the instructions from @larena and finally managed to get it doing what I think it should.

    In the course of that I also think that a version including the result may not actually be of much help (it doesn't look much different)

    So what I will try to do is describe in a little more details what @larena was suggesting. My col/cell references will be from your original spreadsheet.

    1. select the cells A3 - S19 (your january data)
    2. from the insert menu select table and after checking the range in the prompt, select OK
    3. from the "table styles" select a table style - for the purposes of this exercise it probably doesn't make any difference which one you choose.
    4 in the "table style Options" tick the total row checkbox (you can also un-tick the header row checkbox if you like, again it doesnt impact the result, just the look & feel
    5. After you have ticked the total row checkbox you will see a new row has been inserted (if you leave header ticked it should be row 21 with literal "Total" left justified in cell A21)
    6. Taking "New Net Sold" as an example (col G) click in cell G21 and you should see a drop down indicator immediately to the right, click on the drop down indicator and select "Sum". This will insert the current sum into G21 which you can verify by looking at your original value in what is now G22.
    7. Repeat for any cols that you want summed.
    8. When you are happy with the new total row you can then delete your original totals row (should now be row 22)

    The last bit of the puzzle is the process of adding a new line. This will be automatically done by placing the cursor in the last current field S20 and simply pressing the Tab key. A new line should appear and it should have the formulas you need ready for your data input.

    I hope this is a bit clearer for you, sorry about the "instructions 101" style of the post but I was actually doing it as I was typing.

    P.S. whaen yo have it working for the January data you need to repeat the same process for ewach of the other months in your "Sales tab"
    Last edited by jmac1947; 02-25-2014 at 07:03 PM. Reason: forgot the other months

  6. #6
    Registered User
    Join Date
    11-25-2013
    Location
    Western Australia
    MS-Off Ver
    Excel 2010
    Posts
    59

    Re: Hit Tab button to insert new line and preserve previous lines formulas

    Apologies for not uploading an example earlier. I've uploaded a copy of the original spreadsheet, with the top two months converted to tables.

    Good instructions, jmac1947. Step 2 can also be achieved by clicking "Format as Table" on the Home tab.

    Just a bit of an explanation about the inclusion of columns Q & S in the table. When creating a table, Excel will automatically include adjacent columns. This is really useful when you need to add extra columns - you just enter data after the last table column, and the table expands to include it.

    In this case, is eighch wants users to be able to tab from the cells currently marked in red, then Q & S will need to be excluded from the table. To achieve this, you'd need to add a blank column after column Q before creating the table.

    Please let me know if you have any questions.
    Attached Files Attached Files

+ 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] Macro to Insert Blank Line and Sum Above Lines
    By cgomez04 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-14-2012, 10:02 AM
  2. Replies: 0
    Last Post: 02-09-2012, 11:53 AM
  3. Insert Line Every Other Row w/Multiple Formulas based on Line Above
    By xyshel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-23-2011, 11:09 AM
  4. button to insert lines
    By Sonyk in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 04-26-2009, 05:28 PM
  5. Replies: 8
    Last Post: 11-30-2005, 09:55 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