Odd problem with multiple lists...

[vanclute]

I know the Excel help says to try to have only one list per sheet, but how silly a limitation is that?? And does anyone know of it to be a genuine problem with Excel?

I have 4 simple lists in my worksheet. 2 of them get their values from one manually typed set of data, the other 2 from another manually typed set of data. THey work fine by selecting the popup triangle and choosing from the list. However two of the lists (same set of data) will also let me simply type in a value, and if it matches data in the list it selects that data. The other two lists will not let me type in manually, they say a user has restricted the type of data that can be entered, though I can choose the identical data from the list myself.

????

One confused guy...

Jonathan
This message was edited by vanclute on 2002-10-24 22:26

 

[BigC]

Jonathan
It sounds like someone other than yourself built that part of the worksheet and in doing so included some controls to prohibit the input invalid data - hence the restriction.
As such, I think you should consult the spreadsheet creator before trying to break open the controls. Sorry.
Besides, what exactly is the problem you're encountering?

HTH

 

[vanclute]

Actually that's the weird part, I *AM* the spreadsheet creator! And to make things even weirder, I can enter two specific values by keyboard, but any others say it's not allowed.

Specifically, I have a list of price increments in .05 increments, from .50 to 3.00.

The list works fine, I can select any value I want. But if I type any values, they are rejected, EXCEPT for .5 and .55 which enter just fine!

Totally weird... I'm stumped. Anyone else have any thoughts?

Jonathan

 

[vanclute]

OK, I may have figured it out, but if so this sounds like a bug to me...

My list of price increments was created by adding .05 to the previous cel, as in:

A1=.05
A2=A1+.05

This, when put in a list, gives the crazy results I'm seeing. But if my list is manually entered into each cel as individual real prices, it works fine.

Very weird... anyone know what might really be going on?

Jonathan

 

[BigC]

Jonathan
It might be something to do with how Excel stores values to 15 decimal places.
I played around with senarios similar to what you described and got the same errors - until I changed the list formulae to
=round(A1+0.05,2)
After this, the manual input is exactly as per the list and I had no rejections. Why it didn't work for the otehrs I don't know.

HTH

 

[vanclute]

Wow, that is strange... but thanks for the fix! I had already manually entered my list in .05 increments, but that was getting very very tedious!

THanks again...

Jonathan