I am updating a spreadsheet that has 13 worksheets, each with questions where
the user "grades" themselves by selecting numbers through a combo box. The
combo box refers to a range elsewhere on the sheet containing numbers such as
0, 1, 2.5, 5 etc. These numbers are formatted as numbers. When the user
selects a number, the result is posted to a hidden cell to the right of the
combo box. However, this number result is posted as text and the formula
=sum(L5:L19) will not update. =sum(L5+l6+l7+etc) does work, but we are adding
rows and having to manually updating each formula. We really prefer the
results be in number format and not text without having to convert to number
or number * 1. The combo box is not done through Data Validation...which I've
tried. What suggestions can someone offer? I've looked at doing a macro on
the page to do the conversion, but don't know how to assign to any change on
any combo box (all the combo boxes use the numbers, so that could be an
answer) as I don't want to write code for individual combo boxes as their
name may change as they are copied and added to the new rows.

(was unable to duplicate error)
Are you using the combo box from the forms toolbar?
Is the cell you're linking to formatted as number, not text?

I find the problem interesting, as even when you have text being used in a
combo box, it should return a number corresponding to row number of list.
Perhaps the problem is how you are "posting" the number. Are you using a
lookup or index formula?
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"David" wrote:

I am updating a spreadsheet that has 13 worksheets, each with questions where
the user "grades" themselves by selecting numbers through a combo box. The
combo box refers to a range elsewhere on the sheet containing numbers such as
0, 1, 2.5, 5 etc. These numbers are formatted as numbers. When the user
selects a number, the result is posted to a hidden cell to the right of the
combo box. However, this number result is posted as text and the formula
=sum(L5:L19) will not update. =sum(L5+l6+l7+etc) does work, but we are adding
rows and having to manually updating each formula. We really prefer the
results be in number format and not text without having to convert to number
or number * 1. The combo box is not done through Data Validation...which I've
tried. What suggestions can someone offer? I've looked at doing a macro on
the page to do the conversion, but don't know how to assign to any change on
any combo box (all the combo boxes use the numbers, so that could be an
answer) as I don't want to write code for individual combo boxes as their
name may change as they are copied and added to the new rows.

One other thought
=sum(L5:L19)

Perhaps try "add a zero" to coerce text nums to real nums
viz,, use, array-entered*: =sum(L5:L19+0)
*press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:21,000 Files:370 Subscribers:66
xdemechanik
---