input range in drop down list

[Guest]

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 person’s 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?

 

[Dave Peterson]

You might get a few more replies if you go into more details.

What the formula looks like before the deleting, what it looks like after the
deleting. Where it's located (sheet name and cell address). Range names and
what they refer to (if they're used in the formula).

And what kind of dropdown did you use? Data|Validation, a combobox from the
control toolbox toolbar, or a dropdown from the forms toolbar.

 

[Guest]

There are two sheets in the workbook: Estimate and Resources. The goal is
to be able to select a person’s name and have Excel convert it to initials.

In Estimate, cell D1 is the dropdown combo box from the Forms toolbar. The
Input Range is Project_Manager_Names (named reference from Resources A2:A30
which lists the person’s full name). The Cell Link is D2.

The index formula is in cell D4 and is as follows:
=INDEX(Project_Manager,D2,1) where Project_Manager is from Resources B2:B30
and lists the person’s initials.

The next column has a dropdown combo box in E1 that references
Sr_Engineer_Names (from Resources D215) and the index formula in E4
references Sr_Engineer (from Resources E2:E15).

If I delete the column with Project Managers (column D), the dropdown combo
box that used to reference Sr_Engineer_Names (old column E, now column D) has
now changed to Project_Manager_Names in the Input Range and the Cell Link is
lost (#REF!).

The outcome that I want is to have the Input Range stay the same
(Sr_Engineer_Names) and the Cell Link to update to D2.

When I delete certain columns, everything works fine. With other columns, I
experience the error as described above. Is there any fix?

 

[Dave Peterson]

If you delete the linked cell and keep the formula, you're gonna have trouble.

If you're using code to delete the columns, you could convert the formula to a
value first.

with worksheets("Estimate").range("d4")
.value = .value
end with

before you delete that other range.

===

Another option is to drop the linked cell and have a macro populate that cell
when you change the dropdown.

I assigned this macro to the dropdown:

Option Explicit
Sub testme()

Dim myDD As DropDown
Set myDD = ActiveSheet.DropDowns(Application.Caller)

With myDD
If .ListIndex > 0 Then
ActiveSheet.Range("d4").Value = .List(.ListIndex)
End If
End With

End Sub