Odd problem with multiple lists...

vanclute

Board Regular
Joined
Oct 23, 2002
Messages
218
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

BigC

Well-known Member
Joined
Aug 4, 2002
Messages
851
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

Board Regular
Joined
Oct 23, 2002
Messages
218
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

Board Regular
Joined
Oct 23, 2002
Messages
218
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

Well-known Member
Joined
Aug 4, 2002
Messages
851
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

Board Regular
Joined
Oct 23, 2002
Messages
218
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
 

Forum statistics

Threads
1,144,451
Messages
5,724,425
Members
422,553
Latest member
excelgraham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top