How to have combo list sorted

I have set the following data name:
=OFFSET(Par!$D$2,0,0,COUNTA(Par!$D$2:$D$201)-COUNTBLANK(Par!$D$2:$D$201),1)

How to get all data in the right onummeric order?

It seems to be with offset($D$2,0,0,counta($D:$201),1).... likely looks
like the upperformula I use.

But the combo is not sorted when I use it.

Bart

0
12/15/2006 1:56:31 AM
excel 39879 articles. 2 followers. Follow

5 Replies
288 Views

Similar Articles

[PageSpeed] 7

You would have to sort the source data:

Whatever's in this range Par!$D$2:$D$201 needs to be sorted.

Biff

"AA Arens" <bartvandongen@gmail.com> wrote in message 
news:1166147791.194106.272500@f1g2000cwa.googlegroups.com...
>I have set the following data name:
> =OFFSET(Par!$D$2,0,0,COUNTA(Par!$D$2:$D$201)-COUNTBLANK(Par!$D$2:$D$201),1)
>
> How to get all data in the right onummeric order?
>
> It seems to be with offset($D$2,0,0,counta($D:$201),1).... likely looks
> like the upperformula I use.
>
> But the combo is not sorted when I use it.
>
> Bart
> 


0
biffinpitt (3172)
12/15/2006 4:56:10 AM
The row left of it is created by:

=IF(Solo!C22="","",Solo!C22)
=IF(Solo!C22="","",Solo!C22)
etc.

How to have this column permanantly order nummeric?

Bart


On Dec 15, 11:56 am, "T. Valko" <biffinp...@comcast.net> wrote:
> You would have to sort the source data:
>
> Whatever's in this range Par!$D$2:$D$201 needs to be sorted.
>
> Biff
>
> "AAArens" <bartvandon...@gmail.com> wrote in messagenews:1166147791.194106.272500@f1g2000cwa.googlegroups.com...
>
> >I have set the following data name:
> > =OFFSET(Par!$D$2,0,0,COUNTA(Par!$D$2:$D$201)-COUNTBLANK(Par!$D$2:$D$201),1)
>
> > How to get all data in the right onummeric order?
>
> > It seems to be with offset($D$2,0,0,counta($D:$201),1).... likely looks
> > like the upperformula I use.
> 
> > But the combo is not sorted when I use it.
> 
> > Bart

0
12/15/2006 6:08:35 AM
If I understand.......

To sort ascending:

=IF(ROWS($1:1)<=COUNT(Solo!C$2:C$201),SMALL(Solo!C$2:C$201,ROWS($1:1)),"")

To sort descending, replace SMALL with LARGE.

Biff

"AA Arens" <bartvandongen@gmail.com> wrote in message 
news:1166162915.058367.322240@80g2000cwy.googlegroups.com...
> The row left of it is created by:
>
> =IF(Solo!C22="","",Solo!C22)
> =IF(Solo!C22="","",Solo!C22)
> etc.
>
> How to have this column permanantly order nummeric?
>
> Bart
>
>
> On Dec 15, 11:56 am, "T. Valko" <biffinp...@comcast.net> wrote:
>> You would have to sort the source data:
>>
>> Whatever's in this range Par!$D$2:$D$201 needs to be sorted.
>>
>> Biff
>>
>> "AAArens" <bartvandon...@gmail.com> wrote in 
>> messagenews:1166147791.194106.272500@f1g2000cwa.googlegroups.com...
>>
>> >I have set the following data name:
>> > =OFFSET(Par!$D$2,0,0,COUNTA(Par!$D$2:$D$201)-COUNTBLANK(Par!$D$2:$D$201),1)
>>
>> > How to get all data in the right onummeric order?
>>
>> > It seems to be with offset($D$2,0,0,counta($D:$201),1).... likely looks
>> > like the upperformula I use.
>>
>> > But the combo is not sorted when I use it.
>>
>> > Bart
> 


0
biffinpitt (3172)
12/15/2006 6:33:50 AM
Thanks, that does the job.

Bart
Jakarta.

On Dec 15, 1:33 pm, "T. Valko" <biffinp...@comcast.net> wrote:
> If I understand.......
>
> To sort ascending:
>
> =IF(ROWS($1:1)<=COUNT(Solo!C$2:C$201),SMALL(Solo!C$2:C$201,ROWS($1:1)),"")
>
> To sort descending, replace SMALL with LARGE.
>
> Biff
>
> "AAArens" <bartvandon...@gmail.com> wrote in messagenews:1166162915.058367.322240@80g2000cwy.googlegroups.com...
>
> > The row left of it is created by:
>
> > =IF(Solo!C22="","",Solo!C22)
> > =IF(Solo!C22="","",Solo!C22)
> > etc.
>
> > How to have this column permanantly order nummeric?
>
> > Bart
>
> > On Dec 15, 11:56 am, "T. Valko" <biffinp...@comcast.net> wrote:
> >> You would have to sort the source data:
>
> >> Whatever's in this range Par!$D$2:$D$201 needs to be sorted.
>
> >> Biff
>
> >> "AAArens" <bartvandon...@gmail.com> wrote in
> >> messagenews:1166147791.194106.272500@f1g2000cwa.googlegroups.com...
>
> >> >I have set the following data name:
> >> > =OFFSET(Par!$D$2,0,0,COUNTA(Par!$D$2:$D$201)-COUNTBLANK(Par!$D$2:$D$201),1)
>
> >> > How to get all data in the right onummeric order?
>
> >> > It seems to be with offset($D$2,0,0,counta($D:$201),1).... likely looks
> >> > like the upperformula I use.
> 
> >> > But the combo is not sorted when I use it.
> 
> >> > Bart

0
12/15/2006 9:47:37 AM
You're welcome. Thanks for the feedback!

Biff

"AA Arens" <bartvandongen@gmail.com> wrote in message 
news:1166176057.042904.19320@80g2000cwy.googlegroups.com...
> Thanks, that does the job.
>
> Bart
> Jakarta.
>
> On Dec 15, 1:33 pm, "T. Valko" <biffinp...@comcast.net> wrote:
>> If I understand.......
>>
>> To sort ascending:
>>
>> =IF(ROWS($1:1)<=COUNT(Solo!C$2:C$201),SMALL(Solo!C$2:C$201,ROWS($1:1)),"")
>>
>> To sort descending, replace SMALL with LARGE.
>>
>> Biff
>>
>> "AAArens" <bartvandon...@gmail.com> wrote in 
>> messagenews:1166162915.058367.322240@80g2000cwy.googlegroups.com...
>>
>> > The row left of it is created by:
>>
>> > =IF(Solo!C22="","",Solo!C22)
>> > =IF(Solo!C22="","",Solo!C22)
>> > etc.
>>
>> > How to have this column permanantly order nummeric?
>>
>> > Bart
>>
>> > On Dec 15, 11:56 am, "T. Valko" <biffinp...@comcast.net> wrote:
>> >> You would have to sort the source data:
>>
>> >> Whatever's in this range Par!$D$2:$D$201 needs to be sorted.
>>
>> >> Biff
>>
>> >> "AAArens" <bartvandon...@gmail.com> wrote in
>> >> messagenews:1166147791.194106.272500@f1g2000cwa.googlegroups.com...
>>
>> >> >I have set the following data name:
>> >> > =OFFSET(Par!$D$2,0,0,COUNTA(Par!$D$2:$D$201)-COUNTBLANK(Par!$D$2:$D$201),1)
>>
>> >> > How to get all data in the right onummeric order?
>>
>> >> > It seems to be with offset($D$2,0,0,counta($D:$201),1).... likely 
>> >> > looks
>> >> > like the upperformula I use.
>>
>> >> > But the combo is not sorted when I use it.
>>
>> >> > Bart
> 


0
biffinpitt (3172)
12/15/2006 7:19:31 PM
Reply:

Similar Artilces:

Smart List-GPS error 18
Hi, Someone have seen this error before, in smartlist, in open purchase order by vendor ? -- Eric ...

Sorting Combo box items
If someone could tell me how to sort the contents of a combo box in 2007 it would be much appreciated. Base the combo box on a query that has an appropriate ORDER BY clause in it. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele Co-author: Access 2010 Solutions, published by Wiley (no e-mails, please!) "Teeder" <Teeder@discussions.microsoft.com> wrote in message news:9D5993EB-E9D6-44FC-A0F0-50B8B5DAB9AD@microsoft.com... > If someone could tell me how to sort the contents of a combo box in 2007 > it > would be much appreciated....

Is it possible to Cond. Format a List box
Is it possible to format a list box , so as if one row has a zero balance is this field (Part of Script) [tblInvoice_ItMdt.TotalAmount FROM tblInvoice_ItMdt] I could change the colour of the font?...............Thanks for any help............Bob Bob, Access doesn't allow this natively, and I doubt that anyone has come up with a way to do it with a listbox. As far as I know, the closest you would get is to make a continuous view subform, and use conditional formatting on it. You could lay it out so it looks pretty much like a listbox... but of course if your listbox is multi...

Dynamically loading pieces of a bitmap file to an image list
Hello, Similar questions have been asked, but I am not sure if they are in reference the same type of situation. Perhaps there is a better way, but I basically would like to make my app skinable by reading its graphics from a single bitmap file, and give the user the ability to select a different file during program execution. Right now my graphics comes from bitmap resources and are put into an image list. I'm not sure how to read graphics from files, or how to read parts of bitmaps into image lists. So my question is if anyone knows how to dynamically load a bitmap from a file and p...

PING distribution list
Hi there, I would like to test the distribution group in Exchange 2003 or 5.5. I would NOT like to send a email to all members of the distribution list, so is there a way just to, sort of, PING the group and then to get pingbacks, successful or unsuccessful, from members for the group. Best regards, IVAN Aloha ivan.bojat@gmail.com, What exactly are you trying to accomplish? Are you somehow unsure that the AD members in your DistGroup are valid? -Ben- Ben M. Schorr - MVP Roland Schorr & Tower http://www.rolandschorr.com Microsoft OneNote FAQ: http://www.factplace.com/onenotefaq.ht...

How do I get Excell to sort dates that range from 1800 to 1900's
I have posted a cemetery survey on Excell spreadsheet. When I try to sort the birth dates or death dates in ascending or descending order the range of 1900 dates will sort but the 1800 dates fall after the 1900 and will not sort. Why? Is this inherit with this program? The WinXL default dates start with 31 December 1899, so earlier "dates" are interpreted as Text. You can use helper columns and text functions (LEFT(),RIGHT(),MID()) to put the Year, Month and Day in separate columns, then sort. Take a look here for additional workarounds: http://j-walk.com/ss/excel/files...

Validation List Query
Hi all, I've got a validation list showing a project problem status: New, Resolved, In Progress. These options are a named range 'Status'. Thus I do Data>Validation>Allow:List>Source:=Status No problem there. However, from time to time I need to input a custom status, which could be anything. The problem being that once I have a cell validated it does not allow me to type anything in it. Is there a means that keeps the validation drop down, but also lets me input my own data if, and when, I need to? I could always remove the validation from the individual cells, but h...

Removing Folders From Folder List
Can you help !! I am using Outlook 2002 (10.4219.4219) SP-2 I have reorganized my folders and now have a problem with my folder list. I have removed an old archive folder, and can not now remove this from the folder list. I had problems prior to this with this folder, as outlook would not let me close is. Any help on this would be appreciated ...

Contact oddity and why are there 2 Contact Lists?
Running Win 7, and was receiving emails in WLM with my maiden name listed in the TO:. I found out that I had my maiden name listed in Contacts (I run a high school mailing list here) so I decided to check it out and changed the last name to all caps. Sure enuf the next email I rec'd showed that name. I then deleted the email address connected to my name in the contact list and everything is back to "normal" (whatever that is <G>) Why in the world are there 2 contact lists on my computer......one thru WLM and one thru Windows Contacts? I'm confused. ...

Formula to return non-zero values in a list
I have a list of numbers in a column like so - 2 3 4 1 8 0 0 0 0 What formula would I create to always give me the a SUM of the las couple values before the zero value. The list always ends with a valu other than zero, so in this case, the '8' is the end of the list, and would like to SUM the 3 "last" values, those being 8, 1 and 4. As use this file, the zero values will automatically be updated with ne information, so I need a formula which would constantly reflect thi change -- Message posted from http://www.ExcelForum.com If you don't mind having an extra col...

Form Dropdown Combo Box
My dropdown combo box currently displays one column. What I would lik to do is have the box dispay column A and B but only extract the I from B. How would I be able to do this -- Message posted from http://www.ExcelForum.com I'm not sure if I understand the problem correctly, but have you trie using a CONCATENATE function to join the values in Columns A & B? Say you have first names in Column A and surnames in Column B. Us something like =CONCATENATE(A1," ",B1) in Column C for the dropdow list. You could also use =A1&" "&B1. The apostrophe marks are just...

Can validation lists be enlarged
When a validtion list is created can this be enlarged in a spreadsheet which has been reduced to view. Hi for a workaround see: http://www.contextures.com/xlDataVal08.html#Larger -- Regards Frank Kabel Frankfurt, Germany "AC" <AC@discussions.microsoft.com> schrieb im Newsbeitrag news:B6A557E7-7B2C-459B-94CE-02606B6CAB3A@microsoft.com... > When a validtion list is created can this be enlarged in a spreadsheet which > has been reduced to view. ...

conditional formatting sort
Is there a way to sort after conditional formatting. So, if I've set up a conditional format to color my text red if the number is between 0 and 95, then I want to sort so all red numbers are at the top of the spreadsheet? Any ideas? Thanks for your time and assistance. Roberta On Thu, 25 Oct 2007 05:08:00 -0700, rrupp <rrupp@discussions.microsoft.com> wrote: You can't sort by color, but you can add a column to your query like this: SortColumn: Iif(SomeValue >= 0 and SomeValue <= 95, 1, 2) Then sort by this column. -Tom. >Is there a way to sort after condition...

The opposite of the "sort" function
I have a list of alphabetically ordered data in a column. Is there a function for randomizing the order of these data? see http://www.mcgimpsey.com/excel/udfs/samplenoreplace.html In article <1174843249.583556.129830@b75g2000hsg.googlegroups.com>, lucavilla@cashette.com wrote: > I have a list of alphabetically ordered data in a column. > Is there a function for randomizing the order of these data? Exellent solution! thanks! Let's say your data in A1:A10 In B1: =RAND() copy from B1 to B10 Select A1:B10 > Data > Sort > Sort by select Column B > select As...

Delete button missing on HQ Global Customers list
I have a strange problem with HQ Manager. All PC's and Server are running HQ 1.3.1002. When all but one user with admin level access opens HQ Manager, enters maintenance mode, and clicks Database, Global Customers, the Delete button is visible for a split second and then vanishes. The one user that can see and use the Delete button can use it from any PC, so it appears to be related to the user account, except that the button is missing when running HQ Manager on the Server. As far as I can tell, the users that cannot see the Delete button have exactly the same permissions and level. There...

Sorting emails by domains, from org to edu (right char is the most significant)
Hello All I need to sort the domains according their emails. For example: Before sorting: john1@abc.edu john3@abc.org john5@abc.com john4@bcd.org john2@bcd.edu john6@bcd.com After sorting: john3@abc.org john4@bcd.org john5@abc.com john6@bcd.com john1@abc.edu john2@bcd.edu That is, how to sort, according to the domain name ( the right is the most significant )? Thanks. Z. D. On Feb 15, 11:09 pm, "duzhid...@gmail.com" <duzhid...@gmail.com> wrote: > That is, how to sort, according to the domain name ( the right is the > most significant )? you'll probably need ...

contact list #13
Good Day, I hope I am in the right area, we are running Exchange 2003 one Server 2003 and have Outlook 03 clients. We have address lists in the public folder an admin and a vendor, these contain contact information on contrators and vendors. In the folder list I can browse down and few and click on a contact to send them an email, but when I open a new email to send and I click on to and what to chose the list from the address book drop down box they are not there. How do I set it up so vendors and Admin contact list appear in the drop down box in the contact list? Thank you David ...

Printing mailing list directly to envelops using Access
I have come across what I thought would be a simple problem to solve. I would like to print mailing lists directly onto envelops using Access. Currently I print to labels and then put these labels onto the envelopes, then a thought struck, why not print them directly onto the envelop. I have tried custom page layout, but nothing seems to work. Any suggestions are deeply appreciated. Frank, With your report open in design view, from the File menu select the Page Setup option. Change the Orentation to Landscape. Then from the Paper Size list select the desired envelope size. In...

how to find an item in a list control?
hi all, In a list Control how to find an item. If it is found then the item should be highlighted. how to do this? i wrote the following code but its not working what's wrong in the code? LVFINDINFO info; int nIndex; info.flags = LVFI_PARTIAL|LVFI_STRING; info.psz = (LPCTSTR) str; //fd.m_strEditCtrl; sprintf(st,"%d",m_ListCtrl.GetItemCount()); MessageBox(st,"Count"); for(int i=0;i < m_ListCtrl.GetItemCount();i++) { if((nIndex = m_ListCtrl.FindItem(&info,-1)) == -1 ) { m_ListCtrl.SetItemState(nIndex,LVIS_SELECTED,LVIS_SELECTED); } } thanks and r...

data sort
ok now should be simple >> I need to sort by month on data that is held in format >> day/month so eg 1511 1510 3011 3010 now custom/ends with/ 11... does not work custom/ends with/ ??11.. or *11 does not work either contains 11 does not work (& would also be wrong if data set contained 1011) but still I am stumped so any help would be great cheers Alex I would be inclined to add a new, temporary field of formulas that pull off the right 2 digits, and sort by that: =RIGHT(A1,2) -- Jim Rech Excel MVP ...

Fill table with values from list
I have a list and a table on separate sheets in an Excell workbook. First sheet has a list of names. I'm trying fill in a table on the second sheet with the EXACT names from the other list as they are assigned to various groups. (Each name will be used exactly once). Any idea how I can program the table to anticipate the content like that? ...

add extra item to a combo box?
Hello, I have a form with a combo box on it and wish to add 1 item to the list. How do I do this? Thanks Geoff PS I can create a new combo box with an extra item but the table does not reflect this extra item ... I can however open the form and move to any record and use the drop down list to add the new item ... if the cbo is a bound object you can use the Not in List event and program it to add the text using ado or doa to the table and then I use a macro to requery the cbo. If it is unbound I am unsure, I have never done an unbound before. "Geoff Cox" wrote: > Hello...

Changing Newsgroup order List
Is there a way to change the order of the groups listed? For example I want Word to be the second group and not the third listed. -- Are there errors in the Bible? Do Animals go to Heaven? Is drinking Alcohol a sin? Is the KJV the only translation to be used? If you want to learn, get answers, and be able to defend the faith, CERM is your place. http://www.cerm.info/ John Wolf wrote: > Is there a way to change the order of the groups listed? For example > I want Word to be the second group and not the third listed. I don't believe so. They appear alphabetically fo...

Elegant way to restrict data entry to list of values
Hi all: I need to provide a way to restrict data entry on a large range. For example, I need the first 100 rows of column 1 to only provide 'yes, no' as possible entry values. The first 100 rows of column 2 to provide 'yes, no, maybe'; the first 100 rows of column 3 to provide 'client, server, unsure', etc ... I thought about using named ranges linked to drop down controls, but that would result in many controls (over 300 based on above example) on the spreadsheet and lead to performance issues. Can anyone suggest a better way to go about doing this? It would be ni...

Macro: Save and update/replace comments in a list
Hey guys My macro makes a list (in sheet "BOARD") of all files in a folder given as variable path. To each file my macro generates an hyperlink / "open-file funktion" as well. This file-list is placed in range A5-A?? (depending of number of files). The macro works well (Hurra!!!) Now I want to write (short) comments to the file-names in column B / respectivly row (you know there are a lots of incomprehensible filenames in the world, and I want to add and save some information of content in my file-list machine). My aim is to save the filenames + comments in ...