Excel Output to Named Range

[rw9122]

I read the following post, but for some reason I am unable duplicate the creation of a named range. Nor am I able to use the Append Field Map feature (it's greyed out once the pop up box opens).

Link: https://community.alteryx.com/t5/Data-Preparation-Blending/Excel-Output-to-Named-Range/m-p/9644#M159...

Goal:

I'm pulling data in from 5 sources and want to write to a single Excel file (.xlsx) and either create a named range or table (I do not think I can crate a table, but a named range will work). I need to do this so that the 5 pivot table I create are always reading from the latest data set (which can increase or decrease week to week.

I'm open to other solutions if they exist. Maybe there's a better way to do this than create pivot tables off a named range?

Thanks!

[JoeM · Accepted solution]

@rw9122, to output to a named range, you'll have to be sure to 1) Create a named range inside the excel workbook before outputting to it from Alteryx 2) When prompted for a sheet name by Alteryx, name it exactly as you did in Excel 3) Select Output options > Overwrite sheet.

In terms of a solution, would it be better to just write out to a dedicated sheet in your excel workbook and point your pivot tables at that sheet as a source?

[rw9122]

I was able to get your recommendation working. However, creating a named range in Excel defines a "specific" range (Ex: A1:Z:700). The output of my routine is variable and unknown (week to week). So, a named range isn't practical unless I create a super huge range (my data is ~90K rows and 36 columns). I know Excel sheets have a format in which they can be created on the fly. I use another parsing tool and it is able to create any size range based on the current output.

Again, the reason this is important, and maybe there's a better way to handle this, is because I have 15+ pivot tables (reports) that are updated from the output. Pivot tables use a defined range (typically a table) to capture the data. If my data set goes beyond the Pivot Tables defined range, I lose information.

Thanks again for your assistance.

[JoeM]

Yes, the data size variance certainly adds a level of complexity. Just out of curiosity, are these pivot tables in a format replicable by the Alteryx pivot tools? If so, then you might not have to worry about inputting to a specific range and making sure all your pivot tables have a correct reference.

[rw9122]

I can reproduce them with AlterYX. However, management likes the ability to click on a pivot table field and have excel open a sheet with the relative data. I'm in a no win situation. But thank you for your response. I think this functionality is set as an 'Idea' and may get implemented in future releases. We know it's possible... the competition is doing it today. :)

[davidlhowes]

---

@JoeM wrote:

Yes, the data size variance certainly adds a level of complexity. Just out of curiosity, are these pivot tables in a format replicable by the Alteryx pivot tools? If so, then you might not have to worry about inputting to a specific range and making sure all your pivot tables have a correct reference.

---

Hi, have you tried naming the range in Excel using a dynamic reference, e.g. if your sheet is called "Data", the named range would be:

=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1)), this changes the size of the named range according to how many cells are populated in row 1 and column A.

[LisaL]

I have not been successful at using dynamic named ranges with Alteryx.  If you know how to make that work, please message me.

[Ykaty369]

davidIhowes I am having the same issue where I would like to preserve the formatting in my excel spreadsheet and therefore am using the Excel Legacy file format to delete & append my data to a named range in Excel.  Unfortunately because the size of my data changes monthly the named range size becomes a problem.  I tried naming the range using a dynamic reference and it doesn't work.  Instead a new tab is created in the workbook and the data is dumped there.

After Alteryx exports the data in Excel would it be possible to open the Excel file again and resize the named range so that it can be run again and again?

I'm running out of ideas so I thought I would ask.

[mat_ste_cb]

Thanks @JoeM for sharing knowledge,

Is any way to create named range in Excel from Alteryx?

I ask, because I have plenty of XLS files in folder and they are new in every quarter. It would be great if I can automate creating named range (use then as Alteryx desired output).

[davidlhowes]

If the number of columns in your named range doesn't change, the output is able to cope using the File Format: Microsoft Excel Legacy (*.xlsx) with Delete Data & Append as an Output Option.

It deletes all data, puts the new data in and the named range is automatically adjusted to the new number of rows.