I have a workbook in Excel 2010 which holds a data set and then a series of worksheets which based on a primary key return a series of lookups to the data-set and a set of logical statements returning different fields. I've set up each worksheet with its own Excel table. Some of the fields are overwritten by the 'user' to clean the data.
In creating the file I essentially copied the worksheets to build up the file. Key benefit to using the tables is that the formulae copy down automatically when the user appends a record to the bottom of the table. The problem is that the new formulas that auto-create reference are wrongly referencing OTHER tables in the workbook... Even if I overwrite the table references in the existing cells, as soon as I add a new row, it defaults back to other table references.
eg. =Table1[[#This Row],[mycolumnName]] would then recreate itself as Table2[[#This Row],[mycolumnName]]
Has anyone come across this issue? I'm loathed to recreate the whole thing from scratch and tried converting to ranges and then back to table but I can't seem to get it to work...
Any ideas?
In creating the file I essentially copied the worksheets to build up the file. Key benefit to using the tables is that the formulae copy down automatically when the user appends a record to the bottom of the table. The problem is that the new formulas that auto-create reference are wrongly referencing OTHER tables in the workbook... Even if I overwrite the table references in the existing cells, as soon as I add a new row, it defaults back to other table references.
eg. =Table1[[#This Row],[mycolumnName]] would then recreate itself as Table2[[#This Row],[mycolumnName]]
Has anyone come across this issue? I'm loathed to recreate the whole thing from scratch and tried converting to ranges and then back to table but I can't seem to get it to work...
Any ideas?
