I have created a UDF which determines what the name of the day is.
Public Function Dagnaam(daydate As Date, Optional Language As String = "Dutch") weeknl = Array("Maandag", "Dinsdag", "Woensdag", "Donderdag", "Vrijdag", "Zaterdag", "Zondag") weeken = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday") dayno = Application.WorksheetFunction.Weekday(daydate, 2) - 1 Select Case Language Case "Dutch" Dagnaam = weeknl(dayno) Case "English" Dagnaam = weeken(dayno) End Select End Function
What i would like is when the user uses this function in the worksheet the field Language holds a dropdown list with predefined languages.
I probably just am just missing a Library but when I type "Selection." I would normally expect Autocomplete to pop up telling me that I am able to choose from Cut, Paste, ect. However its it almost as of my VBA editor does not recognize Selection as a proper object.
I have the following Reference Library's loaded: Visual Basic For Applications Microsoft Excel 11.0 Object Library OLE Automation Microsoft Office 11.0 Object Library Microsoft Outlook 11.0 Object Library
I have code to do what I need to do currently but it only works for one cell. If I try to get it to copy down through a range it loops endlessly back and forth between two values. I am sure that there is an easy way of accomplishing this. I would appreciate anyones help or suggstions. Thanks.
I am designing (badly!) an excel file that is intended to be as user friendly as possible. I would like to add in a navigation feature using a list box or other appropriate control form that will take the user to the correct tab in the workbook depending on which item they choose from the list.
My aim is too hide all the tabs at the bottom whilst not crowding my page with multiple command buttons, and still be able to navigate easily through the workbook.
How can I add multiple cells (ranges) to a listbox, and when the user clicks one, it will activate that cell in excell. I thought about have the list box for the text, and a corresponding array that holds the actual range or address, but I cant get it working.
Long version: I have a search feature that, when you type a name, it searches through a column and finds the name. Now if there are multiple matches to your string, I want it to add the full name to a listbox, and have the user pick one from the list to activate.
So basically, if I search for the name "john" but theres multiple people with that name ("john doe", "john smith", & "john mazz"), it would add the three to a list box, I would pick one from the list, and it would activate that cell.
I have everything done except that last part. I have it adding multiple matches to the list box, but I cant figure out an easy way to activate the correct cell when double clicking the item in the list. I could have it search again for the selected full name, but there is likely to be a case where one person is entered twice.
I have a Validation drop down box (column B) and a defined range drop down box (column E linked to the Totals tab with A2-A31 defined as Products) that I need to activate everytime data is added to a new line in column 'A' and cells in column 'B' or 'E' are selected. Additionally, they need to activate if the cells in column 'B' or 'E' are selected for any of the old data so changes can be made. This will help reduce the overall file size and calculation time for a year's worth of data.
There is already some code on the worksheet that looks like it can be adapted if someone knows how to code in the drop down boxes.
my sumproduct function is adding up all items that fall on that date which is fine, but i would love to calculate an average so divide by the number of items that were used in the calculation, is that possible?
I've written a program that list all the occurences of a given name in a specific cell from all worksheets in the active workbook. The list is placed in a listbox and the first listing is selected with ListBox1.Selected(0) = True. Additionally the worksheet of the first listing is activated.
My question is now how do I either scroll through each name in the list box and activate the "current" worksheet. I assume that I'll need to address scrolling up/dowm and clicking of any particular name on the list.
I have the following worksheet, which lists the pickup groups and members of each group from a phone system. Another worksheet lists all the exts and I want to have some sort of VLOOKUP, MATCH function that will look at the ext number and come back with the pickup group number.
I've attached a copy of the spreadsheet to show you what I'm talking about. So looking at the spreadsheet if I have an ext number of 8001 I want to return the value of 2 as ext 8001 is a member of pickup group 2. Also of an ext is not a member of any group, then I want to return nothing.
I have roster that has 12 teams on it, each team has their own column with the supervisors name in the first row. I want to look up the supervisor and count the number of team members listed under that supervisor.
I use Excel to query a SSAS OLAP Cube, the return of which I then model into various outputs. I do not have access to amend the cube.
I am hoping it is possible to be able to add custom calculated members to some dimensions in excel rather than on the server, but I'm struggling to find any resources that explains how it is done or if it is even possible. For example, the cube will return the dimension 'Source Country' with members 'England', 'Northern Ireland', 'Scotland', 'Wales', I would like to add a calculated member 'United Kingdom' to consolidate all those members.
Is this possible or should I just look to calculate this consolidation by formulas in the model.
I have Excel 2003 installed on the companies computers. Many people use an Excel file and I am trying to make their work easier. There is on command which has a dropdown list made by me. What I would like to find out is that is there any possibility to make Excel help the user to choose from the drop-down list (the list is quite big). Is it possible to select a cell in this column and type in the cell and then Excel help the user. I mean (this list contains country names in abc order) if the user types S and W then Excel auto-corrects it to SWEDEN. Can this be done with Excel 2003?
I have a Master Data sheet for the input of personnel information. This information is then used in different Data Validation formats to be referenced by other sheets. Column P, I use the Concatenate formula to put together a needed grouping (Last Name, Shift/Kelly) taken from the Master Data sheet. I would like Column Q to then alphabetize Column P by the last name and also drop the blank cells. I would like this to be an automatic function, so as personnel are added or deleted, the alphabetical list will be updated as the information is changed.
I hope I've explained it well enough. I've attached a sample sheet to help make it easier to understand. I would prefer to do this via a forumla and not a macro, but if the only option is to use a macro, then so be it. Is this at all possible?
I have an event that is one week long (7 days), with three functions happening each day (7 days - 3 columns per day).
There are 11 groups with various number of possible attendees listed in rows. Attendees of a given function is indicated by entry of a 1 or 2 (attending partner), Blank = not attending. I would like to show a list of the attendees for a given function by clicking or moving my mouse pointer on the event function column header.
The list would need to be automatically updated when the attendees status changes (entry or deletion of 1 or 2)
I am having difficulty with this formula. In the attached spreadsheet. I want to just input the item number in column A, manually. From there I want it to take the information from the hidden list and fill in the rest of the fields. Example. I input in A4-2664, I want it to search the list for 2664 and take the corrisponding information. It would take the information from M5, O5, and Q5 and place that in C4, E4, G4. I place spaces in the hidden list to make the format the same.
Is it possible to autosort a drop-down list based on the number of times an item is selected? A drop-down might have 500 lines so it is user-friendly if the top lines are those selected by users the most often.
i have a list called "parts". i want this list to be updated everytime a new part # is entered into the list cell. i can get it to work in one cell. i have been able to get it to work in up to 6 rows, however i want it to work in up to 50 rows. here is the vba code i am using
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long
If Target.Cells.Count > 1 Then Exit Sub If Target.Address >= "$D$3" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("parts"), Target) = 0 Then lReply = MsgBox("Add " & Target & " to list", vbYesNo + vbQuestion) If lReply = vbYes Then Range("parts").Cells(Range("parts").Rows.Count + 1, 1) = Target End If End If End If End Sub
i am using insert>name>define for the list and this is what i used for that
like i said, it will work for d1-d6, then it stops working.
is there a way to take a drop down list and pick 1 then have excel fill in the info i seven cell example if list box is 3 then cell 1-7 = 1 and is list box is 1 cell 1=1 cell 2-0 cell 3 = 0 cell 4-7 = 1
I have a budget document that has a selection (Named Range with list items in a drop down) Approved, Rejected, Awaiting Approval or Blank. What I would like to do is set a process that could approve all Awaiting Approval. My thought was to create another choice box at the top that allowed the user to choose approve all awaiting and it would select every matching item that was Awaiting Approval and change it to Approved. At the same time I would like it to set the date and time, this could just copy what I put in an associated field or be real time.