Check box or to not check box

[dmichael66]

I am trying to create a basic spreadsheet in MSExcel used to collect standard info (Name, address blah blah blah). The people who will complete this will need to classify the people they are listing into groups (Group A, Group B etc. 5 groups total) and each group will be listed on a separate tab of the workbook (maybe this isn't the best way?).

As they enter the people they will need to decide if the person they are adding is to receive the welcome kit. Each company is limited to 50 welcome kits. My thought is to use a check box for them to indicate yes or no on the welcome kit.

Is there a way to display the number of welcome kits that are remaining on whatever tab they happen to be on?

Is there a way to give a clear indicator next to each name that 'yes' has been selected (colored box, text?).

While I am fairly savvy with most software this is a little outside of my range so explanations written for a 10 year old would be helpful. Thanks.

 

[Domski]

Hi & welcome,

I would suggest keeping all the info on one sheet to start off with and probably using Data Validation in a cell rather than a check box if you want to keep it simple.

With all the data in one sheet you can use a pivot table to keep track of the number of packs given out to each group in one table rather than having to look across multiple sheets. It also makes any other analysis you want to do much easier.

For info on data validation: http://www.contextures.com/xlDataVal01.html

And some tutorials on pivot tables: http://www.datapigtechnologies.com/ExcelMain.htm

Hope it helps,

Dom

 

[rs2k]

One way is to use checkbox's from View>Toolbars>Forms. Select checkbov and place on worksheet. Right click and choose format control and under the control tab choose it's linked cell, do this for all the checkboxes you need (making the linked cell one below the previous) and then use a COUNTIF formula (=COUNTIF(A1:A6,TRUE)) to count how many have been checked. You could always hide the column if you wanted.

HTH
Colin

 

[dmichael66]

So everything is on one page... how am I to keep track of the number remaining? The running total is for the users benefit, not mine.

 

[Domski]

Here a simple example:

Book1
	A	B	C	D	E	F	G
1	Name	Company			Kits Used	Allowed	Remaining
2	Bob	Company A		Company A	5	50	45
3	Dom	Company B		Company B	4	50	46
4	Jim	Company A		Company C	4	50	46
5	Katie	Company B
6	Janet	Company B
7	Mark	Company A
8	Sally	Company C
9	Steve	Company B
10	Paul	Company C
11	Chris	Company A
12	Susan	Company C
13	Mike	Company A
14	Rob	Company C
Sheet1

Formula in E2 copied down to E4: =COUNTIF(B:B,D2)

Formula in G2 copied down to G4: =F2-E2

The summary table can be located on a different sheet to keep it out of the way and as I said a pivot table could also be used.

Dom

 

[dmichael66]

OK but where do you indicate which people get kits or not? what I am looking for is a simple indicator for each person as to whether they get the kit.

Example - the user lists 5 people in group A - only 3 get the kit; 4 people in group b and 1 gets the kit. So out of the 9 people listed only 4 get the kit and there are 46 remaining. How can I show this to the user as they input the info?

 

[rs2k]

Does the checkbox not work for you?

 

[Domski]

Good point, missed that bit:

Book1
	A	B	C	D	E	F	G	H
1	Name	Company	Kit?			Kits Used	Allowed	Remaining
2	Bob	Company A	Y		Company A	3	50	47
3	Dom	Company B	N		Company B	2	50	48
4	Jim	Company A	N		Company C	3	50	47
5	Katie	Company B	Y
6	Janet	Company B	N
7	Mark	Company A	N
8	Sally	Company C	N
9	Steve	Company B	Y
10	Paul	Company C	Y
11	Chris	Company A	Y
12	Susan	Company C	Y
13	Mike	Company A	Y
14	Rob	Company C	Y
Sheet1

Formula in F2 copied down: =SUMPRODUCT(--(B1:B1000=E2),--(C1:C1000="Y"))

I've only extended the range of the formula down 1000 cells as you can't use full column refs with SUMPRODUCT prior to Excel 2007 and wasn't sure what version you are using. If you are on 2007 you could also use COUNTIFS for multiple criteria but it's not backwards compatible.

Dom

 

[dave3009]

Probably not alot of help now but here's what I made of the original post

http://www.box.net/shared/36yknlr563

It needs work doing to it

 

[dmichael66]

Domski... We are way behind the times here... we are on 2003... After you stop laughing with disbelief can you tell me more about using Countif...

How do you write the following in excel formula speak...

Count how many Trues there are in column A (form check box) and put the result here.

Everyone - Is it possible to set the sheet so that if the condition for the check box is true, that all of the data that follows it (name address etc) is copied to another tab (format/ headers of columns is the same? I am thinking that the last tab could be a collection point for all names of people who are designated to receive the welcome kit. If so, can you provide a sample of how it should be written?

Thanks for everyones help.