Table Reference Formula Being Replaced

[preitano]

Hi,

I have a table that contains a fairly simple formula to get the name of a dorm building from a provided string:
=LEFT([On-Campus Residence],[First /]-1)
The formula only exists within the table where it is being used.

The formula works fine and is added to future rows. However, it somehow gets translated to:
=LEFT('Student Cases'!$F$6:$F$1510,'Student Cases'!$O$6:$O$1510-1)
I'm unsure of when they change, but when I look back they have all been changed.

While this new formula still functions, the address range is expanded sometimes, but not always. I've written a macro to insert the correct formula (it's actually a set of 5 that work together), but these eventually get replaced as well.

Is there something wrong with using a formula that references table columns by name when the table will expand?

Thank you

 

[jdellasala]

Go to File -> Options, click Formulas on the left, and under Working with formulas make sure Use Table Names in Formulas is checked.

I think that should solve the problem!

 

[preitano]

Go to File -> Options, click Formulas on the left, and under Working with formulas make sure Use Table Names in Formulas is checked.

I think that should solve the problem!

Thanks. I had checked that first (sorry, I should have mentioned that), and I have other table formulas in the same workbook that seem to repopulate properly.

 

[jdellasala]

Thanks. I had checked that first (sorry, I should have mentioned that), and I have other table formulas in the same workbook that seem to repopulate properly.

When you select a cell in the table, does the Table Design tab appear? If not, it may look like a table but it's not (an Excel) Table!

 

[preitano]

When you select a cell in the table, does the Table Design tab appear? If not, it may look like a table but it's not (an Excel) Table!

The Design tab does appear and I can see the table name (StudentCases) on the ribbon. Also, the formulas work properly with the column name references.

I just did a test and noticed that after saving the workbook there's no change in the formula (=LEFT([On-Campus Residence],[First /]-1)), but after closing and reopening the file they are replaced again (=LEFT('Student Cases'!$F$6:$F$1548,'Student Cases'!$O$6:$O$1548-1)).

It also seems odd to me that the references are all absolute in the replacement version.

 

[jdellasala]

The Design tab does appear and I can see the table name (StudentCases) on the ribbon. Also, the formulas work properly with the column name references.

I just did a test and noticed that after saving the workbook there's no change in the formula (=LEFT([On-Campus Residence],[First /]-1)), but after closing and reopening the file they are replaced again (=LEFT('Student Cases'!$F$6:$F$1548,'Student Cases'!$O$6:$O$1548-1)).

It also seems odd to me that the references are all absolute in the replacement version.

I'm kind of stumped, but one thing I notice is the formula

=LEFT([On-Campus Residence],[First /]-1)

is not within the table. The [] refers to an entire column which would be a spilled array, but they're not allowed within a table.

I've tried to reproduce the problem putting spilled table column formulas in various locations on the Worksheet with the Table as well as other Worksheets, but can't replicate it! Maybe if you used XL2BB and posted at least part of the table itself? Are there any Named ranges that might be conflicting? Otherwise, I'm stumped!