Custom lists (XP)

[Hans Knudsen]

I tried to make a custom list: A, B, C ... Z
No problem.
Then I tried to make the following list: a, b, c, ... z
but now nothing happens when I press the Import button.

I do not quite understand.

Hans Knudsen

 

[Dave Peterson]

I just typed A,B,C,D,E into the Tools|Options|Custom Lists|list entries box and
added it my custom lists.

Then I tried a,b,c,d,e and hit add.

It sure looks like excel doesn't care about case when working with custom lists.

Which is consistent with how it fills cells based on a list. It just matches
the case of the previous entries.

 

[Hans Knudsen]

You are right.
Not that big deal this custom list thing but kind of a workaround
might be to create lists something like this:
UPPER: A,B,C,D,E
lower: a,b,c,d,e
(and then in a cell just start with for example A or a)

Hans

 

[Leo Heuser]

Which is consistent with how it fills cells based on a list. It just
matches
the case of the previous entries.

Hi Dave

Not quite. See my answer to Hans.

Regards
Leo

 

[Leo Heuser]

I tried to make a custom list: A, B, C ... Z
No problem.
Then I tried to make the following list: a, b, c, ... z
but now nothing happens when I press the Import button.

I do not quite understand.

Hans Knudsen

Hej Hans

If the list is cyclic, you can enter it, starting with
a different element, i.e. make the list A, B, C ... Z.

Now you can enter the list B, C ... Z, A

Apparently Excel starts looking at the bottom list and
works its way up. The first list containing the element
entered in a cell to be dragged is being used.

E.g.

System list:

Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday

if you make this custom list:

monday
tuesday
wednesday
Thursday
Friday
Saturday
Sunday

then that's the one being used, no matter which day
you enter and drag.

Try enter friday and drag.
Then try enter Friday and drag.

!!

I run a Danish version of Excel (i.e. I have tested
the above with the Danish weekday names), but I
take it, that the principles are the same for all versions.

Med venlig hilsen
Leo

 

[Hans Knudsen]

Hej Hans

If the list is cyclic, you can enter it, starting with
a different element, i.e. make the list A, B, C ... Z.

Now you can enter the list B, C ... Z, A

Apparently Excel starts looking at the bottom list and
works its way up. The first list containing the element
entered in a cell to be dragged is being used.

E.g.

System list:

Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday

if you make this custom list:

monday
tuesday
wednesday
Thursday
Friday
Saturday
Sunday

then that's the one being used, no matter which day
you enter and drag.

Try enter friday and drag.
Then try enter Friday and drag.

!!

I run a Danish version of Excel (i.e. I have tested
the above with the Danish weekday names), but I
take it, that the principles are the same for all versions.

Med venlig hilsen
Leo

Hej Leo
Strange, but it works!
Thank you. As always - my respect for your expertise!

Hans

 

[Leo Heuser]

Hej Leo
Strange, but it works!
Thank you. As always - my respect for your expertise!

Hans

Velbekomme, Hans

Leo

 

[Dave Peterson]

But these are different lists. You can see it when you sort test data using
each list. (Yes, you could sort any data by each list (ignoring the B, C, ...,
Z, A version).

But if excel ignores the case in the list and uses the case in the data (for
filling the range), I guess I'd question the use of having two lists.

 

[Leo Heuser]

I wasn't focused on the use of lists for sorting,
but for filling ranges.
My point was, that it *is* possible to create a
list having the same elements as a current list,
by starting with a different element, and if
needed combining it with a difference in case.

A practical example:

Let's assume that a (Danish) user wants to put
emphasis on saturday and sunday by letting the
names consist of upper case letters only (SATURDAY
and SUNDAY) (in Denmark weekday names start
with a lower case letter).

She has to enter weekday names in e.g. A2 and down.

Try the following examples without quotes:

1. Enter "SUNDAY" in A2 and drag down
2. Enter "sunday" in B2 and drag down
3. Enter "Sunday" in C2 and drag down
4. Enter "monday" or ... in D2 and drag down

Everything turns out as expected.


Now create the list

SUNDAY
monday
tuesday
wednesday
thursday
friday
SATURDAY

If the system list starts with Sunday, make it this list instead:

monday
tuesday
wednesday
thursday
friday
SATURDAY
SUNDAY


Make the same dragging movements <g> in F2,
G2, H2 and i2

I guess, this wasn't expected <g>

 

[Dave Peterson]

I agree with everything you wrote--except one thing:

A practical example:

<vvbg>