Structured reference turning into array after re-opining file - Can't use INDIRECT?

[dpnab]

I'm trying to link a cell in Sheet1 to a table in Sheet2.

So Basically formula in A2 is =Table1[Column1] and it "spills" however many rows there are in the table. This is perfect because it's a daily report and it will always be changing, this is what I want (to only return the amount of rows that are in the table).

The problem is once I save it and re-open the file, it turns into an array and changes the formula to include the original amount of rows for the table, ie (Sheet2!A2:A10). Sheet2 is also the name of the table.

So I figured I can use INDIRECT in the original formula, ie = INDIRECT(Table1[Column1]) but that just gives a #REF error.

What am I missing?

Thank you!

 

[Domenic]

INDIRECT returns a reference specified by a text string. So you'll need to wrap your reference within double quotes...

=INDIRECT("Table1[Column1]")

Hope this helps!

 

[dpnab]

INDIRECT returns a reference specified by a text string. So you'll need to wrap your reference within double quotes...

=INDIRECT("Table1[Column1]")

Hope this helps!

This works but it no longer spills the formula. If I put that formula in A3, it would just return the same result, ie only the first row. So what else can I do?

 

[Domenic]

I'm using Office 365, as I see you are, and it seems to work fine...

And , when I add new data to table, the spilled array automatically adjusts...

 

[DRSteele]

For what it's worth, my Excel works just fine too.