Lock table range due to formula

leatherhen99

New Member
Joined
Dec 17, 2019
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Good Day All!

I can't seem to find a canned answer for this...and there might not be one... I have a table with business exceptions with about 10 columns... on another sheet, I have a complex index/match formula with multiple combinations based on start/end dates and the agent's name based on the exceptions. The range within the formula can change often, so I'm using the table fields within the formula (you see where I'm going, don't you?). Yesterday, the team who uses the spreadsheet said that the exceptions were not populating, so I had to go investigate. Someone had changed the range of the table... they had made it 8 columns instead of 10 :(... removing the very two columns that my formulas were supposed to be returning.
So, I know how to lock/protect a cell, range, sheet, and workbook... but now, I need to be able to protect the structure of my table (10 columns wide), and allow the user to continue to make edits as necessary. I've read about protecting the workbook, but I only see that it protects them from adding/deleting/moving a page... that won't protect the integrity of my table, will it?

Any suggestions you have would be greatly appreciated!
Have a great Tuesday!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Forum statistics

Threads
1,143,620
Messages
5,719,789
Members
422,244
Latest member
AYSHANA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top