Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stuck on combining two lists with different column headers | Excel Discussion (Misc queries) | |||
Help with Lists... | Excel Discussion (Misc queries) | |||
Lists... | Excel Discussion (Misc queries) | |||
VLOOKUP and Multi Lists | New Users to Excel |