Office 2007 Trace Precedents + Tables

[Guest]

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?

 

[iliace]

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

 

[Guest]

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.

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

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?

 

[iliace]

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?

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.

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

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 -

 

[Guest]

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.

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?

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.

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

On Nov 19, 8:21 am, TKS_Mark <[email protected]>
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 -

 

[iliace]

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

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.

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?

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.
:
I cannot recreate the problem. Are you sure sf is a valid column
name?
On Nov 19, 8:21 am, TKS_Mark <[email protected]>
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 -

 

[Guest]

Will MicroSoft fix this in an upcoming release?

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

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.

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 <[email protected]>
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.

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

On Nov 19, 8:21 am, TKS_Mark <[email protected]>
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 -

 

[Guest]

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.

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

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.

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 <[email protected]>
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.

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

On Nov 19, 8:21 am, TKS_Mark <[email protected]>
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 -

 

[iliace]

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.

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.

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

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.
:
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 <[email protected]>
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.
:
I cannot recreate the problem. Are you sure sf is a valid column
name?
On Nov 19, 8:21 am, TKS_Mark <[email protected]>
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 -