Table References revert to Cell References

[Falko26]

Hey Team,

I'm trying to reference a specific Column in a Table for my formula. This works perfectly the first time I set it up however when I save and close the workbook the references are changed to look at Cells instead.

First: =SUM(Data[Quantity]) - This works perfectly and if I drag down the formula stays consistent.

Now ill save and close the workbook then re-open.

The Formula has now change to say =SUM(Data!$E$3:$E$122)

Is there a way to force the formula to keep its Table References instead of switching to Cell references when you save and close?

Essentially I have a more advanced formula that references a Table and Column that works great when I first set it up with table and column names but then fails after I reopen the workbook and it reads the cell data instead.

As Show below the formula references the Table and Column. If I add a new variation to the Job Number column in the other sheet it shows up as a new line item here.

Now I closed and reopened the document, then added three numbers to the Job number Column but only one showed up in my list below.

That is because the range is not updating correctly in cells below B4. The Range Updates correctly in B4 as show below, but if I click on B8 you can see it is not referencing the entire Job Number Column like B4 is.

Cell B4 - Through Cells 106

Cell B8 - Only through cells 104

Thanks,

 

[Fluff]

I suspect that it's because your saving it as an xls file. Try saving as an xlsx instead.

 

[Falko26]

I suspect that it's because your saving it as an xls file. Try saving as an xlsx instead.

Fluff,

Thanks for the Reply, you are correct. saving it as a .xlsx did the trick! That was too easy.

 

[Fluff]

You're welcome & thanks for the feedback.