Help with lists please
 

Hi,

I have a work book. The first sheet is basically a list of names.
Each susequent page has severall dropdown boxes using that list. Is it
possible that each time a name from that list is selected, a number
appears on the first page in the column next to the name (the number
being the number of times that name is used?)

When I finished compiling the list on the first page, I selecte dthem
all and named them.

On subsequent sheets i used a data validation to make the drop down box
and names available. All the subsuquent sheets have a number of fields
that use the list, i'd just like to get the first page to show how
often each is selected.

Any help much appreciated.


--
Mediaid
------------------------------------------------------------------------
Mediaid's Profile: http://www.excelforum.com/member.php...fo&userid=9653
View this thread: http://www.excelforum.com/showthread...hreadid=573752

Help with lists please
 

=sum(countif(match1:match20!a6:e13,a4))

I am assuming i'll need something like that, but can't get it to work!
What am i missing?


--
Mediaid
------------------------------------------------------------------------
Mediaid's Profile: http://www.excelforum.com/member.php...fo&userid=9653
View this thread: http://www.excelforum.com/showthread...hreadid=573752

Help with lists please
 

Mediaid,

The first segment of the 'countif' statement is the cell reference or range
that you want to count, the second part of that is the criteria.

The ! follows a sheet name, as part of the reference or range.

Assuming that each name in your list is unique, then you could use multiple
countif statements.

If the list of names is in column A of sheet1, and you have multiple drop
down boxes in different sheets, then in column B of sheet1 (next to the list
of names) you could write in cell B2 (next to the top name in column A)

=COUNTIF(Sheet2!$A$1,A2)+COUNTIF(Sheet2!$A$4,A2) and so on for each range
or reference. Where Sheet2!$A$1 and Sheet2!$A$4 is the reference to two cells
that contain the dropdown boxes, and A2 is the the criteria.

It seems like this might be the long way around, and there probably is a
better way, but it works. If you do end up using something like that, once
you are finished putting all of the countif statements in the formula then
all you would have to do is drag that formula all the way down.

So next to each name in the list of names would the number of each name
selected.

Hope this helps,

---BigPig---



"Mediaid" wrote:


=sum(countif(match1:match20!a6:e13,a4))

I am assuming i'll need something like that, but can't get it to work!
What am i missing?


--
Mediaid
------------------------------------------------------------------------
Mediaid's Profile: http://www.excelforum.com/member.php...fo&userid=9653
View this thread: http://www.excelforum.com/showthread...hreadid=573752

Help with lists please
 

That's a Big help. Took a while but it's done now!

Like you I am sure there would be a quicker way! But thats done the
trick!

Cheers!


--
Mediaid
------------------------------------------------------------------------
Mediaid's Profile: http://www.excelforum.com/member.php...fo&userid=9653
View this thread: http://www.excelforum.com/showthread...hreadid=573752