I have a spreadsheet with ~350 people tracking 24 dates for classes each. On a seperate tab I have a macro that copies and pastes a pre-defined macro to display the due dates. My question is, can I have a macro run the calculation and put the result in the cell rather than have around 8,400 calculations on a sheet?
View 9 RepliesI have 5 columns with different dates to track when certain items expire per employee. Conditional Formatting is working like a champ but the data is growing.
I'd like a way to 'quick glance' at all my people that are expired, coming up on expired (this month and next).
I was thinking if there was a script to run through all 5 columns, if the date was less than today, throw the 1st,2nd and 5-9 column (in that row) into a new table. That way i can keep my big sheet but have another sheet or table that i can see QUICKLY who all needs to get their stuff up to date.
Count people per their classification between two dates. Spreadsheet attached.
The answer I need to show in column (S) is:
Cell (S4) = 1 (supervisor)
Cell (S24) = 1 (fitter)
Cell (S36) = 3 (rigger)
j906 count.xlsx
I need a formula that will search between dates and time to identify if the person will be present at the conference breakfasts, lunches and dinners.
Therefore the cell needs to look if the current date falls within the arrival and departure date and also if the time within eating times falls outside the arrival and departure times.
I'd like to create a number of classes in a VB.NET DLL and then use them from VBA in Excel 2003+. Is this possible? I wouldn't actually expose any of .NET. These would be simply be classes defined by me (which may wrap elements of .NET but never require VBA to have knowledge of .NET or access to .NET assemblies).
If this is not possible, would it be possible to create a DLL in VB.NET that exports functions and subroutines that can then be linked into VBA by importing them (the same way you would with Win32 API routines)?
I am interested to get some opinions/input on where one might obtain Excel training/classes, etc. Our local college infrequently offers classes and when they do, the class is most always entry level which I have already taken. Online or correspondence would be acceptable.
View 2 Replies View RelatedWhy it seems not possible to declare variables in Class modules like so:
[Code] ....
I am trying to replicate the class example on Chip Pearson's site:
[url]
In particular I am interested in having a class that can be accessed by multiple workbooks.
I can create events in my custom classes. When do I even want to raise an event like this instead of calling the corresponding Sub?
I imagine that I can create an event called OnColorChange or I can create a sub called OnColorChange.
Why would I want to create an event?
A lot of people seem to have been having troubles with the fact that if you try to use controls in a class with the "withevents" keyword, some events (like Enter & Exit) are not included in the tracked events. I have been having some troubles with this myself & have not yet seen any particularly satisfying workarounds to this problem.
So here is an idea I just had, I have not made any attempt yet to implement it & so have no idea how complicated doing so would be, but I thought I would just put it out here & get some feedback on the idea before I spend any real time working on it.
The idea is to create a blank, transparent label that covers the entire form, monitor the Click event of this label, & pass it back to the normal controls on the form as necessary. Does this sound doable, or do you all think it would be merely an exercise in futility? If anyone has any better suggestions for how to work around this lack of events,
This is a double IF() to me. I just can't figure it out.
I download a list of classes taught by a number of teachers. I want to summarize how many of classes taught by each teacher.
I have tried many variations of countif() but can't seem to figure out how to do both.
Example copied in below:
ClassesTeacher Classes Taught Angela Barbara Bob
mathAngela math 2 0 1
englishBarbara english 0 2 0
musicBob music 1 0 1
mathBob
englishBarbara
musicAngela
mathAngela
englishAngela
musicBarbara
mathBob
englishBarbara
musicAngela
mathAngela
englishBarbara
In column G of my excel 2003 worksheet named: Data, I have a list of people's first names. These names were extracted from an external database.
Some of the first names actually contain peoples second names or partner's names.
Example:
John & Jill
James and Sue
Kerrie-Anne
Joanna Lyn
I need a macro to please delete people's second name.
So in the example above I need to keep all names that are joined by: & or and or - (such as John & Jill, James and Sue, Kerrie-Anne).
Also, in the example above I need to delete all names that are joined by a <space> (such as Joanna Lyn).
It runs fine for me, but when others (who are using Excel 2003) try to run it, it won't fire. The second macro entitled "Delete Foothill" runs fine though. "Run Foothill won't fire.
Sub Run_Foothill()
Sheets("Foothill Plaza (Weekly Call)").Unprotect Password:="Foothill"
'
' Run_Foothill Macro
'
'
Sheets("OC").Select
Range("B37:C44").Select
Selection.Copy
Sheets("Foothill Plaza (Weekly Call)").Select
Range("B4").Select
ActiveSheet.Paste
Range("A12").Select
Application.CutCopyMode = False
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlMedium
End With
See attached.PMS 2.xlsx. I have a table with data on PMS sheet and would like to have something like the example on the Example sheet using data from the PMS sheet.
View 2 Replies View RelatedI have a list of people in column A and in B to E there are 4 numbers from 1 to 13. (Each person has 4 different numbers). In the next 24 columns are concatenations of these numbers in every order possible. On a separate sheet I have a list of concatenations of all possible combinations of numers 1 to 13 in groups of 4. e.g. 1234, 1235 etc. I have then counted how many times each of the concatnations from the first sheet appear. What I want to do is put the names of the people whoes concatentaions match the ones on the third sheet.
View 14 Replies View RelatedI am trying to do is create a document that will be available to sevaral people. The document will be edited by each person as needed. The completed document will then be used to order materials and supplies. What I want to hapen is each time the document is edited an alert is sent to the person responsible for the purchasing. I'm sure this is possible I just don't know how to do it.
View 14 Replies View RelatedI have 3 spreadsheets I combined into one,
They are voters for 3 separate occasions. I would like to filter out how many people have voted 2 times or 3. I have sorted the information with 2 columns "LASTNAME" and "FIRSTNAME"
Example:
LASTNAME FIRSTNAME
ABBOTTJANET
ABBOTTSTUART
ABBOTTJANET
ABBOTTSTUART
ABBOTTJANET
ABBOTTSTUART
These first 3 show up 3 times, so I would like to take all the info from row one and two to get all the info for Janet and Stu,
I have a sheet in which it is separated by zone area, name (about 200 names), and email. I am trying to add people to an email based on zone. For instance:
Nevada Billy Billy@gmail.com
California Sal Theemail@gmail.com
New York Jim Jim@gmail.com
Nevada JOe Joe@gmail.com
Arizona Alex Alex@gmail.com
So how can add all the people in X zone to a single outlook email (in this case Billy and Joe)?
I want to create a monthly rota for 8 people to cover monday to friday. Only one person per day and I need to put this in a calendar .
View 2 Replies View RelatedI am trying to find the first and last names of people in a cell. I have a userform to get their first and last names separately. I am trying to run a VBA macro to determine if each cell in a column contains both first and last names, and then put a "Y" in an adjacent column. The Code I have is as follows:
View 5 Replies View RelatedI have an excel workbook which is a result of a sports survey I conducted. It basically shows who likes which sport, what level they're at, preferred time of play, etc, and their email address.
A colleague has created a type of form (using formulas, not VBA) wherein you select your criteria (Sport, Gender, Level etc) and it displays the matching names.
What I'd like is to be able to select, via a check box or similar, 1 or more names from the list of results for that sport, click a button, and their addresses automatically open in a new Outlook mail window for the person to send a mail to those people.
how can you set up a workbook to display different sheets for different users depending upon what password they input to open the workbook? The manager should see all sheets but employees only data entry sheets.
View 9 Replies View RelatedI want to do is to count the number of people under each class, eg. 2300 - primary, 3 ppl, but i gt many many classes in my spreadsheet, do u all have solution for me using VBA
SAMPLE DATA:
2300 - primary
amy
ben
alex
1234 - secondary
Linda
tom
Jerry
Tricia
Mabel
5603 - advanced
Linda
tom
Jerry
Kevin
Calvin
I have a workbook with a sheet for each day of the month where guest information is entered. There is a column for each item. There is a summary sheet that summarizes the number of members, guests etc. One thing I would like to track is the number of people from ech zip code.
I manage an emergency response team which consists of around 50 people, made of 5 different teams and these folks need to take part in emergency exercise to keep their skills up. I'm currently building a training/exercise/ roster database in Excel and would like to add a function that tells me who in each team is due to take part in the next exercise. For info: In the actual workbook, I have names defined on the team members and using vlookup to populate the roster via a drop down list.
To explain the attached example; once the person who is at the top of the list (Name1) has done their exercise, they move to the bottom of the list. Then over time, as the others also take part in execises, Name1 is eventually is back at the top of the list the and it is their turn again (this is quite often known as a Squash Ladder, here in the UK). Is there a way I can vba this process via a button or even better (!) by a drop down list?
In the demographics sheet, I have ages listed from row F2 to F31 with different ages. I would like to get assistance with a formula that calculates the percentage of people within these age ranges:
21-25
26-30
31-35
36-40
41-50
51-59
60+
It should be separate formulas. I'm sure if I'm given the first and last ones that I could do the others myself. Also, if I needed to know the percent of males and females, would i use the same formula?
I am looking to track changes on a "Log" sheet throughout an entire workbook. The following code (found here and given below) will give me:
The cell . The date/time it was changed . What the new content in that cell is
The two points I would like it to include, in order of importance, is: The name of the worksheet that cell exists in. The user that made that change*Bonus points* a hyperlink that'll bring me to the worksheet that was edited.
VB:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim r As Long, OutSht As Worksheet
Application.EnableEvents = False
Set OutSht = Sheets("Log")
[Code] ......
I have a worksheet that has multiple units listed on it. Each unit has a materials used list with quantities. I want to reference this column to another sheet and get a total materials needed list and also reference it to my inventory and subtract that number from my materials on hand. how can I do this?
View 6 Replies View RelatedSo far I was able to find solutions to all of my small problems with excel just by searching here but with this one I need help and ideas how can it be done differently.
We need to track inventory from when its taken, when it was installed and we need to know every month when inventory is counted how much do we have on hand in our truck....
I'd like to make a vba macro in excel which will be just like "Track changes" feature. I'd like to track changed cells and when and who did it. (Date, User, Cell, Last value, New value). It is important to make a report in separate worksheet and automatically fill this worksheet when the user save changes.
View 5 Replies View Related