Adding a cover sheet on a separate tab with Excel output

[RMotiwalla]

Hi all!

i was wondering if there was a way to add in a cover sheet of sorts to be appended to the output?

I am basically sending Excel and PDF invoices/bills to customers and I want to be able to add a tab at the beginning that includes basic things such as the date, customer, invoice total, and the due date.

Also, if there is a way to make it dynamic (such as it inputs the correct date, grabs the customer name from within the file, and the dollar amount too).

Can someone please tell me the feasibility of this?

Thanks!

Rob

[CharlieS]

@RMotiwalla

These are all very feasible items to achieve with Alteryx Designer.. Additional named sheets can be added to Excels produced by Alteryx from either the Output tool or the Render tool. Which are you using to produce the Excel file?

To answer your question about the dynamic values, we need to know more about what the data input looks like. Could you provide an example workflow and/or sample data?

[RMotiwalla]

Hi CharlieS,

I am using the render tool to produce my output.

My workflow is huge and very convoluted, most of it was put together by someone with much more knowledge than I have.

Please see the screenshots, first is the original data, then the output (not much of a difference, but its a large workflow and if there is more than one invoice it totals those and puts them on a separate tab), and the cover which I hope the red items can be made dynamic.

Please let me know if there is any other information that would help!

Thanks so much!!!!

Rob

Original dataRe-totaled, tab named

Cover sheet. I would like the red items to be dynamic

[CharlieS · Accepted solution]

@RMotiwalla

I've put together the attached solution as one way to achieve the desired result. Here are a few notes on the process:

- Extracting the data from the first few rows used a lot of position-based processed. I might recommend having a separate Name:Value set for more consistent references. For Example A2 could be "Consolidated Invoice Number:" and B2 could be "1085527".

- Text(36) in my example shows how to reference dynamic text in the Report Text Tool. The variables can be inserted form the "Available Fields" function on that tool. 

- To produce multiple sheets from the Render tool, the "Section Breaks" mean "Different Sheets". If you want to specify these sheet names, look at Layout(40) in my example and note the use of the grouping and the selection of "Section Name" field at the bottom.

Let me know if you have any other questions.

[RMotiwalla]

Thanks so much CharlieS!!!

I had to do some tweaking (and not sure about the input data if I was supposed to be able to use that) but what you made is fantastic!

My only other question right now is how to make the cover sheet stay on the first tab? It worked fine when I tweaked yours, but then I added that to my own workflow and it keeps kicking it out to the back and it appears to be at the layout tool. do you have any idea how to order them?

Into the LayoutOut of the Layout

Thanks!

Rob

[CharlieS]

Happy to help!

I forgot to mention that the Sheet order is controlled by alphabetic order of the sheet names and not record order. That's why in my example I appended the prefix "INV:" to the invoice number to place it after "Cover".