External Data Table - Formula

[benjennings]

Hi

I've recently had 2007 installed (after complaining for the last few years) and am having fun getting used to it.

I've got a MS Query that returns data from our accounts (general ledger) and I want to write a formula from a report that lookups this table. I can easily use 'lookups' or 'index' or 'match' etc but since installing 2007 I've notice that the references to the table within a formula are often laid out differently (as per below).

=Table_TB_Query_GLPCT_GLPGL[[#This Row],[CTACCT]]

An example of the table is below:

<TABLE style="WIDTH: 420pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=558><COLGROUP><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 200pt; mso-width-source: userset; mso-width-alt: 9728" width=266><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" span=2 width=81><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 98pt; FONT-FAMILY: Arial; BACKGROUND: #4f81bd; HEIGHT: 12.75pt; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" height=17 width=130>CTACCT</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 200pt; FONT-FAMILY: Arial; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=266>CTDESC</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 61pt; FONT-FAMILY: Arial; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=81>GLMN01</TD><TD style="BORDER-BOTTOM: white 1.5pt solid; BORDER-LEFT: #d4d0c8; WIDTH: 61pt; FONT-FAMILY: Arial; BACKGROUND: #4f81bd; COLOR: white; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 700; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #4F81BD none" width=81>GLMN02</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; HEIGHT: 12.75pt; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" height=17>016-00-000-1001-000</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none">PETTY CASH</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>-413.32</TD><TD style="BORDER-BOTTOM: white 0.5pt solid; BORDER-LEFT: #d4d0c8; FONT-FAMILY: Arial; BACKGROUND: #b8cce4; COLOR: black; FONT-SIZE: 10pt; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; BORDER-RIGHT: white 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: #B8CCE4 none" align=right>44.63</TD></TR></TBODY></TABLE>

Can anyone advise me the best formula's to use, as I will be using the formula many times throughout the reports, to return the answer from the table.

I did use the following formula, all but the first 'match' refers via the table name, and it does return the right answer but I feel like there is probably a better/easier way or a way of getting every part of the formula to refer to the table name?

=INDEX(Table_TB_Query_GLPCT_GLPGL[#All],MATCH("016-50-550-4722-300",A:A,FALSE),MATCH("GLMN01",Table_TB_Query_GLPCT_GLPGL[#Headers],FALSE))

Many thanks for any help.

Ben