Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default alphabetize - automatically

I have a column (A) on a worksheet (2) that is linked to another spreadsheet
(1) for all the available name spots. So, this column (A) won't necessarily
have every cell filled in. I want to alphabetize this list and omit the
blank cells. I also want that put into 2 columns (B & C) instead of 1. The
ending columns (B & C) will grow and shrink according to how many people are
in the original list. Also, I want to omit the duplicates. The worksheet (1)
that these names are on is the data entry sheet for who is doing what for a
seminar. So, a person could be in two different areas on the sheet (1) and
thus, end up twice on the column (A) on the worksheet (2). Any ideas?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default alphabetize - automatically (using array function)

"mandyjo830" wrote:

I have a column (A) on a worksheet (2) that is linked to another spreadsheet
(1) for all the available name spots. So, this column (A) won't necessarily
have every cell filled in. I want to alphabetize this list and omit the
blank cells. I also want that put into 2 columns (B & C) instead of 1. The
ending columns (B & C) will grow and shrink according to how many people are
in the original list. Also, I want to omit the duplicates. The worksheet (1)
that these names are on is the data entry sheet for who is doing what for a
seminar. So, a person could be in two different areas on the sheet (1) and
thus, end up twice on the column (A) on the worksheet (2). Any ideas?


That was my original question. I have searched the net and have found this
formula to eliminate the blanks, but it isn't eliminating the blanks. I
think the problem is that the range contains formulas. So, how do I get it
to eliminate the blank results and then alphabetize them?

Here's my current formula:
=IF(ROW()-ROW(NoBlanksRange)+1ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL( (IF(BlanksRange<"",ROW(BlanksRange),ROW()+ROWS(Bl anksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default alphabetize - automatically

See if this helps:

http://tinyurl.com/y9sv3x

Biff

"mandyjo830" <u29094@uwe wrote in message news:6948fb3a04734@uwe...
I have a column (A) on a worksheet (2) that is linked to another
spreadsheet
(1) for all the available name spots. So, this column (A) won't
necessarily
have every cell filled in. I want to alphabetize this list and omit the
blank cells. I also want that put into 2 columns (B & C) instead of 1.
The
ending columns (B & C) will grow and shrink according to how many people
are
in the original list. Also, I want to omit the duplicates. The worksheet
(1)
that these names are on is the data entry sheet for who is doing what for
a
seminar. So, a person could be in two different areas on the sheet (1)
and
thus, end up twice on the column (A) on the worksheet (2). Any ideas?



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatically generating a number range Alyssa C. Excel Worksheet Functions 2 September 5th 06 11:24 PM
Automatically multiply be a number. lee lee Excel Worksheet Functions 1 April 7th 06 04:24 AM
Copy Rows to another Sheet Automatically Tim & Holly Excel Worksheet Functions 2 March 7th 06 01:53 PM
Automatically update cells Maddoktor Excel Discussion (Misc queries) 1 December 8th 05 01:56 AM
Excel file automatically opens - More Info Lost4Now Excel Discussion (Misc queries) 2 December 5th 05 12:01 AM


All times are GMT +1. The time now is 10:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"