Reference a sheet with dynamic data that doesn't exist (yet)

[SB3D]

Hello All, first post!

Situation:
Data is generated externally and a script is exporting this data (and some screenshots) to an existing excel file in which it will automatically create a new sheet (SheetA) to plot the data in.
The data rows in SheetA might vary between 1 or 2000 depending on the day, and the data is not "pivot table proof" yet. In sheet B and C I refer to sheet A for some formula's and pivot tables.

I'm trying to find the easiest way to make use of the data in Sheet A without having to make references to this New Sheet in my formula's and pivot tables every time.

So basically its 3 problems in one
1 The Sheet A I want to reference to doesn't exist yet.
2 The data in sheet A might vary from 1-2000 lines.
3 The data in sheet A is not "pivot table proof" as it doesn't have the correct headers and has some blanks etc.


My current solution:
In Sheet B I refer to the none existing data in SheetA by using Indirect.

=INDIRECT($A$1&"!"&CELL("address";A3))

Where A1 is the name if the sheet that doesnt exist yet. and A3 the cell which I refer to. This works great but as sheet A has 3 columns and might have 2000 rows I need 6000 indirect references just in case the data is there. All other cells ofcourse show #ref

This optimized data in SheetB is the input for the pivot table in SheetC

So Yes it works, if I hit refresh in the pivot table it automatically gets the data from Sheet A, sorts it in sheet B and displays it in the Pivot table in sheetC but I was wondering if there is better strategy to speed things up?

I already tried to create a dynamic range using the name manager for Sheet B but Sheet is not actually that dynamic as it has the 6000 cells with indirect formula's so that's not really working.

Thanks in advance!

 

[MrSyyr]

I just tried a small version of this. I defined DataRange in Name Manager as =INDIRECT(SheetName&"!$A$1:$J$20"). SheetName points to a cell with "Sheet5" in it. Sheet5 doesn't exist, so when I put =DataRange in the equivalent cells in Sheet4, then I got your #Ref errors. When I created Sheet5 and put some data in it, then it showed in Sheet4. If you want to tidy it up, you could put =iferror(DataRange,"") in the cells in Sheet4.
Any good?