Can you be more specific? What is a specific cell or range that you gave a name to? And what you do you mean by "losing the location"? What was it when you created the name, and what is it now? What is the layout of the table and where did you add data?
The problem is that cells that have been given a name (for example A1 is now named Hours_Worked), are not holding the original position given. Meaning that after I name the cell, and resume testing the workbook, I will notice that the spreadsheet is not calculating properly. When I troubleshoot by looking at the Named Cell, it is no longer in A1, it is elsewhere (example is Z58). My theory is that when I added data to the range now table, it messed things up somehow.
Some background; I am modifying a workbook (that has worked in the past) by naming key cells. This is needed to help the person that inherits this workbook after I retire. So instead of A1+B1 in the formula, it will appear as Hours_Worked+Rate_of_Pay, for example. Most, but not all, of these formulas have Named Cells that pull their data from other worksheets. Some of the data is in a table, some of it is locked cells (meaning a cell like $A$1) in other sheets or in the worksheet where the formula resides. I first noticed the problem right after I added more data to the table. I believe that this data was not in a table per se, but in a range, I think it’s called. Anyway, it looked like a table. To try to fix the problem, I converted the range to a table, but this made no difference.
One other thing of note; I struggle with how to setup a cell in the formula such that it is either in a set location ($A$1) or it is the first cell in a column (for example) that I plan to copy into every cell in that column. What I have been doing is when creating the name, the cell that the name refers to is almost always something like $A1. This seems to allow me to copy the formula down the column without issue, until now.
Lastly, I had problems with sorting tables or ranges in the past and I discovered that you can’t do this in tables/ranges that have empty cells. I do not have empty cells, but I worry that my data might be scrambled again, especially since I added about 150 rows of data, about 10 columns across. I inserted the data into the middle of the range/table, thinking that this would retain the dynamic quality of the thing. This workbook was 99% complete UNTIL I added the new data and began testing. I also wonder if by sorting and Filtering the data as I was entering the new data, this has also impacted my named cells somehow. When I sort the data, some of the named cells no longer refer to the data they did before.
Thank you for your interest in helping me! I LOVE developing new tools at work, but admittedly still have a lot to learn.
