A lookup with a difference

[Smiley]

Hi all,

I am wondering what the best method would be to create a lookup. I have
this column of data (Column A) with Sub-Committees and different people
that belong to these Sub-Comms. The Name range is defined as
"Sub-Committees"

I want to be able to type in a name and the result should be:

1. Person does exist; and
2. They belong to x,y,z Sub-Committee(s) - plural if they are in more
than one Sub-Committee.

or

1. Person not in current list;
2. Choose which Sub-Committee(s) they need to be added; and
2. Add their name.

Any and all help is very much appreciated.

Shiraz.

 

[cailotto]

If the person does not exist and it doesn't come up in your defined
range, you will get the result of #N/A. You can use the iserror
function and use an if statement to have the result toggle to "Person
not in current list."

 

[Pete]

In addition to your list of sub-committees, you will need a list of
people. I would suggest that both of these are treated as tables, the
second column of each will be the number - the number of members in the
committee table and the number of committees served on in the people
table. Subsequent columns would refer to membership - for the committee
table these would point to the people who serve on the committee and
for the people table these would point to the committee(s) on which
they sit.

You can then think about the routines necessary to maintain this
structure - Add a new Committee (and its membership), Add a new Person
(and the committee(s) served on), Delete a Committee (and references
within the People table), Delete a Person (and references within the
Committee table), Committee membership Report, Report of People with
their membership etc.

This would involve some VBA programming, rather than simple Excel
lookup functions.

Pete

 

[rsenn]

Oh, I think it can be done.

Check the simple file at this link and see if that is what you can
build on.

http://www.anywhereenterprises.com:80/1/1/a?a=dF&p=sOWqqzY5isOWOAApQZOAApQZApz