Richard Schollar
MrExcel MVP
- Joined
- Apr 19, 2005
- Messages
- 23,707
Using: Excel 2013 64bit
I have been sent a worksheet by a colleague that contains two tables that in Name Manager are defined as follows:
Name: S.08.02.x.01
Scope: Workbook
Refers to: =S.08.02.a.01!$A$17:$U$270
and
Name: S.08.02.x.02
Scope: Workbook
Refers to: =S.08.02.a.02!$A$17:$Q$237
There are 5 tabs in the workbook but only 3 are relevant to the question:
S.08.02.a.01
S.08.02.a.02
Rec to BR TB
My issue (if such it is) is that there is a formula on the "Rec to BR TB" sheet that sums one of the fields in the S.08.02.x.01 table and it appears thus:
Cell 'Rec to BR TB'!U32
=+SUM(S.08.02[C0160])
This formula referencing the first table but how do I tell that from the formula above? I do not have a Table named S.08.02!!
The formula is pulling back the correct value.
If I create a new formula and select the same data field from the same Table, the formula is shown as:
=SUM(S.08.02.x.01[C0160])
which is what I would expect.
How has this happened? I am concerned because it has audit implications for this workbook. I *know* where the formula should be taking the data from but what if I didn't - how would I identify where it is?
UPDATE: OK, there was another sheet (Hidden) with the Table on it that this formula was referencing.
With the sheet Hidden, the Table did not appear in Name Manager. With the sheet Hidden Trace Precedents command resulted in a message that "Trace Precedents command requires that the active cell contains a formula which includes valid references" and hence did not reveal the Hidden sheet.
Is this behaviour by design? It's very annoying - I would expect at least some notification indicating that there was a hidden sheet/table.
Is it a result of using 64bot Excel?
I have been sent a worksheet by a colleague that contains two tables that in Name Manager are defined as follows:
Name: S.08.02.x.01
Scope: Workbook
Refers to: =S.08.02.a.01!$A$17:$U$270
and
Name: S.08.02.x.02
Scope: Workbook
Refers to: =S.08.02.a.02!$A$17:$Q$237
There are 5 tabs in the workbook but only 3 are relevant to the question:
S.08.02.a.01
S.08.02.a.02
Rec to BR TB
My issue (if such it is) is that there is a formula on the "Rec to BR TB" sheet that sums one of the fields in the S.08.02.x.01 table and it appears thus:
Cell 'Rec to BR TB'!U32
=+SUM(S.08.02[C0160])
This formula referencing the first table but how do I tell that from the formula above? I do not have a Table named S.08.02!!
The formula is pulling back the correct value.
If I create a new formula and select the same data field from the same Table, the formula is shown as:
=SUM(S.08.02.x.01[C0160])
which is what I would expect.
How has this happened? I am concerned because it has audit implications for this workbook. I *know* where the formula should be taking the data from but what if I didn't - how would I identify where it is?
UPDATE: OK, there was another sheet (Hidden) with the Table on it that this formula was referencing.
With the sheet Hidden, the Table did not appear in Name Manager. With the sheet Hidden Trace Precedents command resulted in a message that "Trace Precedents command requires that the active cell contains a formula which includes valid references" and hence did not reveal the Hidden sheet.
Is this behaviour by design? It's very annoying - I would expect at least some notification indicating that there was a hidden sheet/table.
Is it a result of using 64bot Excel?