Excel 2007 Vlookup Using New Table Syntax

[jhcoxx]

Excel 2007, SP1 on Vista

I turned a rectangular region (with column headings) into a Table (for
ease of adding new rows, etc) in a workbook - it's Table15 in Program
List.xlsx

In a second workbook, when I use the worksheet Function Wizard to set
up a Vlookup using part of that table, it gives me syntax like

=VLOOKUP(A5,'Program List.xlsx'!Table15[[#All],[Program]:[Status]],
4,FALSE)

or

=VLOOKUP(A6,'Program List.xlsx'!Table15[[Program]:[Status]],4,FALSE)

(I think the [All] 'means look at the column headings, too')

Both work when the Program List.xlsx workbook is open - neither work
when it is closed.

Reverting to the older syntax for referring to a range, i.e.,

=VLOOKUP(A6,'[Program List.xlsx]Unique Composite'!$B$1:$E$959,4,FALSE)

works when the Program List.xlsx workbook is open AND when it is
closed.

Am I missing some element of the new Table syntax or is this behavior
a 'feature' of the new Tables in 2007?

Thanks for your help on this!

James

 

[Tyro]

It's called Structured References, Excel 2007's way of referring to table
entries.

Tyro

 

[jhcoxx]

Tyro -

Thanks for the name of the new feature - "Structured References" With
that lead, I've poked around a bit and now understand more about what
the syntax is, but I'm still not finding any mention of whether this
method of referencing is supposed to work when the Table is in a
closed workbook.

Given the hype about the new .xls* formats, it would seem that the
information needed to decode a reference to a Table would be available
somewhere in the XML structure and would be accessable without having
to have the workbook with the Table in it open, but that's not what
I'm seeing.

In fact, it seems that if the data range for a VLOOKUP is in
another .xlsx, even using a defined range name for the data range
fails if the workbook is closed. That is, naming the data range in
Program List.xlsx as Data_Range and using a cell formula of

=VLOOKUP(A15,'http://teams/sites/ts1302/Shared Documents/[Program
List.xlsx]Unique Composite'!$B$1:$E$959,4,FALSE)

works when Program List.xlsx is open but fails when it is closed.
That was not the behavior in the previous version of Excel, where both
the range name or the cell reference form of the range address would
work if the workbook they were in was closed.

Anyone know for certain that Structured Refereces into Tables won't
work with closed workbooks? It seems odd that both the older named
range and the newer Tables methods of getting data from have stopped
working - unless it was a design decision by MS....

James

It's called Structured References,Excel2007's way of referring totable
entries.

Excel2007, SP1 on Vista

I turned a rectangular region (with column headings) into aTable(for
ease of adding new rows, etc) in a workbook - it's Table15 in Program
List.xlsx

In a second workbook, when I use the worksheet Function Wizard to set
up a Vlookup using part of thattable, it gives mesyntaxlike

=VLOOKUP(A5,'Program List.xlsx'!Table15[[#All],[Program]:[Status]],
4,FALSE)

or

=VLOOKUP(A6,'Program List.xlsx'!Table15[[Program]:[Status]],4,FALSE)

(I think the [All] 'means look at the column headings, too')

Both work when the Program List.xlsx workbook is open - neither work
when it is closed.

Reverting to the oldersyntaxfor referring to a range, i.e.,

=VLOOKUP(A6,'[Program List.xlsx]Unique Composite'!$B$1:$E$959,4,FALSE)

works when the Program List.xlsx workbook is open AND when it is
closed.

Am I missing some element of the newTablesyntaxor is this behavior
a 'feature' of the new Tables in 2007?

Thanks for your help on this!

James- Hide quoted text -

- Show quoted text -