Table referencing

[Richard Schollar]

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?

 

[igold]

Hi Richard,

I do not have as answer to your post.

However, on more than one occasion, I have used a portion of code that you have written to write workable codes for someone else.

Even though I have always credited you where it was appropriate, I would like to take this opportunity to say "Thank You" for your plethora of posts that I have benefited from.

Thank You!

Regards,

igold

 

[JoeMo]

Using: Excel 2013 64bit

Is it a result of using 64bot Excel?

Richard,

In a quick test using 32-bit Excel (2010) I get similar results in that the Name Manager doesn't list the table when the sheet it is on is hidden. When I create a simple formula on another sheet that sums a column (header "Hdr2") in the Table (named JoeTbl) I get "=SUM(JoeTbl[hdr2])". Note there is no reference to the sheet the table is on which is OK since the Table has workbook-level scope, but is different than what you see.