Excel table lagging with a table header cell - Cell reference doesn't work. At All.

Question

I got a few of Microsoft's Excel 365 templates to create a customized personal finance tracker, and when I was changing a table header of one of the sheets to reference a cell above which was a date, so I could pull up the month as a criteria for a sumif, excel started to act weirdly.

For instance, cell reference stopped working. No matter what I typed, either the cell would change its value to "jan-00" or to 0 or some other value, instead of referencing the cell. It doesn't even show the reference cell as text or an error. It goes to those values. I already checked data validation, and there is no value there. Also, by looking at other collumns, I could see there was no single formula beforehand.

Also, even if I try to change "jan-00" to "jan-22" or anything else by typing, it doesn't change. If I try to change the type of the data, it will only change its formating, but won't change the data.

Can someone help me figure out what is going on?

Thanks

Answer 1

NO SYSTEM RESTORE OR OTHER REPAIRS NEED TO BE PERFORMED!

The behavior is normal and by design:

A table converts anything that you type into the headings into a unique text!

Tables have been designed for data processing, therefore the headings should never change and must be unique.
This is the only way they can work as link between Excel and Power Query / Power Pivot.

Andreas.

Answer 2

Hello Thiago M,

Thank you for the update.

If those steps mentioned above will not work you can proceed with the system restore which means that the device will go back to the time when it was working well. Nothing to worry because no files will remove to the device.

Just right click the start menu and choose run.
In the run box please type in: rstrui and hit OK. This will bring you to a window to select a restore point (System restore window)

Kindly select the date where you suspect that the issue happened then click next, NOTE: to show more restore points - TICK the box that says show more restore points at the bottom left of the window ) > After clicking Next this will bring another window, just click next to perform the system restore.

I hope this information helps. If you have any questions, please let me know and I’ll be glad to assist you further.

Best regards,
Jung
Independent Advisor

Answer 3

Hi Jung, I just tried method 1 2 and 3, and none of them are working. I was able to create an excel and get the same problem.

I will attach a picture of error excel. There you will find 2 tables. What I was trying to do was reference one table header with another cell. In this example, I created 2 tables, one below the other. In this case I made the headers of the first table to be dates. And I wanted to make the second table have headers referencing the first tables' header.

If you see, G11 should have ''=G5'' in its formula bar. Instead, it has something else.

If you have additional suggestions, I will be glad to be trying them.

I will be trying the next 4th method above, although none of the first one started.

Answer 4

Hi Thiago,

Thank you for the update. May I know what happened? Have you try to perform each step? Can you provide the updates for each step that you've done?

Best regards,
Jung
Independent Advisor

Answer 5

Hi Thiago,

I’m Jung one of the Independent Advisor and I’d be happy to help you out with your question.

If this concern is also occurring in other Excel files, we can say the problem is with the application itself. Therefore, we suggest that you perform all primary troubleshooting steps to resolve this concern. Please follow each method below and get back to us with the results:

Method 1: Install Office Update.

Open Excel.
Click on File.
Go to Account.
Under Product Information, choose Update Options and click Update Now.
Once the updates completely installed, relaunch Excel and see if the problem occurs.

Method 2: Open Excel in safe mode.

Press the Windows and R key on your keyboard simultaneously.
In the Run command, type excel /safe (include space between excel and / symbol) and then click OK.
Once the Excel app opens in safe mode, observe the issue if it is persisting.

If the issue doesn’t occur while in Safe Mode, then an add-in is likely causing it. To disable add-ins, please access this link and follow the steps under Permanently disable or remove an add-in.

Note: To stop Office Safe Mode, just exit and restart your Excel application.

Method 3: Repair Office program.
Right-click the Start button (lower-left corner).
Select Apps and Features.
Right-click the Microsoft Office program and select Change and then choose Online Repair.
Click Repair and follow the on-screen instruction to continue. Restart your computer and check if the concern is still occurring.

Method 4: Uninstall and Reinstall Office program.
Uninstall Office from a PC: https://support.microsoft.com/help/9dd49b83-264a-477a-8fcc-2fdf5dbf61d8?ui=en-us&rs=en-us&ad=us

Download and install or reinstall Office 365/Office 2016 or Office 2019 on a PC or Mac:
https://support.microsoft.com/help/4414eaaf-0478-48be-9c42-23adc4716658?ui=en-us&rs=en-us&ad=us

Method 5: Create a new user profile.
Try to create a new user profile on the device and check if the issue will be the same.
https://support.microsoft.com/help/20de74e0-ac7f-3502-a866-32915af2a34d

I hope this information helps. If you have any questions, please let me know and I’ll be glad to assist you further.

Best regards,
Jung
Independent Advisor