Office 2007 Trace Precedents + Tables
 

I use formula references between sheets to table values. For instance,
"=BoothCoated[[#Totals],[sf]]". But when I click the "trace precedents"
button, a popup says that valid references are required.

How can I make that trace precedents command work properly with table
references so that auditing spreadsheets is more automated?

Office 2007 Trace Precedents + Tables
 

I cannot recreate the problem. Are you sure sf is a valid column
name?


On Nov 19, 8:21 am, TKS_Mark
wrote:
I use formula references between sheets to table values. For instance,
"=BoothCoated[[#Totals],[sf]]". But when I click the "trace precedents"
button, a popup says that valid references are required.

How can I make that trace precedents command work properly with table
references so that auditing spreadsheets is more automated?

Office 2007 Trace Precedents + Tables
 

Yes, sf is a valid column name. I just clicked F5 (go to) and pasted the
reference "BoothCoated[[#Totals],[sf]]" into the go to field and it works
fine. For some reason, it just doesn't work in the trace precendents.

Maybe you're using sf as the default total column in the right field. Try
making it one of the middle columns in a table instead of the right. Then
you will have to click the drop-down list to choose Sum on your own. Also,
the table is in another sheet in my workbook. The formula that refers to the
table is in its own table.

One or all those items in the above paragraph might make this problem occur.

"iliace" wrote:

I cannot recreate the problem. Are you sure sf is a valid column
name?


On Nov 19, 8:21 am, TKS_Mark
wrote:
I use formula references between sheets to table values. For instance,
"=BoothCoated[[#Totals],[sf]]". But when I click the "trace precedents"
button, a popup says that valid references are required.

How can I make that trace precedents command work properly with table
references so that auditing spreadsheets is more automated?

Office 2007 Trace Precedents + Tables
 

OK, here is what I've tried.

1. I have a table total down at the bottom of a column from a table
I'm using, which I renamed cf. It is the third column from the right,
not the last column in the table. Formula I'm using is Sum, from the
dropdown list. Tracing precedents for the subtotal works with no
problem.
2. I refer to the subtotal outside of the table. Tracing precedent
for this cell works with no problem.
3. I created a new column (Column 1) and moved it before my cf
column. It has a column-wide formula of =Table1[[#Totals],[sf]] and I
can trace precedents for this as well.

Am I not covering your scenario, with those three variations?


On Nov 19, 1:04 pm, TKS_Mark
wrote:
Yes, sf is a valid column name. I just clicked F5 (go to) and pasted the
reference "BoothCoated[[#Totals],[sf]]" into the go to field and it works
fine. For some reason, it just doesn't work in the trace precendents.

Maybe you're using sf as the default total column in the right field. Try
making it one of the middle columns in a table instead of the right. Then
you will have to click the drop-down list to choose Sum on your own. Also,
the table is in another sheet in my workbook. The formula that refers to the
table is in its own table.

One or all those items in the above paragraph might make this problem occur.



"iliace" wrote:
I cannot recreate the problem. Are you sure sf is a valid column
name?

On Nov 19, 8:21 am, TKS_Mark
wrote:
I use formula references between sheets to table values. For instance,
"=BoothCoated[[#Totals],[sf]]". But when I click the "trace precedents"
button, a popup says that valid references are required.

How can I make that trace precedents command work properly with table
references so that auditing spreadsheets is more automated?- Hide quoted text -

- Show quoted text -

Office 2007 Trace Precedents + Tables
 

You covered all scenarios except for one. The two tables are in two
different sheets in the same workbook. I just now moved my problem table
into the same sheet as the problem reference. Then when I tried to trace, it
works just fine. It won't work when the two tables are in different sheets.

"iliace" wrote:

OK, here is what I've tried.

1. I have a table total down at the bottom of a column from a table
I'm using, which I renamed cf. It is the third column from the right,
not the last column in the table. Formula I'm using is Sum, from the
dropdown list. Tracing precedents for the subtotal works with no
problem.
2. I refer to the subtotal outside of the table. Tracing precedent
for this cell works with no problem.
3. I created a new column (Column 1) and moved it before my cf
column. It has a column-wide formula of =Table1[[#Totals],[sf]] and I
can trace precedents for this as well.

Am I not covering your scenario, with those three variations?


On Nov 19, 1:04 pm, TKS_Mark
wrote:
Yes, sf is a valid column name. I just clicked F5 (go to) and pasted the
reference "BoothCoated[[#Totals],[sf]]" into the go to field and it works
fine. For some reason, it just doesn't work in the trace precendents.

Maybe you're using sf as the default total column in the right field. Try
making it one of the middle columns in a table instead of the right. Then
you will have to click the drop-down list to choose Sum on your own. Also,
the table is in another sheet in my workbook. The formula that refers to the
table is in its own table.

One or all those items in the above paragraph might make this problem occur.



"iliace" wrote:
I cannot recreate the problem. Are you sure sf is a valid column
name?

On Nov 19, 8:21 am, TKS_Mark
wrote:
I use formula references between sheets to table values. For instance,
"=BoothCoated[[#Totals],[sf]]". But when I click the "trace precedents"
button, a popup says that valid references are required.

How can I make that trace precedents command work properly with table
references so that auditing spreadsheets is more automated?- Hide quoted text -

- Show quoted text -

Office 2007 Trace Precedents + Tables
 

Ah! Multiple tables - confirmed. It does work with GETPIVOTDATA()
formulas, or using a non-structured A1-style reference to a different
sheet.


On Nov 19, 3:20 pm, TKS_Mark
wrote:
You covered all scenarios except for one. The two tables are in two
different sheets in the same workbook. I just now moved my problem table
into the same sheet as the problem reference. Then when I tried to trace, it
works just fine. It won't work when the two tables are in different sheets.



"iliace" wrote:
OK, here is what I've tried.

1. I have a table total down at the bottom of a column from a table
I'm using, which I renamed cf. It is the third column from the right,
not the last column in the table. Formula I'm using is Sum, from the
dropdown list. Tracing precedents for the subtotal works with no
problem.
2. I refer to the subtotal outside of the table. Tracing precedent
for this cell works with no problem.
3. I created a new column (Column 1) and moved it before my cf
column. It has a column-wide formula of =Table1[[#Totals],[sf]] and I
can trace precedents for this as well.

Am I not covering your scenario, with those three variations?

On Nov 19, 1:04 pm, TKS_Mark
wrote:
Yes, sf is a valid column name. I just clicked F5 (go to) and pasted the
reference "BoothCoated[[#Totals],[sf]]" into the go to field and it works
fine. For some reason, it just doesn't work in the trace precendents.

Maybe you're using sf as the default total column in the right field. Try
making it one of the middle columns in a table instead of the right. Then
you will have to click the drop-down list to choose Sum on your own. Also,
the table is in another sheet in my workbook. The formula that refers to the
table is in its own table.

One or all those items in the above paragraph might make this problem occur.

"iliace" wrote:
I cannot recreate the problem. Are you sure sf is a valid column
name?

On Nov 19, 8:21 am, TKS_Mark
wrote:
I use formula references between sheets to table values. For instance,
"=BoothCoated[[#Totals],[sf]]". But when I click the "trace precedents"
button, a popup says that valid references are required.

How can I make that trace precedents command work properly with table
references so that auditing spreadsheets is more automated?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Office 2007 Trace Precedents + Tables
 

Will MicroSoft fix this in an upcoming release?

"iliace" wrote:

Ah! Multiple tables - confirmed. It does work with GETPIVOTDATA()
formulas, or using a non-structured A1-style reference to a different
sheet.


On Nov 19, 3:20 pm, TKS_Mark
wrote:
You covered all scenarios except for one. The two tables are in two
different sheets in the same workbook. I just now moved my problem table
into the same sheet as the problem reference. Then when I tried to trace, it
works just fine. It won't work when the two tables are in different sheets.



"iliace" wrote:
OK, here is what I've tried.

1. I have a table total down at the bottom of a column from a table
I'm using, which I renamed cf. It is the third column from the right,
not the last column in the table. Formula I'm using is Sum, from the
dropdown list. Tracing precedents for the subtotal works with no
problem.
2. I refer to the subtotal outside of the table. Tracing precedent
for this cell works with no problem.
3. I created a new column (Column 1) and moved it before my cf
column. It has a column-wide formula of =Table1[[#Totals],[sf]] and I
can trace precedents for this as well.

Am I not covering your scenario, with those three variations?

On Nov 19, 1:04 pm, TKS_Mark
wrote:
Yes, sf is a valid column name. I just clicked F5 (go to) and pasted the
reference "BoothCoated[[#Totals],[sf]]" into the go to field and it works
fine. For some reason, it just doesn't work in the trace precendents.

Maybe you're using sf as the default total column in the right field. Try
making it one of the middle columns in a table instead of the right. Then
you will have to click the drop-down list to choose Sum on your own. Also,
the table is in another sheet in my workbook. The formula that refers to the
table is in its own table.

One or all those items in the above paragraph might make this problem occur.

"iliace" wrote:
I cannot recreate the problem. Are you sure sf is a valid column
name?

On Nov 19, 8:21 am, TKS_Mark
wrote:
I use formula references between sheets to table values. For instance,
"=BoothCoated[[#Totals],[sf]]". But when I click the "trace precedents"
button, a popup says that valid references are required.

How can I make that trace precedents command work properly with table
references so that auditing spreadsheets is more automated?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Office 2007 Trace Precedents + Tables
 

I worry that referring to an A1-style reference will end up being butchered
if I extend the table using the new handles that MS provides. It won't be as
dynamic as before and could ruin my references.

"iliace" wrote:

Ah! Multiple tables - confirmed. It does work with GETPIVOTDATA()
formulas, or using a non-structured A1-style reference to a different
sheet.


On Nov 19, 3:20 pm, TKS_Mark
wrote:
You covered all scenarios except for one. The two tables are in two
different sheets in the same workbook. I just now moved my problem table
into the same sheet as the problem reference. Then when I tried to trace, it
works just fine. It won't work when the two tables are in different sheets.



"iliace" wrote:
OK, here is what I've tried.

1. I have a table total down at the bottom of a column from a table
I'm using, which I renamed cf. It is the third column from the right,
not the last column in the table. Formula I'm using is Sum, from the
dropdown list. Tracing precedents for the subtotal works with no
problem.
2. I refer to the subtotal outside of the table. Tracing precedent
for this cell works with no problem.
3. I created a new column (Column 1) and moved it before my cf
column. It has a column-wide formula of =Table1[[#Totals],[sf]] and I
can trace precedents for this as well.

Am I not covering your scenario, with those three variations?

On Nov 19, 1:04 pm, TKS_Mark
wrote:
Yes, sf is a valid column name. I just clicked F5 (go to) and pasted the
reference "BoothCoated[[#Totals],[sf]]" into the go to field and it works
fine. For some reason, it just doesn't work in the trace precendents.

Maybe you're using sf as the default total column in the right field. Try
making it one of the middle columns in a table instead of the right. Then
you will have to click the drop-down list to choose Sum on your own. Also,
the table is in another sheet in my workbook. The formula that refers to the
table is in its own table.

One or all those items in the above paragraph might make this problem occur.

"iliace" wrote:
I cannot recreate the problem. Are you sure sf is a valid column
name?

On Nov 19, 8:21 am, TKS_Mark
wrote:
I use formula references between sheets to table values. For instance,
"=BoothCoated[[#Totals],[sf]]". But when I click the "trace precedents"
button, a popup says that valid references are required.

How can I make that trace precedents command work properly with table
references so that auditing spreadsheets is more automated?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Office 2007 Trace Precedents + Tables
 

I have no idea what Microsoft will do about it - I'm assuming they'll
continue improving structured references, since it's new and hot.

Strictly for formula auditing purposes, you could save a copy of the
workbook, convert tables to range, ensure that formulas are accurate,
and revert back to original with structured references.


On Nov 19, 3:50 pm, TKS_Mark
wrote:
I worry that referring to an A1-style reference will end up being butchered
if I extend the table using the new handles that MS provides. It won't be as
dynamic as before and could ruin my references.



"iliace" wrote:
Ah! Multiple tables - confirmed. It does work with GETPIVOTDATA()
formulas, or using a non-structured A1-style reference to a different
sheet.

On Nov 19, 3:20 pm, TKS_Mark
wrote:
You covered all scenarios except for one. The two tables are in two
different sheets in the same workbook. I just now moved my problem table
into the same sheet as the problem reference. Then when I tried to trace, it
works just fine. It won't work when the two tables are in different sheets.

"iliace" wrote:
OK, here is what I've tried.

1. I have a table total down at the bottom of a column from a table
I'm using, which I renamed cf. It is the third column from the right,
not the last column in the table. Formula I'm using is Sum, from the
dropdown list. Tracing precedents for the subtotal works with no
problem.
2. I refer to the subtotal outside of the table. Tracing precedent
for this cell works with no problem.
3. I created a new column (Column 1) and moved it before my cf
column. It has a column-wide formula of =Table1[[#Totals],[sf]] and I
can trace precedents for this as well.

Am I not covering your scenario, with those three variations?

On Nov 19, 1:04 pm, TKS_Mark
wrote:
Yes, sf is a valid column name. I just clicked F5 (go to) and pasted the
reference "BoothCoated[[#Totals],[sf]]" into the go to field and it works
fine. For some reason, it just doesn't work in the trace precendents.

Maybe you're using sf as the default total column in the right field. Try
making it one of the middle columns in a table instead of the right. Then
you will have to click the drop-down list to choose Sum on your own. Also,
the table is in another sheet in my workbook. The formula that refers to the
table is in its own table.

One or all those items in the above paragraph might make this problem occur.

"iliace" wrote:
I cannot recreate the problem. Are you sure sf is a valid column
name?

On Nov 19, 8:21 am, TKS_Mark
wrote:
I use formula references between sheets to table values. For instance,
"=BoothCoated[[#Totals],[sf]]". But when I click the "trace precedents"
button, a popup says that valid references are required.

How can I make that trace precedents command work properly with table
references so that auditing spreadsheets is more automated?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -