Query Tables Does Not Fill Down Formulas When Refreshed 1

[Salut39]

I am using Excel 2007 and I don't seem to have a box in "External Data Properties" called "Fill Down Formulas In Columns Adjacent To Data" and my Query Tables Does Not Fill Down Formulas When Refreshed.

Does anybody know how to fix this?

Thanks
Yuri

 

[SkipVought]

Yuri,

Using Data > Get External Data > From Other Sources > From Microsoft Query... uses a different object set than Excel '97-2003 does, and consequently, this option is not available. You have a ListObject object and the ListObject object inherently contains this feature. This table, BTW, behaves the same as the Insert > Tables > Table, which is a Structured Table, an new featue of 2007+ and has features that are really neat and hugely useful. Your formulas WILL adjust to the size of the table, especially if you use the Structured Table Reference features in your formula.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

Hi Skip,

From my understanding even if this option is unavailable it should behave as if it's there? Or I need to explore different options? My data sourse in Accesss 2003.

Yuri

 

[SkipVought]

Are you using structured table references in your formula(s)?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

Yes

 

[SkipVought]

So then you have noticed that when you enter a formula in an adjacent cell, it automatically fills the entire column in the table AND automatically assigns a default HEADER value, yes?

BTW, when I refresh my MS Access table, the formulas DO adjust.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

in Insert > Tables > Table how can I include my external data into structured table? Sorry I only used formula which references to data table on the left.

 

[SkipVought]

You should not need to use Insert > tables > Table.

When you add your querytable via Data > Get External Data > From Other Sources > From Microsoft Query... , it makes a structured table for your resultset.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

Great!!!

It works, but I had to insert columns to the table rather than extend it.

Thanks a lot Skip!

 

[SkipVought]

What do you mean by that?

I can ADD a new query, that returns N columns and in the N+1 column, enter a formula in ANY row adjacent to the resultset, and THAT N+1 column becomes part of the structured table.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Salut39]

The table didn't pick up my formulas on the right automaticly I needed to extend it by dragging the corner of the last cell in the table or insert column within the range. All works fine. I understand the concept now.

Thank you