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.​

 

[Ludwig]

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

Saw you in there pretty quickly after I posted my reply

Done that, but still not getting the little black downward arrow which will select the table column. Are you seeing it?

 

[Peter_SSs]

Are you seeing it?

Yes. Are you sure you didn't choose 'Select unlocked cells'?
I just double-checked by downloading another copy of your sample workbook.
Checked no black arrow
Performed steps 1 & 2.
See what happens:

 

[Ludwig]

Yes. Are you sure you didn't choose 'Select unlocked cells'?
I just double-checked by downloading another copy of your sample workbook.
Checked no black arrow
Performed steps 1 & 2.
See what happens:

Ah! This time it's worked (I didn't save it back over the top though in case someone opens it now). Maybe I didn't tick the right (= 'locked" cells) box last time.

That's a fix for the problem. The locking settings must change some settings somewhere to free things up once more. Thanks for sticking with it and finding the resolution for the problem. Much appreciated.

I'll mark your one as the answer to this post.

 

[Peter_SSs]

You are welcome. Glad you got it working. Thanks for letting us know.