Data Validation 05-08-10

I’m doing data validation on a group of cells.  At the moment all the names 
of the employees appear on the drop down list for those cells, but what I 
need is to compare those names to a day-off list where the people who are off 
on that day appear.  If their names appear on the day-off list then they 
should not appear on my data validation cells.

In other words I have colA-colG with the names of the week, Mon-Sun.
I have 30 rows. The first 15 rows are for my data validation and they are 
drop downs of the people who are working on that day. The source for those 
rows comes from a separate sheet where all the names of my employees are 
listed.  Rows 16-30 have the names of the people who are off that day.  So 
the names in Rows 1-15 should not match 16-30.

Thanks.

0
Utf
5/8/2010 4:53:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
660 Views

Similar Articles

[PageSpeed] 5

On 5/8/2010 12:53 PM, Lost Cluster wrote:
> I’m doing data validation on a group of cells.  At the moment all the names
> of the employees appear on the drop down list for those cells, but what I
> need is to compare those names to a day-off list where the people who are off
> on that day appear.  If their names appear on the day-off list then they
> should not appear on my data validation cells.
>
> In other words I have colA-colG with the names of the week, Mon-Sun.
> I have 30 rows. The first 15 rows are for my data validation and they are
> drop downs of the people who are working on that day. The source for those
> rows comes from a separate sheet where all the names of my employees are
> listed.  Rows 16-30 have the names of the people who are off that day.  So
> the names in Rows 1-15 should not match 16-30.
>
> Thanks.
>
If I understand you correctly, a name should only appear once in a 
column. I would use COUNTIF formulas in Columns H thru N. For example, 
if the formula =COUNTIF(A$2:A$31,A2) in Column H, Rows 2-31 shows a 
number greater than 1, that says there is more than one occurrence of a 
particular name. Or, to make it easier to spot, you combine an IF 
function with a COUNTIF formula such as 
=IF(COUNTIF(A$2:A$31,A2)>1,"PROBLEM",1) so that the word PROBLEM would 
appear any time there's more than one occurrence of a name.
Hope this is helpful.
Bob Ryan
0
Bob
5/8/2010 9:39:16 PM
You would need VBA (programming) for that.  But there is one decision for 
you to make first.  VBA would create a DV list for each day of the week 
based, of course, on what is entered in rows 16-30.  In practice, you would 
enter one name in rows 16-30, then maybe another name, then maybe another 
name, and so on.  What you have to decide is when do you want VBA to setup 
those DV lists?  Every time a name is entered?  Or maybe when you are 
through entering names in rows 16-30?  But how would VBA know you are 
through?  Or maybe place a button somewhere on the sheet that you would 
click on to tell VBA to create those lists?  Another question I would need 
answered is do you want all 7 days calculated at one time?  If not, an 
option would be for you to click on the day-of-the-week cell in row 1 and 
this would trigger VBA to calculate the DV list for that day only.  Post 
back with your thoughts.  HTH  Otto

"Lost Cluster" <LostCluster@discussions.microsoft.com> wrote in message 
news:D4B3CA9C-1064-4702-B8B6-BADA5AD2074B@microsoft.com...
> I’m doing data validation on a group of cells.  At the moment all the 
> names
> of the employees appear on the drop down list for those cells, but what I
> need is to compare those names to a day-off list where the people who are 
> off
> on that day appear.  If their names appear on the day-off list then they
> should not appear on my data validation cells.
>
> In other words I have colA-colG with the names of the week, Mon-Sun.
> I have 30 rows. The first 15 rows are for my data validation and they are
> drop downs of the people who are working on that day. The source for those
> rows comes from a separate sheet where all the names of my employees are
> listed.  Rows 16-30 have the names of the people who are off that day.  So
> the names in Rows 1-15 should not match 16-30.
>
> Thanks.
> 
0
Otto
5/8/2010 9:49:58 PM
Here are 2 macros that will do what you want.  I assumed the sheet with 
lists is named "Lists".  Change this as needed.  This code will fire when 
you click on one of the header cells in row 1, in columns A to G.  It will 
set up the DV cells in that column.
The first macro is a sheet event macro and must be placed in the sheet 
module of your sheet (not the Lists sheet).  To access that module, 
right-click the sheet tab and select View Code.  Paste this macro into that 
module.  "X" out of the module to return to your sheet.  The second macro 
goes in a regular module.  Post back if you need more.  HTH  Otto

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    If Target.Row = 1 And Target.Column < 8 Then
        Call SetupList(Target.Column)
    End If
End Sub

Sub SetupList(c As Long)
    Dim Off As Range
    Dim i As Range
    Dim TheDVName As String
    Dim Dest As Range
    Set Off = Range(Cells(16, c), Cells(30, c))
    Select Case c
        Case 1: TheDVName = "Mon"
        Case 2: TheDVName = "Tue"
        Case 3: TheDVName = "Wed"
        Case 4: TheDVName = "Thu"
        Case 5: TheDVName = "Fri"
        Case 6: TheDVName = "Sat"
        Case 7: TheDVName = "Sun"
    End Select
    With Sheets("Lists")
        If Not IsEmpty(.Cells(2, c).Value) Then
            .Range(.Cells(2, c), .Cells(Rows.Count, 
c).End(xlUp)).ClearContents
        End If
        Set Dest = .Cells(2, c)
        If Application.CountA(Off) = 0 Then
            Range("All").Name = TheDVName
        Else
            For Each i In Range("All")
                If Off.Find(What:=i, Lookat:=xlWhole) Is Nothing Then
                    Dest = i.Value
                    Set Dest = Dest.Offset(1)
                End If
            Next i
            .Range(.Cells(2, c), .Cells(Rows.Count, c).End(xlUp)).Name = 
TheDVName
        End If
    End With
End Sub


"Lost Cluster" <LostCluster@discussions.microsoft.com> wrote in message 
news:D4B3CA9C-1064-4702-B8B6-BADA5AD2074B@microsoft.com...
> I’m doing data validation on a group of cells.  At the moment all the 
> names
> of the employees appear on the drop down list for those cells, but what I
> need is to compare those names to a day-off list where the people who are 
> off
> on that day appear.  If their names appear on the day-off list then they
> should not appear on my data validation cells.
>
> In other words I have colA-colG with the names of the week, Mon-Sun.
> I have 30 rows. The first 15 rows are for my data validation and they are
> drop downs of the people who are working on that day. The source for those
> rows comes from a separate sheet where all the names of my employees are
> listed.  Rows 16-30 have the names of the people who are off that day.  So
> the names in Rows 1-15 should not match 16-30.
>
> Thanks.
> 
0
Otto
5/10/2010 2:37:27 PM
> I=92m doing data validation on a group of cells.  At the moment all the n=
ames
> of the employees appear on the drop down list for those cells, but what I
> need is to compare those names to a day-off list where the people who are=
 off
> on that day appear.  If their names appear on the day-off list then they
> should not appear on my data validation cells.
> In other words I have colA-colG [for] Mon-Sun.
> I have 30 rows. The first 15 rows are for my data validation and they are
> drop downs of the people who are working on that day. The source for thos=
e
> rows comes from a separate sheet where all the names of my employees are
> listed.  Rows 16-30 have the names of the people who are off that day.  S=
o
> the names in Rows 1-15 should not match 16-30.

The following seems to work with Excel 2003.
For convenience, I put complete employee list in J1:J15 of the same
sheet. Putting them on a different sheet is a straightforward change.

I use rows 31:46 as helper rows.

Leave row 31 empty.

In A32 put
  =3DIF(COUNTIF(A$16:A$31,$J1)>0,"",MAX(A$31:A31)+1)
Then copy A32 down to A46. Then select A32:A46 and copy rightward to
column G.

In A1 put
  =3DIF(ROW()>MAX(A$32:A$46),"",
     OFFSET($J$1,MATCH(ROW(),A$32:A$46)-1,0))
Then copy A1 down to A15. Then select A1:A15 and copy rightward to
column G.

Modify to suit.
0
zvkmpw
5/11/2010 7:42:06 PM
Reply:

Similar Artilces:

Spotting similar data
I have 12 columns of number that go a couple of hundred rows deep. The numbers range from 1 thru 5. Any of the rows that have all 3's are bogus data and I wish to delete them. Right now I am doing this manually by scanning down through the spreadsheet. Is there a faster and easier way to do this? I am using Excel 2003 on an XP machine. Thanks very much. Put a formula like this in M2 (assuming a header row): =3DIF(COUNTIF(A2:L2,3)=3D12,"Remove","okay") then copy down. Then apply autofilter to column M and select "Remove" from the filter pull-down. Highlight ...

data loss between workstations
In an XP home peer-peer net. when computer 'c' changes an excel doc., that resides on comp. 'a',then saves and closes the changes arn't always seen on 'b' when they open the doc. later. this is intermittant. Help?! thanks "jay" <anonymous@discussions.microsoft.com> wrote in message news:038901c3ae31$031f4f80$a301280a@phx.gbl... > In an XP home peer-peer net. when computer 'c' changes an > excel doc., that resides on comp. 'a',then saves and closes > the changes arn't always seen on 'b' when they open the >...

Importing Data #8
How do I import a Delimited Text (DOS ASCII file) into Excel I am using Excel 2000 Cliff-Stouff wrote: > How do I import a Delimited Text (DOS ASCII file) into Excel > I am using Excel 2000 File-Open and change the file type to txt. then follow the prompts. -- Registered Linux User no 240308 Just waiting for Broadband to complete the conversion!(3 weeks and counting!) gordonATgbpcomputingDOTcoDOTuk to email me remove the obvious! Cliff Just open it as is and the Text Wizard will open to allow you to parse according to type of delimiter. Gord Dibben Excel MVP On Wed, 26 Jan 2005...

Automatic refresh of external data
I've looked and played with all the settings that I think would allow automatic refresh of external data, but I still get a dialog box every time I open the workbook that asks if I really want to enable or disable automatic refresh. Yes I really do! Where is the switch or property which automatically enables this? TIA Keith BTW I using 2003. I assume I'm the only one with this problem. "Keith Streich" <kstreich999@execpc.com> wrote in message news:e2xtwtOGFHA.936@TK2MSFTNGP12.phx.gbl... > I've looked and played with all the settings that I think would ...

unable to update public free/busy data --- how do i get rid of this error?
I keep getting an error popping up while Outlook 2002 (10.2627.3501)SP-1 is running. "unable to update public free/busy data" This is running on a Dell Optiplex gx270 for the last 2.5 years, with no problems until recently. Probably due to some piece of software installed. Anyone have ideas how I can stop this error from randomly coming up each day?? I did find a patch for the registry, but it did not work. I am not communicating with Exchange server at all. Thanks for any help. Jay Try applying all SPs to the installation. --� Milly Staples [MVP - Outlook] Post all replies ...

obtaining data from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb See your other post. "Chau" <Chau@discussions.microsoft.com> wrote ...

Formating excel multiple paragraphs and colums using one source of data
Scenario: Sheet#1: column #1 product names separated by category names. column#2 prices corresponding to products. Final sheet is basically all product pricing. To print this out i need to create lay out on sheet # 2 This sheet would have three vertical frames. this frames would have two columns(one for product names one for pricis) Sheet #1 would project the content to sheet #2 in such way that if sheet #1 content changes all the content would get formated in number #2 sheet accordingly .(This content will have multiple pages within a sheet. How can I achieve that?. ...

Unknown data in cell when number entered
If someone could help me I would appreciate it. I have written an exce program that calculates. When I enter a whole number into the cell an hit return I get unknown text with the number. Ex: If I enter 5 an hit return it shows 5E-33. Can anyone tell me why this occurs? really don't need the extra text and numbers to appear. Thanks in advance, Sa -- Message posted from http://www.ExcelForum.com Satellite, Tools / Option / Edit tab : Uncheck Fixed decimal (with likely 33 as the number of places) -- Regards, Auk Ales * Please reply to this newsgroup only * * I will not re...

PLease help CRM Data Migration Framework not migrating any records
Hi all I populated my cdf_<Entity> and cdf<Entity>_Info tables using a .net app and verified that the record counts in both tables were matching for each of the entities i.e Contact, Leads, Account etc but when I run the data migration framework to move the data from the cdf)<Entity> tables into the CRM tables it does not move any data but alos does not return an error . I checked the log and for each record that it tried to migrate it returns a status saying failed to migrate but no reason. I am running out of ideas as to why this is happening . This is my first exper...

Combo Box Drop-down list is based on specific data
I have a table (ASSIGNEES), which has four columns (ID, ASSIGN-NAMES, EMAILADDRESS, DEPARTMENT). I need to set up a combo box that only shows the names associated with a specific department when I click on the drop-down arrow. For example, anyone with a department of MGT will not show in the drop-down selection and anyone with a department of ENG will show up. Thank you in advance for your help. Linda You put the criteria in the query that you create in the record source. So in the query, reference the control on the form that has the department you want (or don't want) to s...

Upgrade from v8.0 to 9.0 or v8.0 to 10.0?
Hi there, My company upgraded Great Plains from 7.5 to 8.0 in September 05. Our VAR did a very poor job of the upgrade and it cost us way more than first told. Anyways, I just renewed our service plan today and I'm trying to evaluate whether it is really worth it to upgrade from 8.0 to either 9.0 to 10.0. I see upgrading to 9.0 as a safer option since 10.0 will take awhile before all the bugs are worked out, I want to ensure a stable working environment. If anyone could provide their recommendations as to why to upgrade or not upgrade that would be greatly appreciated. Thanks, Mark Hi...

entering time data
is there a way to enter minute second data e.g., 30:43 without having to enter the hour (none of the data points are over 59 minutes duration) ---- i'm using the format [h]:mm:ss (since i want to add the values and the total will exceed several hours) --- it seems to require an entry of the form 0:30:43 in order for the cell to contain the correct value ---- is there a way to avoid entering the colons altogether thanx nick carriero It looks like you could use this code if you alter it: http://www.ozgrid.com/VBA/mask-time-input.htm ************ Hope it helps! Anne Troy www.Offic...

Cannot Create a new Company 07-13-10
Hi: I'm trying to create a new company in GP, we already have three up and running since November 2009, now i need to add six new companies, just for financial records. While running the utilities, in the 'Load Store Procedures' it gives me: "The following SQL statement produced an error: Grant Execute ON dbo.SE_Get_Acc_Detail_Open TO DYNGRP" I clicked 'Ok' then it gives me: "ERROR [Microsoft][SQL Native Client][SQL Server]Cannot find the object 'SE_Get_Acc_Detail_Open', because it does not exist or you do not have permission.&quo...

Data Migration Failed
I got this error DOC 1 ERROR: A remove operation on table 'SY_Batch_Activity_MSTR' failed because the record couldn't be locked. Integration Failed ..... Please give me an advice how to solve this erorr. Thanks. -- Gee I already fine my solution. It is because of security excess on alternate window modification. -- Gee "Gee" wrote: > I got this error DOC 1 ERROR: A remove operation on table > 'SY_Batch_Activity_MSTR' failed because the record couldn't be locked. > Integration Failed > .... > > Please give me an advice how to solv...

How recover XP without losing data or applications
My XP Pro system won't boot after I installed some applications. It won't boot to safe mode. (After the Windows boot screen it shows a "DOS" list of dll files in System32 and the system hangs there.) I heard of various methods to recover XP Pro but I don't want to lose data or applications. What's the best approach to take? kathie wrote: > My XP Pro system won't boot after I installed some applications. > > It won't boot to safe mode. (After the Windows boot screen it shows > a "DOS" list of dll files in Syst...

subtract two data fields in pivot table
Hi, I have a pivot table in which I have 5 column entries and 2 row entries. I want to subtract the two row entries of each column and display the result right below them in the same column, and want the same for each of the 5 columns. Thanks in advance for any help! ...

I can't open Outlook Data File from external sources like CDs #2
Dear technical experts, I have been facing a security problem with Microsoft Outlook files. I exported Outlook items into Office outlook files (with the extension is .pst). The exported files were firstly saved on my computer (different path and location in comparison with default path of Office Outlook files in Documents and Settings). I can open my exported files easily. Because the size of outlook items is very large, I would like to save Office outlook files on another storage device in stead of using hard drives of my computer. I made copy these files to a flash drive (USB dri...

email 04-08-10
I lost all names in auto field for email addresses by alphabet "raceh" <raceh@discussions.microsoft.com> wrote in message news:53E665F7-4EC5-4859-AFC6-1A55198F0AA3@microsoft.com... >I lost all names in auto field for email addresses by alphabet So sorry. Do you have a question? -- Brian Tillman [MVP-Outlook] ...

Ambiguous outer join 01-25-10
I have an update query that joins three tables, A, P & S. S & P are linked with an inner join, P & A with an outer join from P to A. Each join is on three fields, none of which are common between the joins. The query works fine and displays fine in the graphic window, but throws an error message about "The SQL statement could not be executed because it contains ambiguous outer joins." when I attempt to switch to SQL view and throws error 3258 with the same message in VBA when I try to use code to display the SQL. I don't get it - if there's somethin...

How to make a chart with selective (discontinuous) data?
For example, two colums A and B have 10 rows (2 to 11) with values in each cell. How can I make a graph A Vs B which displays data only from rows 2, 5 8 and 10. I mean from discontinuous data and not from a continuous range. If yes how to do in very large sheet? Best to use your worksheet to pull the data into a continuous range, and then plot that. -- David Biddulph "Making grapg from discontinuous data" <Making grapg from discontinuous data@discussions.microsoft.com> wrote in message news:6E74492E-4B84-41B1-B50A-C322A703370E@microsoft.com... > For example, two colu...

Show series name on the chart
I want to show the name of the data series on the chart - but only once. If I check "Series name" in the "Format data series" "Data Labels" tab - then it shows at every data point - I only want it at e.g. the last. How is that done? Hi, To do this manually first select the series and then select the individual point. Now apply data labels. With VBA code have a look at Jon Peltier's page http://peltiertech.com/Excel/Charts/LabelLastPoint.html With dummy data series see http://www.andypope.info/charts/Labellast.htm Cheers Andy -- Andy Pope, Microsof...

When I type 10 in a cell I keep getting 0.1 instead. Any thoughts
Data in cell. When I type the number 10 in a cell 0.1 comes up instead. After totalling Nos. 10, 20, 25, 30 & 15 total comes as 0.100 instead of 100; my formual is =sum(E5:E9). I am using EXcel 2003. I go into general and click on text so I could keep the numbers as stated. I still get it wrong Tools/Options/Edit, uncheck the Fixed Decimal places checkbox. In article <A6D404D4-EACD-4489-8F43-072638A8C087@microsoft.com>, Enthusiastic new user <Enthusiasticnewuser@discussions.microsoft.com> wrote: > Data in cell. When I type the number 10 in a cell 0.1 comes ...

Formatting the datas
Dear Paul, Thanks for your help. Actually the datas i have, dont have any commas or spaces and more over all the fields like name,age are together and broken into 2 or 3 different rows. i wanted to seperate it and put it in a table format. the sample data is as follows. name ddddd last pd date 12/10/2003 age 23 employee ffffff employer fghfghfgh. the result what iam expecting is as follows. name lastpddate age employee employer dddd 12/10/2003 23 fffffff fghfghfgh. can u help me in solving this problem? Bye Faddrick. ...

Need Help... 05-11-10
I have project update form based on parameter query. In the query ProjectEngineer Criteria field, I put ( Like [forms]![formfind]![combo1] ) . The record source of query is TblProject and fields are ProjectEng,….etc. The row source of combo1 of Formfind is a query as SELECT [Employees Extended].ID, [Employees Extended].[Employee Name] FROM [Employees Extended] ORDER BY [Employees Extended].[Employee Name]; The Employee Extended is a query from Employee table. Also I set the column width 0;2 so that it display only the Engineers name not the ID. The problem is that, When I open...

Series Source Data
Tjena! I have plotted one series: =SERIES(;xyzFINAL!$A$*3* :$A$15;xyzFINAL!$B$*3*:$B$15;1) I would like to replace the red bold 3 with INDIRECT(ADDRESS(2;7);1) so that I can put the number 3 in cell G2 instead. How/Is this possible ? Tack --- Message posted from http://www.ExcelForum.com/ ...