Custom list in Excel

[ranebow]

I'm trying to create a custom list with military time (0700 0730 0800 0830 etc). When I create the list it looks ok but will not populate. If I put the letter "A" before 0700 it works fine. Is there a way to create a numeric list?

 

[SkipVought]

Hi,

Check out this thread thread68-1034143.

The key info bit is that Time Values are NUMBERS. What you and I want to see is just a FORMAT of a NUMBER.

You can FORMAT the cells (hhmm) but you must enter 7:00 to DISPLAY 0700 as a time. OTHERWISE your entry will just be NUMERIC CHARACTER STRINGS and NOT Time Values.



Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[ranebow]

I don't have problems with the format. I'm having problems with it populating.

 

[SkipVought]

you cannot enter 0700 and get a time!

Excel interprets 0700 as 700.

when you enter 7:00 Excel figgers out that you are entering a Time. You MUST enter the [Colon].

the only other option is to write a VBA procedure to translate 700 to a Time Value.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[ranebow]

Ok, I've formated the column as TIME. If I enter 2 different times and then copy down it populates.

But I still have a question on how to create a custom list (TOOLS/OPTIONS/CUSTOM LIST) with numeric values. I can't get that to work.

 

[pbrodsky]

Read the Excel help file!

This is copied from the help:

Note A custom list can contain text or text mixed with numbers. To create a custom list that contains only numbers, such as 0 through 100, first select enough empty cells to contain the list. On the Format menu, click Cells, and then click the Number tab. Apply the Text format to empty cells, and then type the list of numbers in the formatted cells. Select the list and then import the list.

 

[SkipVought]

Let me ask how you intend to use this custom list?

The default custom lists are strings that are SORTED in an order other than alpha.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[ranebow]

I had used help, unfortunately I formatted the cells AFTER entering data and it didn't work. So I created the custom list but now can't use it. Every time I copy down for the fill it just "counts".

In response to SkipVought....I was thinking of creating another list of Building numbers.....1, 3, 4, 5, 9, 12, 14

Wanted to use a custom list to be able to put them in headings of different reports. The list goes on a little farther that 14. So I was just trying to be a little lazy. But I can get around this.

Thanks for everyone's help.

 

[SkipVought]

FYI:

We use NUMBER in many cases to designate an IDENTIFIER and NOT a value on which we will do arithmetic, for instance...

Invoice Number
Employee Number
Building Number
Part Number

These "Numbers" are really NUMERIC STRINGS which are vastly different than NUMBERS on which arithmetic will be performed.

So, it is advisable, when defining a NUMERIC IDENTIFIER, to make it TEXT.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}