I have a spreadsheet used for estimating projects. There are categories
across the top (Electrical Engineering, Mechanical Engineering, etc.). Under
each category are job titles (anywhere from 1 to 9 titles for each category).
Each title has a dropdown list with names of people under that title. The
drop down list of names is converted to the persons initials using an index
formula.

My problem comes when some of the columns (categories and/or titles) are not
used and are deleted or hidden. The formulas in some of the dropdown boxes
change €“ specifically, the input range and the cell link. I am using named
references, but Excel switches the reference in the input range to the name
that was used in the column to the left that was deleted or hidden. I want
the input range to stay the same. I also want the cell link to update to the
new location.

Can anyone help?