Referencing tables in formula

[Ludwig]

Background:

I moved a table column to another position within the table by creating a new column, copy/pasting the data across (no formula involved, it's just numbers), then doing a global edit on the column name references throughout the workbook - using "colmName]" as search/first argument. Once that was done, I deleted the 'old' column and renamed the new back to the old - which meant of course the formulas all changed correctly too. Job done, no formula problems encountered after this change.​

Problem:

BUT .. I can no longer construct a formula based on that table using the mouse. Normal process available is -​

1. To enter a formula in a cell, start by pressing "=" & enter any other parts of the formula you're constructing.
2. When you come to need to reference a table column you move the mouse over the table column header cell to get a solid black down-pointing arrow - then left click the mouse.
3. Excel then enters the table & column name as the next portion of the formula. Fantastic feature!

Sadly this no longer happens for *any* columns in this table since I added the new column and deleted the old (using process as detailed above).​

• I can click on the column header cell, then tweak the formula shown by deleting the "[#Header]" portion to get the required table column reference I need but I shouldn't have to do that.

I still get the solid black down-pointing arrow when working with other tables in the same XLSX workbook, just not the table I modified the column order on.​

​

I have opened the workbook using "Open and Repair" but no errors were reported.​

Issue:

How do I get Excel to behave correctly on this table when constructing formula.​

Rebuilding the table from scratch would mean a *lot* of time updating a lot of formula throughout the workbook, something I don't want to have to do.​

 

[Alex Blakenburg]

It sounds rather like it is no longer a table. Do you still get Table Design Tab on the menu when you click inside the table ?
Ideally when you move a column you would select the column and dragg it with your mouse to the new position

 

[Ludwig]

It sounds rather like it is no longer a table. Do you still get Table Design Tab on the menu when you click inside the table ?
Ideally when you move a column you would select the column and dragg it with your mouse to the new position

No, it's still a table - all the formulas referencing it as a table are working, and "Table Design" tab is on the menu. And as I said, I can click on any of the column header cells and get a table reference to that to remove the "header" portion from to get to the actual table column reference I want.

Of course, I could manually type out the column reference I need character by character into a formula but that's prone to typos
Fortunately Excel still prompts me with suitable matches to table names or column names within the table previously typed as I do type the reference out 'long-hand'.

 

[Peter_SSs]

Perhaps I didn't exactly follow steps in post 1 though I think I did. After that, I still get the solid black arrow when constructing formulas.

Again I may not be fully understanding but I'm wondering why go to all that trouble to move a column? Why not simply select the column and drag it to its new position? No need to edit any formulas or headings.

 

[Alex Blakenburg]

Are you able to roll back to a previous version and use the drag method of moving the column that Peter and I have both suggested ?

If not then if you click in a cell in the table of which you can't see the downward pointing arrow and hit Ctrl+Spacebar what happens ?
Is just the table data range of table highlighted or the whole column ?
If you hit F5 (goto) and select the table name from the Goto list does it highlight the data body of the table and does it match what you are expecting the databody to be ?

 

[Ludwig]

I went back to an earlier version, just prior to my moving of the column having first saved a copy locally elsewhere. The dragging made the move much much easier to do. However the problem still exists.

• Clicking in a cell in the table and using Ctrl+Spacebar selects the entire column of data, not including the column header cell.
• Hitting F5 and selecting table name highlights the entire data body of the table.

I went back to a mid-Feb version (this xlsx file is in Onedrive) and checked .. the problem exists in that copy too, so it predates my moving of the columns. Something else I did at an earlier time 'caused' it, trouble is I don't know when or what; it's March now
It's only now I wanted to add a formula using it that I found the problem.

So it seems apart from a re-construct there's nothing I know to do that will fix it.

 

[Alex Blakenburg]

To be honest I seldom use the black downward pointing arrow, I find it is too fiddly. Its easier to click a cell in the column and hit Ctrl+Spacebar to select the column's datarange.
If you copy the table into a New Workbook does the problem transfer to the table in the new workbook ?
If if does can you share the workbook via dropbox, google drive, onedrive or any other sharing platform ? Just make sure it available to anyone with the link and post the link here. If it doesn't then in a copy of the workbook can you delete everything else from the workbook leaving just the problem table and share that ?

 

[Ludwig]

After 3 hours, I was able to reconstruct the entire worksheet containing the 'faulty' table. Numerous copy & paste values, paste formats, then define new table & copy/paste [table reference-based] formulas, etc etc. So now I've been able to delete the old sheet.

I find using the black downward pointing arrow easy to use most of the time. Now it's back so I'm happy.

Thanks for your feedbacks though, I appreciated it. I'll try to extract the faulty table from the sidelined copy to post here tomorrow.

 

[Ludwig]

Here's a link to the cutdown version of the workbook with just the 'problem' table. I would be interested if you find out anything about what went wrong, and how to fix its so I know in case I encounter the problem again. I'll leave the link active for a short time only.

NB:

I've changed some formula so they don't refer to lookups in another table on another sheet (which of course I've deleted from this copy).​

I also randomised the data in the table ​

 

[Peter_SSs]

Try these steps

1. Review tab -> Protect Sheet -> Leave password box empty -> tick the box for 'Select locked cells' -> OK
2. Review tab - Unprotect Sheet
3. Check for the little black arrow