+ Reply to Thread
Results 1 to 10 of 10

Setting print area for Excel spreadsheet or table with total row at bottom

  1. 04-14-2014, 12:32 PM #1
    CounterClockWise
    CounterClockWise is offline
    Registered User
    Join Date
    04-14-2014
    Location
    North Caroline, US
    MS-Off Ver
    Excel 2010
    Posts
    4

    Setting print area for Excel spreadsheet or table with total row at bottom

    I am trying to design a travel form for some staff members to use. Some members will only have a few rows of travel to enter while other members may have up to 200 rows. I need to have a line at the end that will show the total miles and total reimbursement amount. The form needs to be set up to only print the rows that they enter information on.
    Most of the staff members are not "tech savvy" and will not understand filtering out the blank lines. We are also not allowed to use macros. I have tried experimenting with different setups with Excel spreadsheets and tables but I can't solve the printing issue - I always end up with 7 pages even if there is only user data on page 1. Does anyone have any ideas or suggestions? What is the best way to do this? Can it be done without macros? Thank you!
    Reply With QuoteRegister To Reply
  2. 04-14-2014, 07:13 PM #2
    mikeTRON
    mikeTRON is offline
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Setting print area for Excel spreadsheet or table with total row at bottom

    Macros would make this easy but you can use a dynamic named range.

    How many columns of data? Can you post a sample summary so I can give you an exact formula to use? This should be fairly straightforward.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.
    Reply With QuoteRegister To Reply
  3. 04-14-2014, 08:03 PM #3
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Setting print area for Excel spreadsheet or table with total row at bottom

    You could set up with boilerplate text at the top with cells, text boxes, Active X text boxes etc then have a table under that with headings for the data to be entered. Include the TOTALS row at the bottom of the Table. (Design Tab Table Style Options) As the table expands with data, the TOTALS row will remain at the bottom of the table.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  4. 04-14-2014, 08:41 PM #4
    CounterClockWise
    CounterClockWise is offline
    Registered User
    Join Date
    04-14-2014
    Location
    North Caroline, US
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Setting print area for Excel spreadsheet or table with total row at bottom

    Thank you so much! I ran across dynamic named ranges earlier this afternoon but I have not been able to figure out the correct formula yet... I have 5 columns :Date, To, From, Purpose, Miles. I have the header and footer already set up with all of the information that our Finance Dept. needs and I have the top 3 rows of the table set up for users to enter their name and information (these 3 rows are set up to print at the top of every page). Have a great evening!
    Thanks,
    Carla
    Reply With QuoteRegister To Reply
  5. 04-14-2014, 09:06 PM #5
    mikeTRON
    mikeTRON is offline
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: Setting print area for Excel spreadsheet or table with total row at bottom

    Post a sample workbook and I can show you. Otherwise I cant determine where the summary is or how to setup your logic.

    Newdoverman also has a good idea to use a table.
    Reply With QuoteRegister To Reply
  6. 04-17-2014, 09:05 AM #6
    CounterClockWise
    CounterClockWise is offline
    Registered User
    Join Date
    04-14-2014
    Location
    North Caroline, US
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Setting print area for Excel spreadsheet or table with total row at bottom

    Sorry to take so long to post the workbook - in lots of meetings this week. This is the first time that I've tried to post a workbook to this site - please let me know if it will not open.
    Thank you so much for your help!
    Thanks,
    Carla
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 04-17-2014, 09:34 AM #7
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Setting print area for Excel spreadsheet or table with total row at bottom

    Cannot open, I get a File Format Not Valid error. File is possibly corrupted.
    Reply With QuoteRegister To Reply
  8. 04-17-2014, 11:25 AM #8
    CounterClockWise
    CounterClockWise is offline
    Registered User
    Join Date
    04-14-2014
    Location
    North Caroline, US
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Setting print area for Excel spreadsheet or table with total row at bottom

    2nd try! Please let me know if the attachment will not open.
    Thanks,
    Carla
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 04-17-2014, 11:47 AM #9
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Setting print area for Excel spreadsheet or table with total row at bottom

    Opens just fine.
    Reply With QuoteRegister To Reply
  10. 04-17-2014, 11:55 AM #10
    Chris 53
    Chris 53 is offline
    Forum Contributor
    Join Date
    01-30-2013
    Location
    Wales
    MS-Off Ver
    Excel 2013
    Posts
    231

    Re: Setting print area for Excel spreadsheet or table with total row at bottom

    Have you thought of a slight rejigging of the form so that the totals are put at the top rather than the bottom?

    This means that the figures that the accounting people want to see are always displayed on the first page. You don't have to worry about moving totals at the bottom or inserting lines. Format the table so that new rows are added at the bottom with the formulas copied down and just let excel handle it.

    As far as I know putting totals at the bottom are a hang up from paper based accounting records where everyone added things up from the top, but excel doesn't care where the total formula is placed.

    Hope this helps

    Chris
    Last edited by Chris 53; 04-17-2014 at 11:58 AM.
    Click * below if this answer helped
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Setting Print Area within Excel Macro
    By RenaWare in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2013, 08:10 PM
  2. Problem setting print area in Excel
    By brittkneez in forum Excel General
    Replies: 2
    Last Post: 07-10-2009, 03:43 PM
  3. Setting The Print Area
    By beans_21 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-10-2006, 05:32 AM
  4. setting a print area
    By Matilda in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-13-2005, 03:50 PM
  5. Print Area:What would be the code for setting the print area
    By wammer in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-02-2005, 02: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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1