Auto Generate List

Oscar, you run the risk of having your posts accidentally deleted by a mod if you use the post icon -- it's what all the spammers use.

Can you post your data layout with some sample entries?

I attached an excel 2003 sample. I use 2007 and save as 2003 for others

No attachment. Must be zipped and less than 100K.

Unzipped version was 50 kb
zipped is 11.7 kb

I have an event that is one week long (7 days), with three functions happening each day (7 days - 3 columns per day).

There are 11 groups with various number of possible attendees listed in rows. Attendees of a given function is indicated by entry of a 1 or 2 (attending partner), Blank = not attending.
I would like to show a list of the attendees for a given function by clicking or moving my mouse pointer on the event function column header.

The list would need to be automatically updated when the attendees status changes (entry or deletion of 1 or 2)

I have an event that is one week long (7 days), with three functions happening each day (7 days - 3 columns per day).

There are 11 groups with various number of possible attendees listed in rows. Attendees of a given function is indicated by entry of a 1 or 2 (attending partner), Blank = not attending.
I would like to show a list of the attendees for a given function by clicking or moving my mouse pointer on the event function column header.

How about just creating a list on sheet2 for each function. Column A would be the 1st day 1st function with those attending, Cloumn B would be 1st day, 2nd function with those attending. etc...

The list would need to be automatically updated when the attendees status changes (entry or deletion of 1 or 2)

Oscar,

I added a sheet and made a list of numbers in column A (1,2,3, ...). In B1 and copied right and down, this array formula (i.e., confirmed with Ctrl+Shift+Enter) lists the attendees at the events:

=INDEX(Sheet1!$B:$B, SMALL( IF( ISNUMBER(Sheet1!E$8:E$100), ROW(Sheet1!E$8:E$100)), $A1) )

It needs an IFERROR wrapper to suppress errors beyond the number of attendees.

Does that get you started?

Thanks for the reply.

I copied the formula as is to test. I got #NUM on the formula. When I looked at it step by step, the error #VALUE is on ISNUMBER. The data in column E
(1st day/1st Function) is defined as A number.

I then tried added =IF(ISERROR(Sheet1!$E6:E$100),"", in front of formula but received #value for ISERROR and ISNUMBER.

So I am testing to see if I can tweak it...

It's an array formula -- it MUST be confirmed with Ctrl+Shift+Enter, not just Enter.

First of all I want to say thanks for you help.

I am using Excel 2007, after downloading and opening the file I received this message:

The following user-defined functions, created in a previous version of Excel, have the same name as a new built-in function of Excel 2007. When these function names are used in a formula, the new built-in function will be used, which may cause different results.
To avoid the name conflict, rename the user-defined functions, and the change all associated formulas so that they use the new name.

Funtion -- iferror

After clicking O,K Excel dit in a loop, not quite opening all the way. The program on the satus bar flashes. I can switch between applications, but cannot access Excel. I even tried to bump up it processing priority, right clicking on the application on the status bar and choosing close. But that doesn't work also. it keep flashing like it is in a loop.

I am able to open another instance of Excel, but not this one.

I'm going to submit this then try again to open your attachment.

Oscar

Oscar, I uploaded a 2007 version in my last post.

THANK YOU,THANK YOU,THANK YOU

It appears to be Just as the doctor ordered!!!

thank for your help.

Oscar

You're welcome, glad it worked for you.