Can't sort cells in date order that contain dates created from arr

[StaceyJ]

Okay, so I finally figured out how to get my array to work. It's inputting
dates that it finds in cells on another tab (looking up based on criteria and
returning all results in seperate cells).

Here is the problem. If I copy the array down only 10 rows, and I have the
filter on, it will still give me the ability to sort the output as dates
(which it should since they ARE dates). However, if I copy it down even 1
more cell, it changes the default sort to "A to Z" and will no longer let me
sort them as dates. (Instead of giving me the sort options of "Oldest to
Newest" and "Newest to Oldest" it gives me "A to Z" and "Z to A". I'm am
using Excel 2007.)

I have to be able to sort them as dates. What should I do?

 

[Roger Govier]

Hi Stacey

I'm not fully understanding what you are saying.
Have you Inserted a Table?
If so, there is no question about having to copy formulae down, they get
added automatically as you add more rows to the table.
Sure, if they are looking up values from another sheet, and the values are
returned as dates, then the automatic filter button at the head of the table
column will still give the ability to sort by Date.

Can you describe in a little more detail what you problem is?

 

[StaceyJ]

I'm using excel, so isn't the entire worksheet a table?

Anyway, yes, there is an array starting in about row 3 of one column. If I
have just that one cell filled with the array (which returns a date--and
ACTUAL date--not a text version but the actual serial formatted as a date),
the automatic filter buttons will show I can sort oldest to newest or vice
versa. I can copy that array down 10 cells and the autofilter will continue
to give me that option. However, as soon as I hit row 11 or above, it
changes the filter options. Despite the fact they're still date serials, it
looks as it as (I'm assuming) alphanumeric, because the options change to
only Sort A to Z or vice versa.

I dont' have this problem with regular formulas. I'm only experiencing this
as a result of pulling in the information via an array.

 

[Roger Govier]

Hi Stacey

Yes to an extent a whole Excel sheet is a kind of Table.
However, there is a specific Excel Object called a Table which then exhibits
special properties.
A table is created by selecting a cell within your range of data>Insert
tab>Table>My table has headers.

I am still not understanding.
When you say an array, do you mean a formula?
If you have created a Table in Excel 2007, any formula within the table will
automatically be inserted onto any new line added to the table. There is no
need to copy the formula down.

Perhaps you would like to send me a copy of the book which exhibits this
problem and I might better understand the problem you are experiencing.
To mail direct
roger at technology4u dot co dot uk
Change the at and dots to make a valid email address