It's been a long time since I have dabbled in spreadsheets, but a committee I'm on at work uses a spreadsheet to sell tickets, and we needed a mechanism to try and avoid duplicates & prevent people from buying more tickets than allocated. The scenario is this: there are 6 or so people selling tickets to the rest of the company employees, and each employee is allowed 6 tickets. Because the tickets are at a substantial discount, there was concern that people might purchase more by simply going to another person in a different area and purchasing more.
One member developed a workbook with a separate worksheet for each committee member to track their sales, to try and avoid and/or catch duplicate buyers, there was a summary sheet where a single list of the names was referenced with simple cell references from the other users sheets. After entering names people would check the summary sheet for duplicate entries by doing a sort. I volunteered to try and make it so a warning would pop up as soon as a duplicate name (first name, last name in one column) was entered.
I was going to set up a vaildation column on each sheet with a nested IF/lookup formula of sorts, with simply having "duplicate" or "OK" show as soon as they entered. Then I saw that excel now has a validation feature so I checked that out. What I ended up doing was using a custom validation feature for the cells A7:A60 on each sheet with the formula =COUNTIF('summary'!$A$2:$A$433,A7)=1 in the formula box. As you can see, I have it checking the summary of referenced cells in the sheet named "summary" for any duplicates, and A7 is the first row in each persons worksheet where names are entered.
What I ended up doing was highlighting A7:A60 in each persons sheet, then choosing the data validation feature and entering the COUNTIF formula each time. It works fine, but it was a lot of work doing each sheet separately. I thought I could put the validation itself on the summary sheet instead of each persons individual sheet, but this didn't work when I tested it, as I could enter duplicate names in any of the worksheets with no error message popping up.
My method works fine, it catches duplicates and is not case sensitive, I instructed the users just to make sure the names are spelled correctly and to make sure not to add extra spaces etc., I was going to add an employee # row as then that would be unique, but this isn't that critical so I just left it using the name field.
Is the way I did it the only way to use validation across multiple sheets, or is there a better more efficient way? Had I been able to just put the validation on the summary sheet it would have been really fast, but it didn't work. Given that I only had to deal with 5 other worksheets it was not that onerous, but if there had been more numerous sheets it would have been a lot of work.
(Mind you, if I had built this in the first place I likely would have just used a database)
thanks in advance
One member developed a workbook with a separate worksheet for each committee member to track their sales, to try and avoid and/or catch duplicate buyers, there was a summary sheet where a single list of the names was referenced with simple cell references from the other users sheets. After entering names people would check the summary sheet for duplicate entries by doing a sort. I volunteered to try and make it so a warning would pop up as soon as a duplicate name (first name, last name in one column) was entered.
I was going to set up a vaildation column on each sheet with a nested IF/lookup formula of sorts, with simply having "duplicate" or "OK" show as soon as they entered. Then I saw that excel now has a validation feature so I checked that out. What I ended up doing was using a custom validation feature for the cells A7:A60 on each sheet with the formula =COUNTIF('summary'!$A$2:$A$433,A7)=1 in the formula box. As you can see, I have it checking the summary of referenced cells in the sheet named "summary" for any duplicates, and A7 is the first row in each persons worksheet where names are entered.
What I ended up doing was highlighting A7:A60 in each persons sheet, then choosing the data validation feature and entering the COUNTIF formula each time. It works fine, but it was a lot of work doing each sheet separately. I thought I could put the validation itself on the summary sheet instead of each persons individual sheet, but this didn't work when I tested it, as I could enter duplicate names in any of the worksheets with no error message popping up.
My method works fine, it catches duplicates and is not case sensitive, I instructed the users just to make sure the names are spelled correctly and to make sure not to add extra spaces etc., I was going to add an employee # row as then that would be unique, but this isn't that critical so I just left it using the name field.
Is the way I did it the only way to use validation across multiple sheets, or is there a better more efficient way? Had I been able to just put the validation on the summary sheet it would have been really fast, but it didn't work. Given that I only had to deal with 5 other worksheets it was not that onerous, but if there had been more numerous sheets it would have been a lot of work.
(Mind you, if I had built this in the first place I likely would have just used a database)
thanks in advance
