1

I am trying to create a table of dynamic range that takes data from another sheet. The short story is that I need to do this because Alteryx will drop an old existing sheet of the same name when uploading new data each time that it is ran.

enter image description here

I am filling up a template from a pivot table formed from this table that takes the raw data (4 sheets total).

The Problem: The problem I have is with the Raw Data and the Table sheets. The Table Sheet needs to mimic the exact data of the Raw Data Sheet, but in table form. So there are two things to consider: the size of the raw data, and the actual data itself.

How might one do this?

And before I answer redundant questions:

Why don't you make a table on the raw data sheet? Because the sheet gets dropped each time Alteryx runs and will not save that there once existed a table there

Just say the cell references the other cell/ size references the size Yes, I get this, but I am making things dynamic on the table sheet

Make a giant table to take any size data range and map the expected columns also did this but if I told my college professor I did this as a professional analyst I would get slapped on the wrists

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
courtrhale
  • 19
  • 1
  • 3
  • You will want vba. You can record yourself doing the actions of copying/pasting the values and creating the table. Then manipulate the code created from recording your actions to find the extents of the data before copying and pasting and turning into a table. – Scott Craner Dec 06 '18 at 20:37
  • 2
    How about creating a query connection to the Raw Data - keep the Raw Data in a separate .xls file (or a txt/csv file if you prefer) and create a query connection using Get Data, From File. Then simply refresh the query whenever the raw data is changed. – chris neilsen Dec 06 '18 at 21:35
  • Are you trying to have Alteryx create the pivot table in Excel from the "Raw Data" sheet and write the pivot table into a new sheet in the same Excel file? If not, what part is Alteryx playing? What is a "template from a pivot table"? – Degan Dec 07 '18 at 21:34

1 Answers1

0

If you are using Excel 2016 or later, you can create a "Pivot Table":

Pivot Table button locataion

And choose where to place it, for example in another worksheet: Pivot Table wizard

Lucas Sousa
  • 401
  • 5
  • 18