0

I am an excel user at the most basic level with a programming background who has been put on a nightmare project.

Here is what I need to produce:

  • Multi-page excel sheet which will be converted to PDF through the use of a third party tool.
  • Every page will have the same "header and footer", which I "quoted" because they are not in the actual header and footer because they need to reference named ranges on background sheets.
  • The first page will always be the same static text.
  • The last three pages will always be the same static text.
  • The middle pages will contain data from the "background" sheets which contain a bunch of raw data that gets populated every time the user runs the 3rd party tool.

My problem: how do I make these middle pages like a dynamic template where if the data from a report needs to spread to the next sheet this will happen dynamically leaving all the other pages intact? Should I use an excel template? Has anybody done anything like the before?

I'll just mention that the reports are coming from Salesforce and third party tool is Drawloop.

Reinier Torenbeek
  • 16,669
  • 7
  • 46
  • 69
rekordboy
  • 146
  • 1
  • 1
  • 5
  • 1
    Hard to say anything if you do not give a better description of the nature of the contents of the spreadsheet, or upload an example somewhere. – Reinier Torenbeek Apr 04 '13 at 22:04
  • Say for argument sake I have some raw data in one of the background sheets that has 6 columns. I want to take this raw data and display it in the main sheet, which will be one of the pages in the template I described above. If this hypothetical report has 50 rows it will not all fit on one page. I want it to dynamically push the data to a new page with the same header and footer as the rest of the document as described.--Sorry I can't upload an example due to sensitive nature of the data. – rekordboy Apr 04 '13 at 22:26
  • You could upload a scrubbed/anonymized copy of the data. What we are concerned with is the structure of the data/sheet/table/etc., not its contents. Right now nobody has any idea what you mean by "background sheets" or what they might contain, why they are used for the header & footer **and** the table data, etc., how they might be structured, etc. **Have you tried just forcing page breaks at a set number of rows?** Record a macro doing this, tweak it as needed. – David Zemens Apr 05 '13 at 01:37
  • Sorry, By background sheet I mean a sheet where some data from a salesforce report gets dumped in raw with headers. I hide the background sheet so that when I run Drawloop the ugly raw data page doesn't show up in the PDF output. I am attempting to extract all rows with only some of columns from the background sheet into the "output" sheet. And since the number of rows in the reports will always be different I need the data to expand to new pages as necessary. I have not tried anything with page breaks yet but I suspect the solution will involve something like that. – rekordboy Apr 07 '13 at 19:18

0 Answers0