Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Varying page Excel report
I have an Excel template in which I import data that is of varying records.
Sometimes there are 3 records, sometimes there are 50 or 200 records. I do not know how to make a varying page Excel report. (For example, my clients sometimes want to see data on top 3 occupations, sometimes on top 50 occupations.) (The template is formatted, has headers, footers, a few links, works fine.) Using Excel 2002. |
#2
|
|||
|
|||
Hi Judy-
Perhaps I'm missing something, but there should not be any need for adjustment. The number of pages will be based on the number of records being printed as long as you have not set a specific Print Area and you choose Active Sheet(s) in the Print dialog box (and assuming there is no other content elsewhere on the sheet). One thing you may want to add in your template, if not already done, is go to FilePage SetupSheet (not thru Print Preview) and specify Rows to Repeat at Top in the Print Titles section so that your captions will reprint at the top of the columns if more than one page of data is being printed. Please post back with more specifics if this is not sufficient. HTH |:) "judypudy" wrote: I have an Excel template in which I import data that is of varying records. Sometimes there are 3 records, sometimes there are 50 or 200 records. I do not know how to make a varying page Excel report. (For example, my clients sometimes want to see data on top 3 occupations, sometimes on top 50 occupations.) (The template is formatted, has headers, footers, a few links, works fine.) Using Excel 2002. |
#3
|
|||
|
|||
Thank you so much! I am new to Excel, and what are are saying about the
Print dialog box should work for my report. But I think I still must format all 200 pages or so in Excel, in case my report is that long. If I do a copy and Paste Special, etc., 6 times then my 4 page report will become 256 pages. Then I will just print the Active sheets! It should work. Thanks again! "CyberTaz" wrote: Hi Judy- Perhaps I'm missing something, but there should not be any need for adjustment. The number of pages will be based on the number of records being printed as long as you have not set a specific Print Area and you choose Active Sheet(s) in the Print dialog box (and assuming there is no other content elsewhere on the sheet). One thing you may want to add in your template, if not already done, is go to FilePage SetupSheet (not thru Print Preview) and specify Rows to Repeat at Top in the Print Titles section so that your captions will reprint at the top of the columns if more than one page of data is being printed. Please post back with more specifics if this is not sufficient. HTH |:) "judypudy" wrote: I have an Excel template in which I import data that is of varying records. Sometimes there are 3 records, sometimes there are 50 or 200 records. I do not know how to make a varying page Excel report. (For example, my clients sometimes want to see data on top 3 occupations, sometimes on top 50 occupations.) (The template is formatted, has headers, footers, a few links, works fine.) Using Excel 2002. |
#4
|
|||
|
|||
Hello again-
Glad the info was useful, but I get the impression that one important point has not been emphasized. There is no need to format 200 pages because Excel does not use pages as we think of them. Page Setup is used to specify Margins, Page Orientation, Headers/Footers, etc. for the entire WorkBook file. The actual number of pages that get printed depends on the number of records (rows with content) on a worksheet that will fit on a page of paper. It doesn't matter if there is only one page or 1,000 pages, the same specs will be used for each page. If you have content on another worksheet, the same Page Setup specifications will be used when you print that worksheet's content unless you change them for some reason (due to a different type of data content, for example). Copying & Pasting Page Setup settings is not only unnecessary, but can't be done. Regards |:) "judypudy" wrote: Thank you so much! I am new to Excel, and what are are saying about the Print dialog box should work for my report. But I think I still must format all 200 pages or so in Excel, in case my report is that long. If I do a copy and Paste Special, etc., 6 times then my 4 page report will become 256 pages. Then I will just print the Active sheets! It should work. Thanks again! "CyberTaz" wrote: Hi Judy- Perhaps I'm missing something, but there should not be any need for adjustment. The number of pages will be based on the number of records being printed as long as you have not set a specific Print Area and you choose Active Sheet(s) in the Print dialog box (and assuming there is no other content elsewhere on the sheet). One thing you may want to add in your template, if not already done, is go to FilePage SetupSheet (not thru Print Preview) and specify Rows to Repeat at Top in the Print Titles section so that your captions will reprint at the top of the columns if more than one page of data is being printed. Please post back with more specifics if this is not sufficient. HTH |:) "judypudy" wrote: I have an Excel template in which I import data that is of varying records. Sometimes there are 3 records, sometimes there are 50 or 200 records. I do not know how to make a varying page Excel report. (For example, my clients sometimes want to see data on top 3 occupations, sometimes on top 50 occupations.) (The template is formatted, has headers, footers, a few links, works fine.) Using Excel 2002. |
#5
|
|||
|
|||
Thanks so much. I need more time with EXCEL.
I am using repeat rows for the top. But, I have links in the repeat rows that display the date of the data, which organization the report is for, and other important info. Now I have 4 pages filled in with data, in an 8 page template. When I print Active Sheets - 1 - 4, the report says Page 1 of 8, not Page 1 of 4. This will be confusing to my users. Especially when it will say Page 1 of 256, when I enlarge the workbook to 256 pages . I think the links make the entire workbook active, or something. When I save the report to a file it saves all 8 pages. I will just delete the extra pages without data. (I will have to delete 250 pages in the future, when I enlarge the template as big as it might get. Ugh. How tedious.) I find EXCEL is not as automated as I would like. I guess I need to learn VBA to do things automatically, like tell EXCEL every time it reads in a file that it is delimited by commas. Since all my files are, I would like to save this as a default. Thanks again. "CyberTaz" wrote: Hello again- Glad the info was useful, but I get the impression that one important point has not been emphasized. There is no need to format 200 pages because Excel does not use pages as we think of them. Page Setup is used to specify Margins, Page Orientation, Headers/Footers, etc. for the entire WorkBook file. The actual number of pages that get printed depends on the number of records (rows with content) on a worksheet that will fit on a page of paper. It doesn't matter if there is only one page or 1,000 pages, the same specs will be used for each page. If you have content on another worksheet, the same Page Setup specifications will be used when you print that worksheet's content unless you change them for some reason (due to a different type of data content, for example). Copying & Pasting Page Setup settings is not only unnecessary, but can't be done. Regards |:) "judypudy" wrote: Thank you so much! I am new to Excel, and what are are saying about the Print dialog box should work for my report. But I think I still must format all 200 pages or so in Excel, in case my report is that long. If I do a copy and Paste Special, etc., 6 times then my 4 page report will become 256 pages. Then I will just print the Active sheets! It should work. Thanks again! "CyberTaz" wrote: Hi Judy- Perhaps I'm missing something, but there should not be any need for adjustment. The number of pages will be based on the number of records being printed as long as you have not set a specific Print Area and you choose Active Sheet(s) in the Print dialog box (and assuming there is no other content elsewhere on the sheet). One thing you may want to add in your template, if not already done, is go to FilePage SetupSheet (not thru Print Preview) and specify Rows to Repeat at Top in the Print Titles section so that your captions will reprint at the top of the columns if more than one page of data is being printed. Please post back with more specifics if this is not sufficient. HTH |:) "judypudy" wrote: I have an Excel template in which I import data that is of varying records. Sometimes there are 3 records, sometimes there are 50 or 200 records. I do not know how to make a varying page Excel report. (For example, my clients sometimes want to see data on top 3 occupations, sometimes on top 50 occupations.) (The template is formatted, has headers, footers, a few links, works fine.) Using Excel 2002. |
#6
|
|||
|
|||
When I print Active Sheets - 1 - 4, the report says Page 1 of
8, not Page 1 of 4. I believe you are confusing a Worksheet with a Page. Just because you want to print 4 pages of data doesn't mean you have to separate that data onto 4 worksheets. The content of any 1 worksheet will determine the number of pages required to print it on (anywhere from 1 to dozens) when you print that Active sheet. The term 'Active' simply refers to the sheet(s) you have selected when you print. Apparently the volume of content on your 4 Worksheets is requiring 8 Pages to be printed... That accounts for your page numbering. Use Print Preview to see what is printing on which pages. You might also want to review Excel Help on Printing. Very useful info. like tell EXCEL every time it reads in a file that it is delimited by commas. To open a text file as a new workbook, start with a Blank new workbook, then use DataImport External DataImport Data... and just doubleclick the text file. Follow the steps of the Import Wizard (probably no more than clicking NEXT twice & FINISH) & specify what cell the incoming data should start in. Then save the file with whatever name you choose. You can add more content to an existing workbook in the same way. Good Luck |:) "judypudy" wrote: Thanks so much. I need more time with EXCEL. I am using repeat rows for the top. But, I have links in the repeat rows that display the date of the data, which organization the report is for, and other important info. Now I have 4 pages filled in with data, in an 8 page template. When I print Active Sheets - 1 - 4, the report says Page 1 of 8, not Page 1 of 4. This will be confusing to my users. Especially when it will say Page 1 of 256, when I enlarge the workbook to 256 pages . I think the links make the entire workbook active, or something. When I save the report to a file it saves all 8 pages. I will just delete the extra pages without data. (I will have to delete 250 pages in the future, when I enlarge the template as big as it might get. Ugh. How tedious.) I find EXCEL is not as automated as I would like. I guess I need to learn VBA to do things automatically, like tell EXCEL every time it reads in a file that it is delimited by commas. Since all my files are, I would like to save this as a default. Thanks again. "CyberTaz" wrote: Hello again- Glad the info was useful, but I get the impression that one important point has not been emphasized. There is no need to format 200 pages because Excel does not use pages as we think of them. Page Setup is used to specify Margins, Page Orientation, Headers/Footers, etc. for the entire WorkBook file. The actual number of pages that get printed depends on the number of records (rows with content) on a worksheet that will fit on a page of paper. It doesn't matter if there is only one page or 1,000 pages, the same specs will be used for each page. If you have content on another worksheet, the same Page Setup specifications will be used when you print that worksheet's content unless you change them for some reason (due to a different type of data content, for example). Copying & Pasting Page Setup settings is not only unnecessary, but can't be done. Regards |:) "judypudy" wrote: Thank you so much! I am new to Excel, and what are are saying about the Print dialog box should work for my report. But I think I still must format all 200 pages or so in Excel, in case my report is that long. If I do a copy and Paste Special, etc., 6 times then my 4 page report will become 256 pages. Then I will just print the Active sheets! It should work. Thanks again! "CyberTaz" wrote: Hi Judy- Perhaps I'm missing something, but there should not be any need for adjustment. The number of pages will be based on the number of records being printed as long as you have not set a specific Print Area and you choose Active Sheet(s) in the Print dialog box (and assuming there is no other content elsewhere on the sheet). One thing you may want to add in your template, if not already done, is go to FilePage SetupSheet (not thru Print Preview) and specify Rows to Repeat at Top in the Print Titles section so that your captions will reprint at the top of the columns if more than one page of data is being printed. Please post back with more specifics if this is not sufficient. HTH |:) "judypudy" wrote: I have an Excel template in which I import data that is of varying records. Sometimes there are 3 records, sometimes there are 50 or 200 records. I do not know how to make a varying page Excel report. (For example, my clients sometimes want to see data on top 3 occupations, sometimes on top 50 occupations.) (The template is formatted, has headers, footers, a few links, works fine.) Using Excel 2002. |
#7
|
|||
|
|||
Thanks again!
On Print Preview it shows 8 pages, 4 pages with no data. I think because of the first 13 lines as titles with links it would print 8 pages, 4 pages with no data but with the 13 title lines and linked data filled in correctly. When I do View, Page Break Preview, select print area, File, Print area, set print area then it prints only four pages with Page 1 of 4. Just what I was looking for! I was looking under Page setup and Print, not View. Some solutions are not that obvious to me. I need to try to use the Wizard more. I have not always been able to get help from the Wizard, but this time it did help me. I have a few books that help, the Show Me book, and the Excel 2002 Bible. The other point I was making is that I have read a file into my template about 50 times or more now, to develop the template, etc. It just gets tedious to continuously tell it to delimit the file the same way, put the data in the same cell, etc., with the same three steps. I will look into VBA to automate this. When the template is finished I will be reading different files into it 100's of time. Now that does get tedious. Thanks again! "CyberTaz" wrote: When I print Active Sheets - 1 - 4, the report says Page 1 of 8, not Page 1 of 4. I believe you are confusing a Worksheet with a Page. Just because you want to print 4 pages of data doesn't mean you have to separate that data onto 4 worksheets. The content of any 1 worksheet will determine the number of pages required to print it on (anywhere from 1 to dozens) when you print that Active sheet. The term 'Active' simply refers to the sheet(s) you have selected when you print. Apparently the volume of content on your 4 Worksheets is requiring 8 Pages to be printed... That accounts for your page numbering. Use Print Preview to see what is printing on which pages. You might also want to review Excel Help on Printing. Very useful info. like tell EXCEL every time it reads in a file that it is delimited by commas. To open a text file as a new workbook, start with a Blank new workbook, then use DataImport External DataImport Data... and just doubleclick the text file. Follow the steps of the Import Wizard (probably no more than clicking NEXT twice & FINISH) & specify what cell the incoming data should start in. Then save the file with whatever name you choose. You can add more content to an existing workbook in the same way. Good Luck |:) "judypudy" wrote: Thanks so much. I need more time with EXCEL. I am using repeat rows for the top. But, I have links in the repeat rows that display the date of the data, which organization the report is for, and other important info. Now I have 4 pages filled in with data, in an 8 page template. When I print Active Sheets - 1 - 4, the report says Page 1 of 8, not Page 1 of 4. This will be confusing to my users. Especially when it will say Page 1 of 256, when I enlarge the workbook to 256 pages . I think the links make the entire workbook active, or something. When I save the report to a file it saves all 8 pages. I will just delete the extra pages without data. (I will have to delete 250 pages in the future, when I enlarge the template as big as it might get. Ugh. How tedious.) I find EXCEL is not as automated as I would like. I guess I need to learn VBA to do things automatically, like tell EXCEL every time it reads in a file that it is delimited by commas. Since all my files are, I would like to save this as a default. Thanks again. "CyberTaz" wrote: Hello again- Glad the info was useful, but I get the impression that one important point has not been emphasized. There is no need to format 200 pages because Excel does not use pages as we think of them. Page Setup is used to specify Margins, Page Orientation, Headers/Footers, etc. for the entire WorkBook file. The actual number of pages that get printed depends on the number of records (rows with content) on a worksheet that will fit on a page of paper. It doesn't matter if there is only one page or 1,000 pages, the same specs will be used for each page. If you have content on another worksheet, the same Page Setup specifications will be used when you print that worksheet's content unless you change them for some reason (due to a different type of data content, for example). Copying & Pasting Page Setup settings is not only unnecessary, but can't be done. Regards |:) "judypudy" wrote: Thank you so much! I am new to Excel, and what are are saying about the Print dialog box should work for my report. But I think I still must format all 200 pages or so in Excel, in case my report is that long. If I do a copy and Paste Special, etc., 6 times then my 4 page report will become 256 pages. Then I will just print the Active sheets! It should work. Thanks again! "CyberTaz" wrote: Hi Judy- Perhaps I'm missing something, but there should not be any need for adjustment. The number of pages will be based on the number of records being printed as long as you have not set a specific Print Area and you choose Active Sheet(s) in the Print dialog box (and assuming there is no other content elsewhere on the sheet). One thing you may want to add in your template, if not already done, is go to FilePage SetupSheet (not thru Print Preview) and specify Rows to Repeat at Top in the Print Titles section so that your captions will reprint at the top of the columns if more than one page of data is being printed. Please post back with more specifics if this is not sufficient. HTH |:) "judypudy" wrote: I have an Excel template in which I import data that is of varying records. Sometimes there are 3 records, sometimes there are 50 or 200 records. I do not know how to make a varying page Excel report. (For example, my clients sometimes want to see data on top 3 occupations, sometimes on top 50 occupations.) (The template is formatted, has headers, footers, a few links, works fine.) Using Excel 2002. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to place a border on every page in excel? | Excel Worksheet Functions | |||
move page up and down in Excel? | Excel Discussion (Misc queries) | |||
Export html page to Excel - support files folder | Excel Discussion (Misc queries) | |||
Excel Range Value issue (Excel 97 Vs Excel 2003) | Excel Discussion (Misc queries) | |||
Microsoft Access Report into Excel Spreadsheet | Excel Discussion (Misc queries) |