Author Topic: Excell assistance  (Read 2550 times)

Mr Dumpster Stache

  • Stubble
  • **
  • Posts: 142
Excell assistance
« on: December 05, 2015, 02:37:15 PM »
I'm googling the answer, but I recalled there are a lot of excell wizards here, so figured I'd ask y'all too:

I'm setting up an excell book for work, as a scheduling tool. Each book is one month, and has a tab for each day. ("February" book has 29 tabs)

I have a column which my team members will be entering numerical data (PO numbers) into, and I would like to set up a data validation rule so that they can not enter a duplicate. I know I need to use the "COUNTIF=1" function, but how do I format the selection to look at all the tabs in the book?

Thanks for the help!

hope2retire

  • 5 O'Clock Shadow
  • *
  • Posts: 78
  • Location: NJ
Re: Excell assistance
« Reply #1 on: December 05, 2015, 02:51:32 PM »
http://www.excel-easy.com/examples/prevent-duplicate-entries.html

can you create a master list in a separate sheet that you can use which will summarize all the numbers.?

ShoulderThingThatGoesUp

  • Magnum Stache
  • ******
  • Posts: 3057
  • Location: Emmaus, PA
Re: Excell assistance
« Reply #2 on: December 05, 2015, 03:50:51 PM »
No duplicates on the page or none in the workbook? A conditional formatting rule with countifs might be your answer.

Mr Dumpster Stache

  • Stubble
  • **
  • Posts: 142
Re: Excell assistance
« Reply #3 on: December 05, 2015, 04:21:30 PM »
Duplicates on the page is easy with the COUNTIF. Duplicates in the workbook appears to be impossible, or at least very difficult. I tried using a page range as the reference - it let me select the pages and looked like it would work, but then it told me that type of selection is invalid.

The master list looks like it might work, but I have not yet found an easy way to set up the 300+ lines required without having to manually format each cell.

boarder42

  • Walrus Stache
  • *******
  • Posts: 7849
Re: Excell assistance
« Reply #4 on: December 05, 2015, 04:30:41 PM »
Try excel vs excell when you search

Mr Dumpster Stache

  • Stubble
  • **
  • Posts: 142
Re: Excell assistance
« Reply #5 on: December 05, 2015, 04:34:51 PM »
Try excel vs excell when you search
Fortunately google is smarter than I am and automatically corrects for that. :D

hope2retire

  • 5 O'Clock Shadow
  • *
  • Posts: 78
  • Location: NJ
Re: Excell assistance
« Reply #6 on: December 05, 2015, 07:57:50 PM »
Created three tabs 01,02 with some numbers as examples
Created a master list that copies all the cells from 01 and 02
Created data validation in the yellow region of sheet 03 using the master list.

Enter numbers in sheet 03 that is not there in 01 and 02, it will accept. if you enter numbers that exist in 01,02 it will not let you. Ex: enter 2009, 2010 in sheet 03 it will accept. but if you enter 2008 it will not. see if this fits your bill. you have to follow this validation in all sheets including 01 and 02 according to the master list that should work.

h2r
« Last Edit: December 05, 2015, 08:13:19 PM by hope2retire »

Mr Dumpster Stache

  • Stubble
  • **
  • Posts: 142
Re: Excell assistance
« Reply #7 on: December 06, 2015, 09:53:24 AM »
hope3retire - that's basically what I did. It seems to be working, it was just a little fiddly to set up. 9 lines per day, 30 days per month... Now to see if the team can find a way to break it. :D

hope2retire

  • 5 O'Clock Shadow
  • *
  • Posts: 78
  • Location: NJ
Re: Excell assistance
« Reply #8 on: December 06, 2015, 10:07:57 AM »
Yeah setup in one page(like in 03 sheet) and copy those sheets 30 times and rename it. then just setup the master list page that is it, you are done. Key is to equate the countif to 0. Good luck

h2r