I am wanting to have a schedule posted as 8:00am to 5:00pm to show up in a format that shows the interval every thirty minutes with either a 1 or a zero so that I can calculate each intervals total staffing. Also if a break or lunch is entered I would like it to show a zero or .5 in the field (.5 being for 15 mins and 0 for anything taking the full 30 mins).
View 3 RepliesI have 2 workbooks and I must list the differences between them. Here is an example of what I need to do -
Workbook 1
Column1Column2DUE0.044433NHF0.068691CBA0.059029RMD0.051539CSL0.047868WOW0.047744
Workbook 2
Column1Column2DUE0.044433NHF0.068691CBA8AGI5WOW0.047744
The report to look like this
Column1Column2StatusCBA8modifiedRMD0.051539deletedCSL0.047868deletedAGI5added
Any way to produce a report per row of an excel spreadsheet. For example if my spreadsheet contains the names of 10 people in column one and lots of columns of data per person. Is there an easy way for me to pull all that data to produce a one page report for each person listed with the data within it?
View 14 Replies View RelatedLinking is what I am having trouble with... I can establish the link but when I copy down a cloumn, Excel wants me to establish the link each and every time.
I purchased Link Hacker and it tells me Error 52. Bad file name or number
I have tried the mapped drive as well as the entire directory listing.
Is there anyway to force Excel to perform the formula without having to manually tell the link where to go?
I want to type a formula and it tells itself where to find the data.
If not is there another way to accomplish the reading of the spreadsheets?
I would like to do is develop a stand alone program in Visual Basic ( not in Excel VBA) to update the price file in our Portfolio system automatically using the downloaded Excel format file csv extention file from the BigCharts. But before that, I need to export the *.pri file from our Portfolio system in to Excel which still saves as *.pri extention. Then once it updates, I import the updated *.pri file back in our Portfolio program. I understand that the Excel VBA code can be incorporated in Visual Basic code provided there is an object declaration for Excel file (In this case eventhough the both files are in Excel format, they don't have xls extention). how to use external files and Excel VBA code in Visual Basic?
Below is the code that I currently have in Visual Basic. What I'm trying to accomplish is using the ticker (eg. msft) as a keyword search to look up in the price file. If found , the price of that ticker from the test.csv file will be copied in to the price file which is test.pri. I haven't ran it yet.
Sub UpdatePrice(BigChartPath As String, BigChartName As String, AxysPricePath As String, AxysPriceName As String)
'Below are Excel VBA codes
'Uses the test.csv to look up tickers in test.pri and update the price in it
Dim PriceFile As Workbook, BigChartFile As Workbook
Dim PriceFileSheet As Worksheet, BigChartSheet As Worksheet
Dim MaxRows As Long
Dim PriceFileRow As Long
Dim BigChartRow As Long
Dim BigChartFound As Boolean
Call CheckBookOpen(BigChartPath & BigChartName)
Call CheckBookOpen(AxysPricePath & AxysPriceName)
Set BigChartFile = Workbooks(BigChartName) 'Big Chart website imported CSV file saved as test.csv...............
Attached is the file wherein I am trying to make the scheduling gantt for UAE calendar where the weekends are Friday and Saturday. I am not able to format sundays as workdays some how through my conditions.
View 2 Replies View RelatedI have columns with the dates on the top, and staff listed on the rows to the left of the speadsheet. I must have 4 H's (indicating a base location), and 4 F's (indicating another base) in the column below the date. I want to have a cell, just above the date which will highlight red if there are any less than 4 H's and 4 F's. If there are 4 H's and 4 F's in the column (indicating full staffing levels), then I want the same cell to turn green, indicating that the day is OK. The red highlight would be a quick scan for supervisors to look at and see if we are deficient on staffing, and will prompt us to look for people who can be the missing H or F.
View 3 Replies View RelatedI have a table like the one below:
Day Night
2/26/2007DC
2/27/2007AB
2/28/2007AB
3/1/2007 CD
3/2/2007 CD
3/3/2007 BA
3/4/2007 BA
Each letter represents a 12 hour shift (ex. D team has 36 hours for the week). If I need coverage on 2/27/07 on the day shift, I need to find a team that is working 36 hours for that week and was not scheduled to work the shift before or the shift after. (Example, on 2/27/07 A team needs coverage. The only teams working 36 hours are D and C. C team work the previous shift. So the code would input D in a cell).
Is there a way to set the ontime application to run even when excel is turned off? I'd like to be able to refresh a data table every morning at 6:30, even if excel is not on. Ideally, if even my computer isn't on. The code below works, but only if the sheet is open to the exact worksheet.
Option Explicit
Public RunWhen As Double
Public Const cRunWhat = "The_Sub"
Sub StartTimer()
RunWhen = TimeValue("09:54")
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
End Sub
Sub The_Sub()
'my code here
StartTimer
End Sub
I have a simple macro that I run when I hit "Ctr Q".
How do I ensure that this macro runs every 5 minutes automatically from the first time I hit "Ctr-Q". Currently, it only runs once and then stops.
Here's my macro for your view:
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 08-09-2006 by Scott
'
' Keyboard Shortcut: Ctrl+q
'
Range("B6").Select
ActiveCell.FormulaR1C1 = "NOW()"
Range("B6").Select
ActiveCell.FormulaR1C1 = "=NOW()"
Range("A4").Select
End Sub
I have a scheduling problem that I'm trying to get my head around. Im sure that some kind person can give me some ideas on how to get this started. It may be extremely easy, but I just cant get it off the ground. Basically I have 15 doctors that work for me - 9 full time (38hrs/week) and 6 part time (15+hrs/week) in a general practice. We are open 7 days a week
Open Close
Monday8:3019:00
Tuesday8:3019:00
Wed8:3019:00
Thur8:3021:00
Fri8:3019:00
Sat9:0018:00
Sun9:0018:00
Doctors have 5 days on and then 2 days off.
The below is the proposed daily schedule and the doctor requirements. % of booked patients for the times and % of random patients for the times are included as well as average waiting times.
Booked Random Waiting Doctors
8:30 - 10:00Morning Hours55%45%30min5-7
10:00 - 13:00Increasing80%20%45min7-8
13:00 - 18:00Busy 100%01.5hr8+
18:00 - 19:00Decreasing70%30%1hr6-7
19:00 - 21:00Night 40%60%30min5+
There are 3 types of patient visits
ShortDoubleTriple
%0.810.140.05
Minutes153045
58% of patients choose their doctor and 42% come in off the street and will take the first available doctor.
and finally - doctors cant work for more than 4 hours without a break. brek details are below
HoursBreak
<4hrs0
4 - 5hrs10min
5 - 8hrs2*10 mins - 45min lunch
8+2 * 10min 2*45min lunch
I need to create a timetable for the doctors which optimises their time based on the above details. I also need to design a data sheet for reception which will maximise the number of patients seen. Finally I need to calculate what the maximum number of patients the centre can see on an average day say monday.
If i input a 3 in a1 how do i return data in the 3 rows below:
Attached File : schedule2.xlsx
I'm trying to come up with an Employee Shift Scheduling template. I have 6 employees total with 3 different shifts (day, swing, graveyard). I would like to not do a rotating shift. I'm trying to figure out if a 5X8 shift is better or 4X10 and I would like to come up with a weekly schedule where I can see what shift everyone is working and were I have double coverage.
View 1 Replies View RelatedI have 20 staff members that I need to schedule in half day shifts (AM & PM) across three different locations for the month of July.
I want to make sure there is no overlap/duplication for any one staff in a time slot. Nor do I want any one staff member to work an AM and PM shift in the same day.
Ideally, I would also like to avoid back-to-back shifts (e.g., not allow PM on Monday to be scheduled for an AM on Tuesday).
Lastly, I would like to have the number of shifts be be as equitably distributed across all of the staff members as well.
Any way that I can do this in Excel?
I have a macro that I want to run on an hourly basis, say at 9am, 10am, etc whenever the workbook is open. This needs to be definable as I have several users that will be conducting the same activity throughout the day. In other words I want the macro to run on the hour for user 1, quarter past the hour for user 2, half past for user 3 and quarter to for user 4. Each user does have their own unique macro to run so it should (hopefully) just mean introducing a line of code to each, defining the time to run.
View 4 Replies View RelatedI have an excel spreadsheet that contains customer data exported from Quickbooks. In that file there is a column called CType (Column G) which contains the scheduling cycle for that customer. There are eleven different possible schedules, including 15xYr, 1xMo, 2xMo, EOM (every other month), EOW (every other week), EOW-S / 1xMo-W (every other week between 5/1 and 10/31 and once a month during the rest of the year), EOW-Th (every other week but must be on a Thursday), ETW (every third week), On Call, W, Q.
Although the number of records changes daily, on average there are about 950 rows of data in the spreadsheet.
I need to set up an array (?) for the values in column G starting with G2 through the last non-blank row (all records will have data in column G), and calculate the next scheduled service date based on the schedule type shown in column G and the last service date which will appear in column M of that row. The last thing I need to consider is whether there is a value in Column N, which would represent a hard-entered ‘Next Scheduled Service Date’. If I find a date in that column it needs to over-ride the calculated value. My assumption to this point is that I should use a new column T to hold the value of the calculated schedule date. There are a few other details, but rather than confuse this whole thing further I’ll stop there.
What I’m struggling with is the most efficient means of running this macro. I’ve been looking at many, many threads over the past few weeks, reading
VBA Programming for Dummies (which I apparently am), and reviewing other resources.
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
So I'm having a problem trying to make this scheduling sheet.. What I did was row a would be the employee list then b,c,d and so on are mon-sun Originally in b1 I would put like 9am-5pm and c1 8-4 and so on and in row i I put the total and added a sum function but somehow it wasn't able to calculate. So then I redid the whole thing this time in row b1 i put 9am and c1 5pm and so on and again I put the total and added the function and it still came out wrong..
View 2 Replies View RelatedI need to dispatch 10 technicians across the US starting each Monday. They go to 10 different customers for the whole week. I know the distance between each technician and each customer. What I would like to do is assign each technician to a customer so that the total distance traveled for all the technicians is minimized. I know that if it was only 3 techs and 3 customers (as an example), I could build a table of all the possible combinations (3 factorial or 6 permutations), determine the distance for each permutation and select the minimum. I'm not sure that doing this method with 10 techs is feasible as the number of permutations is 10 factorial or over 3 million possibilities. Anyone see a workable solution?
I've include a spreadsheet with customers, technicians and the distances between them!
I'm trying to create an Employee Scheduler. I want my employees (1 technician per row for a total of 10 rows) in the 1st column then the monthly calendar to the right (1 cell/day). That is what you'll see when you open the worksheet. Then, down below I set up my Data Area where I input my project, start date, end date, technician (where each tech is assigned a unique number), and other data across a row of cells. This could be infinite (or at least up to the very last row).
I modified a free Project Scheduler I got from XL-EasyGantt to become my Employee Scheduler but am having some difficulties getting it to do what I want. The project Scheduler basically works by entering (on the same row) the project (or task), the start date and finish date and then the cells get automatically filled in across those dates. This allows for overlapping of tasks but I want to create an Employee Scheduler so it doesn't allow overlapping or schedule a technician to start a new project before he/she has completed the one their on.
The main function which is in each cell of the monthly calendar is as follows: =IF(OR(AND($G28>=K$4,$G28<L$4),AND($M28>=K$4,K$4>=$G28)),IF(AND($E11="x",$M28>=K$4,$M28<L$4),"x",IF($E11=".",".","..")),"")
My problem: I want to be able to look at the Main Schedule and see when each Technician is scheduled for a job (or multiple jobs) for any given month. I want the function to look in my Data Area and assign the start date and end date of a project and the corresponding technician number and fill in the date cells across from their name in the Schedule above.
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies View Relatedfind the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies View RelatedI am making a schedule and I would like it to take out a 30 min break if the hours worked is over 6 hours.
I have so far
A B
1 11:00 7:30
=24*(B1-A1)
Gives me 8 hours, I would like it to subtract the 30 minutes only ifthe sum is over 6 and not alter the sum if it is under 6.
I am using my spare time to learn how to create macros (for the first time) in Excel and Powerpoint in order to the use the programs more efficiently (since I spend way too much time on the them
I am SOOOO confused as to what the scoop is with VBA vs. VB net vs Visual Studios? Is there an expert that can provide me with some guidance given that this is the first (and probably the only) time I will be learning a programming language for Excel & Ppt
Here are my questions:
1) Which programming language should I invest my time to learn if you were in my situation? This is complicated by the fact that most corporations do are not using Office 2007 and (from my experience) are utilzing Excel 2003
2) If I choose to learn Visual Studio or VBA 2007, will my knowledge of the language enable me to use VBA in Excel 2003?
3) If I create a macro in (for eg.) excel 2007, will it run in excel 2003?
Can I enter any record through excel in Visual Foxpro Database without having source code.
I would like to add some visual aspects to my lottery macro. Like numbers flipping through the range as they appear (and I can control how long they flip through the range) and maybe a little rand () function as the numbers are picked. How would I do this and where would I add these two aspects???
View 6 Replies View Relatedhow I can get to the visual basic toolbar in excel 07. In previous versions there was a visual basic toolbar and it allowed you to add buttons, ect. Where is this in 2007?
View 9 Replies View RelatedIs there a code you can type in to VBA to allow a picture in a spreadsheet to change when a parameter is specified. For example i have a picture of a timber beam i drew in excel. and i wondered if i could set it up so that when the user inputs the length of beam they are using, the diagram alters to show the length specified.
View 2 Replies View RelatedI have a workbook that has a login form. I set it up so that if the user does not log in within 2 minutes a nother form will pop up telling them the workbook will close due to no activity. What I want to do is put a timer on that for that counts down the seconds before the workbook exits and at the sametime give them an opportunity to cancel the action.
way to make anything that ticks of the seconds and allows me to press the cancel button at the same time.
My company "makes widgets". Each "type of widget" is associated in our internal database with a record locator (five alpha characters). Our company website can also use that record locator to pull up a webpage specifically dedicated to that widget. In addition to the webpage itself, the website also creates an expanded URL which contains additional information about that widget.
I'd like my macro to take a list of record locators, open up the company website related to each specific widget, pull back the expanded URL, and do some text management with the URL to populate other parts of the spreadsheet.
For example, if my record locator is "ABCDE", my macro would go to the website, "http://www.widgets.com/ABCDE". The site then redirects to an expanded URL: "http://www.widgets.com/whole-bunch-of-data-about-the-ABCDE-widget". I'd hope to write a macro which pulls back the expanded URL and does some magic with the "whole bunch of data" part. I've been told that the MSHTML library would be useful for a project of this type. I've been able to connect my spreadsheet to that library, but then I'm not sure what to do with it.