Excel 2007 :: Inconsistent addresses for tables?

[Guest]

I’ve noticed something which may perhaps indicate a serious error in Excel
2007: When you create a table from a range of data with descriptive headers
in the first row, the table is automatically named, and the reference
(address) associated with the name appears to include the first row (as it
should, I suppose).

However, if you look in Formulas/Name Manager, the reference of the name
does not include the first row (i.e. the headers). I discovered this anomaly
while searching for a possible reason why Excel 2007 apparently fails to
‘see’ the names of defined tables in external files, e.g. when I want to
create a pivot table in workbook A from the data in a table in workbook B, or
when I want to query the contents in a table in workbook B from workbook A.

I’ve never encountered similar problems with Excel 2003, and Excel 2007 has
no problems with seeing list names in external Excel 2003 workbooks (or with
seeing manually defined range names in Excel 2007 workbooks).
Is something wrong with Excel 2007 or am I just a fool?

 

[Jan Karel Pieterse]

Hi Ulrik,

However, if you look in Formulas/Name Manager, the reference of the name
does not include the first row (i.e. the headers). I discovered this anomaly
while searching for a possible reason why Excel 2007 apparently fails to
‘see’ the names of defined tables in external files, e.g. when I want to
create a pivot table in workbook A from the data in a table in workbook B, or
when I want to query the contents in a table in workbook B from workbook A.

It is confusing indeed. I was able to get data from an external 2007 workbook
into a pivottable using the table name however, but the syntax is different from
what you would expect.

Normally you would refer to a range name using: [WorkbookName.xls]!TableName
But although a table is represented by a range name, you should not use the
range name syntax as the source. Rather you must use this:

WorkbookName!TableName

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Guest]

Hi Jan,
I could make a pivottable from an external workbook the way you suggested,
but I still wonder why Excel cannot 'see' the table name when I try 'Use an
external data source' option.

Similarly, I cannot use the MS Query guide to access a table in an external
workbook, because the table name is not listed.

Regards,
Ulrik


--
Ulrik Gerdes

Hi Ulrik,

However, if you look in Formulas/Name Manager, the reference of the name
does not include the first row (i.e. the headers). I discovered this anomaly
while searching for a possible reason why Excel 2007 apparently fails to
‘see’ the names of defined tables in external files, e.g. when I want to
create a pivot table in workbook A from the data in a table in workbook B, or
when I want to query the contents in a table in workbook B from workbook A.

It is confusing indeed. I was able to get data from an external 2007 workbook
into a pivottable using the table name however, but the syntax is different from
what you would expect.

Normally you would refer to a range name using: [WorkbookName.xls]!TableName
But although a table is represented by a range name, you should not use the
range name syntax as the source. Rather you must use this:

WorkbookName!TableName

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Guest]

Hi again Jan,

I've discovered an interesting thing (perhaps) about names of tables and
ranges. Please mail me, so that I can send you a demo-workbook.

Regards,
Ulrik ([email protected]).



--
Ulrik Gerdes

Hi Jan,
I could make a pivottable from an external workbook the way you suggested,
but I still wonder why Excel cannot 'see' the table name when I try 'Use an
external data source' option.

Similarly, I cannot use the MS Query guide to access a table in an external
workbook, because the table name is not listed.

Regards,
Ulrik


--
Ulrik Gerdes

Hi Ulrik,

However, if you look in Formulas/Name Manager, the reference of the name
does not include the first row (i.e. the headers). I discovered this anomaly
while searching for a possible reason why Excel 2007 apparently fails to
‘see’ the names of defined tables in external files, e.g. when I want to
create a pivot table in workbook A from the data in a table in workbook B, or
when I want to query the contents in a table in workbook B from workbook A.

It is confusing indeed. I was able to get data from an external 2007 workbook
into a pivottable using the table name however, but the syntax is different from
what you would expect.

Normally you would refer to a range name using: [WorkbookName.xls]!TableName
But although a table is represented by a range name, you should not use the
range name syntax as the source. Rather you must use this:

WorkbookName!TableName

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com

 

[Jan Karel Pieterse]

Hi Ulrik,

Similarly, I cannot use the MS Query guide to access a table in an external
workbook, because the table name is not listed.

In my opinion, this is something that would need fixing.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com