Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

What can I use instead of COUNTIF to hide used data in a list

0
=IF(COUNTIF(Entries!$D$10:$D$104,D32)>=1,"",ROW()-1)

I have rephrased the question Variatus apologies.

I have a list of data which in essence are time slots, but I am only using them as 3 or 4 numbers with a decimal point therein to separate hours and mins. Thanks for the explanation regarding time Variatus. 

In my code that I am using in the "presentation Data" tab under the Used Time heading the Countif function is changing the time slot of say 9.24 to 23 and 24, this column is selected on the "Form" as Tee Times, the number 23, 24, 25, 26  etc, will not mean anything to an end user. I have tried reformatting both column D & E as Time using the Custom filter under numbers but that does not work. 

What can I use instead of COUNTIF to maintaining the formatting of the Time column in the "Used Times" column, but not show previously used times in the list that is showing on the "Form drop down list". 

I presume (a dangerous thing to do in Excel) that the coding would remain the same.

Once again. Thanks in advance. Jim 

Answer
Discuss

Answers

0

In essence, I'm afraid there is still no solution but another attempt at small progress which is accumuated in the code below.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim IDs() As String
    With Target
        If .Address = Range("FormIDNumber").Address Then
            IDs = IDList
            With .Validation
                .Delete
                .Add Type:=xlValidateList, _
                     AlertStyle:=xlValidAlertStop, _
                     Operator:=xlBetween, _
                     Formula1:=Join(IDs, ",")
                .IgnoreBlank = False
                .InCellDropdown = True
                .InputTitle = "Registration ID"
                .ErrorTitle = "Invalid ID"
                .InputMessage = "Please select a registration ID."
                .ErrorMessage = "Please select an ID from the drop-down list."
                .ShowInput = True
                .ShowError = True
            End With
            .Value = IDs(0)
        End If
    End With
End Sub
Private Function IDList() As String()
    Dim Fun() As String
    Dim Tbl As ListObject
    Dim Arr As Variant
    Dim R As Long, i As Long    
    Set Tbl = Worksheets("Presentation Data").ListObjects("IndexTable")
    With Tbl.DataBodyRange
        Arr = .Columns(Tbl.ListColumns("Slot ID").Index).Resize(, 2).Value
    End With    
    ReDim Fun(UBound(Arr))
    For R = 1 To UBound(Arr)
        If Not IsDate(Arr(R, 2)) Then
            Fun(i) = Arr(R, 1)
            i = i + 1
            If i > 24 Then Exit For         ' max 255 characters
        End If
    Next R    
    If i Then ReDim Preserve Fun(i - 1)
    IDList = Fun
End Function

This code deletes any existing validation in named range FormIDNumber and replaces it with a new one. You may like to review the messages. Note that the code limits the number of entries in the list to 24 because teh total list can't be longer than 255 characters. You can adjust the total up or down within the said limit. Note also that the code suggests the first item in the list, over-writing the previous cell value. This is the line of code that does it. .Value = IDs(0). It can be removed.

In order to make this work I have added the table IndexTable to your worksheet Presentation Data. It is referred to by name in the above code, as is its first column. The idea is that you somehow enter the current time in column B (Time), and this causes the Slot ID to be excluded from the list in the form. The result is a list of IDs with their respective Tee-off times which you can use in the rest of your system once you connect it. I have chosen a Date/Time format which you can display in different formats in Entries!C:D.

I hope this helps.

Discuss

Discussion

Morning, Thanks for the reply, to answer the questions that you raise 

Presentation data "A:B" and "D:E" are 2 totally seperate entities. "A:B" is application numbers . and on those applicants will give a prefferred tee off time on their application forms. The administrator would then allocate to the applicant the nearest available tee off time that is available.  "D:E" are the allocated Tee off times for the applicants, (they just happen to be mainly in time order as I have tested the program). Therefore on the "Entries " sheet Column "D"  is the allocated  tee off slots. Initially before putting the COUNTIF function into the program the default drop down lists on the form were Column A and D..

The duplicate entries in Column A and D have come about as I am given to understand that you cannot use the COUNTIF function on the original column that holds the data that it is working on and excluding the info within that list.

The COUNTIF functions start at row 10 as that is the first row on the Entries sheet after the headers that hold the relevenat Data that is being checked, and I dont know how not to display blanks.

You mention using VBA if I knew how to program that I would use it but once again I dont know how I would program it.

Thanks for your input. 
   
Jim W (rep: 14) Feb 14, '19 at 4:49 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login