bkunes said:25 rows
Ragdyer said:The procedure here, since this is all accomplished using functions which
cannot change other cells, is to have a working datalist, where you will
input data, and a "presentation" datalist which will be populated by
formulas drawing values from the working datalist in a particular,
pre-defined order.
You could, if you wish, use "Custom Views", where a "views" window is added
to the Menu Bar for a real time indication of the present view in force,
plus the ability to toggle between views with a single click.
You said 8 columns, so let's say that the working datalist is in Columns S
to Z, and the presentation datalist is in Columns A to H.
Enter identical headers in Row1 for both datalists, with the sort key (the
dates), being in Columns A and S.
I've sized the formulas to Row 50 so you have room to expand.
Fill in your working datalist in S2 to Z(n) with whatever data you presently
have.
To start:
In A2, enter this formula:
=IF(ISERR(LARGE($S$2:$S$50,ROWS($1:1))),"",LARGE($S$2:$S$50,ROWS($1:1)))
And copy down to A50.
This will *automatically* sort the dates in your working datalist -
descending, newest dates first.
If you would like to sort with the oldest dates on top, simply change the
Large() function to the Small() function in both places in the formula.
Now, to match the other fields to the sorted dates, enter this *array*
formula in B2:
=IF($A2="","",INDEX(T$2:T$50,SMALL(IF($S$2:$S$50=$A2,ROW($1:$49)),COUNTIF($A
2:$A$50,$A2))))
--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.
*After* the CSE entry, copy the formula across to H2.
Then, select B2 to H2, and copy that selection down to Row50.
You should now have your "presentation" datalist displaying the data from
your working datalist in sorted order, according to the dates.
Assuming that your adding new data to the bottom of the working datalist,
the newer (lower) duplicate date entries will display higher in the
presentation datalist.
This can be changed if you wish by simply changing the Small() function in
the array formula to the Large(0 function.
Post back if you're interested in creating "Custom Views".
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.