Data Validation/Forms Combo Box

[Guest]

I have created a worksheet that works splendidly involving Data Validation
Drop Downs (DDVD). There are many formulas in this workbook. All was well
until my boss told me that he prefers "the little hand instead of having to
click on the cell to select the item he wants." Meaning a combo box
drop-down menu. So I went back, deleted the DVDD's and created DD's from the
Forms/Combo Box toolbar icon. Now my formulas respond with the error #REF!
UGH! Here is one example of how the formula now looks:

=IF(#REF!=$A$2,3.5*$C$2*IF($G$2>=1.5,1.25,1)+6.45*$C$3*IF($G$2>=1.5,1.25,1)+6*$C$4*IF($G$2>=1.5,1.25,1)+9.25*$C$5*IF($G$2>=1.5,1.25,1)+16*$C$6*IF($G$2>=1.5,1.25,1)+4.27*$C$7*IF($G$2>=1.5,1.25,1))+IF(#REF!=$A$3,5*$C$2*IF($G$2>=1.5,1.25,1)+8.05*$C$3*IF($G$2>=1.5,1.25,1)+7.25*$C$4*IF($G$2>=1.5,1.25,1)+12*$C$5*IF($G$2>=1.5,1.25,1)+22*$C$6*IF($G$2>=1.5,1.25,1)+6*$C$7*IF($G$2>=1.5,1.25,1))+IF(#REF!=$A$4,3.35*C2*IF($G$2>=1.5,1.25,1)+6.4*$C$3*IF($G$2>=1.5,1.25,1)+5.55*$C$4*IF($G$2>=1.5,1.25,1)+9.5*$C$5*IF($G$2>=1.5,1.25,1)+15.45*$C$6*IF($G$2>=1.5,1.25,1)+3.7*$C$7*IF($G$2>=1.5,1.25,1))+IF(#REF!=$A$5,4.5*$C$2*IF($G$2>=1.5,1.25,1)+8.05*$C$3*IF($G$2>=1.5,1.25,1)+8.85*$C$4*IF($G$2>=1.5,1.25,1)+13*$C$5*IF($G$2>=1.5,1.25,1)+22*$C$6*IF($G$2>=1.5,1.25,1)+4.5*$C$7*IF($G$2>=1.5,1.25,1))

Wise-cracks aside (I'm sure I could have used a VLOOKUP but at the time was
more comfortable with Logic formulas)
Any way of changing the DVDD's to Combo box DD's without having to
re-write/edit all the formulas??

TIA!

 

[Dave Peterson]

Maybe...

I think that the #ref! error wasn't caused when you got rid of the
data|validation. It was caused because you deleted the cell--(or the row that
contained that cell--or the column that contained that cell.)

If you can go back to a copy of the workbook that had data|validation, you could
remove the data|validation (but don't change that cell).

Now you're going to have to make a choice. A dropdown from the Forms toolbar
and a combobox from the control toolbox behave differently.

=========
If you use the combobox from the control toolbox toolbar, you'll find that the
next step is easier.

Add that combobox from the control toolbox toolbar and place it over the cell
that had data|validation.

Rightclick on that combobox and choose properties (make sure you're in design
mode).
Assign the .listfillrange to the same source that data|validation used.
Assign the .linkedcell to the same cell that held the data|validation (right
under the combobox).

If you want, you can give the cell a number format of:
;;;
(3 semicolons)
to hide the value in that cell.

The formula will still point to the same cell, so it should work ok.

===========
If you use a dropdown from the Forms toolbar, you'll have to do more work.

Add the dropdown to the same cell that had data|validation.

Rightclick on the dropdown and choose "Format Control"
Select the Control tab
Assign the Input range (same range as used with data|validation)
Assign a different cell link--maybe off the screen a bit (I'll use x999).

In the cell under the dropdown, put this formula:
=if(x999="","",index(InputRangeHere,x999))
(Change the inputrange to the name or address of that input range.)

You can hide the formula and linked cell with the same custom number format.

===========

The .linkedcell of the combobox from the Control toolbox toolbar returns the
value in that combobox.

The cell link of the dropdown from Forms toolbox returns an index into that
list. So you need a way to convert it to the value. (You could use some VBA to
get populate that cell with the value under the dropdown--but I think the cell
link and formula is more straight-forward.)

 

[Guest]

Hello Dave,
Thank you for your answer. I was able to create dropdowns from the control
toolbox and to keep the formulas working, but not from the Forms toolbar. Do
you know if it is possible to force the mouse pointer to change when hovering
over the dropdown menus? Or is this option only available when dropdowns are
created from the Forms toolbar?

 

[Guest]

update:
Now able to make this work from both dropdowns. I put the formula in the
cell one row beneath where it needed to be. Still have the question about
the mouse pointer. Thanks again!

 

[Dave Peterson]

I don't know how to change the mouse pointer when hovering over a dropdown or a
combobox placed on a worksheet.

 

[Guest]

Thanks for the honest answer. Have a great day! You all are amazing