Using values in drop down boxes in formulae

  • Thread starter Thread starter 44203 - ExcelForums.com
  • Start date Start date
4

44203 - ExcelForums.com

Is there any way to use the value shown in a drop down box in a sum
formula.
I am trying to work out the total of a column including whatever
number is shown in the drop down box. The formul at the moment is
=SUM(C16:C28) with the drop down box positioned on cell C28. Is there
any way of linking the drop down box to the cell so the number shown
is added as well?
Thx
 
Hi
what drop down boy are you suing ('Data - validation')?

--
Regards
Frank Kabel
Frankfurt, Germany

"44203 - ExcelForums.com"
 
If you used data|validation, then your =sum(c16:c28) should work.

===

If you used a combobox from the control toolbox toolbar, you can make C28 the
linked cell and then your formula will still work.

Show the control toolbox toolbar.
Click on the design mode icon
rightclick on the combobox and choose properties
look for linkedcell and type in C28
Toggle the design mode to off
and test it out.

(I like to give that cell a custom format of ;;; so that it doesn't bleed
through. But the value will still show up in the formulabar.)

====

If you used a dropdown from the Forms toolbar, you can use another cell as the
linked cell. And then use that linkedcell in a formula in c28.

Rightclick on the dropdown
select format control
on the control tab
assign your input range and your linked cell.
Say your input range was A1:A25
and your linked cell was D28 (hide the column if you want)

Then in C28:
=IF(D28="",0,INDEX(A1:A25,D28))
 
Back
Top