I have a workseet that is basically a massive list of orders that have been placed. I have some conditional formatting based on cells that use the today() function to work out how long (in days) since the order was placed.
Trouble is, the spreadsheet is always open, and is never closed down... so it does not refresh automatically. So orders that should go red to indicate a week since they've been placed, don't!
I don't want to rely on people refreshing the screen. Ideally, I would like it to refresh once a day at 1 minute past midnight, or even every hour...
I have created a daily schedule which has a number of factory variables taken into consideration which determine the date and time a particular product should, barring any mechanical problems, come off the machine. (see attached spreadsheet).
The date at the top will be editable by me only so that when I update the production quantities, the “date/time off” column automatically re-adjusts to the remaining quantities.
The formulas are a little long winded, but I have left them that way whilst I try and develop it. I should be able to figure out how to condense them later.
My problem is that the “date/time off” on the right works excellent, but over a 24 hr period.
Ordinarily, we work a 12 hour day (6am to 6pm) with overlapping shifts to cover breaks, and 20 mins warm up at the start of the day for the machine, thus maximising a 12 hour day.
Of course if demand exceeds the allotted time we put on overtime.
Is it possible to specify that normal days are only 12 hours so that if a product exceeds 6pm, it flows into the next day with the balance starting at 6:20am?
And, if the production for the week exceeds the time could I stipulate particular days which we deem are suitable for overtime? Ie, we decide Wednesday is a 14 hour day and not 12.
I had toyed with the idea of creating a 365 day table/calendar, on another worksheet which would have its individual allocated hours in an adjacent column and somehow link them to the date/time off, perhaps by way of a VLOOKUP, but I have been chasing my tail trying to figure out how to implement it.
I work in a computer lab and we have to keep track of how many people sign in (using excel 2010), and how long they were here.
I'm looking for a way to count how many entries were made per hour for every hour someone was signed in.
For example someone signs in at 9:22 am and leaves at 3:34 pm.They were here from 9-10, 10-11, 11-12 etc.. and its getting very tiresome to go through and manually enter a "1" for each individual hour in each cell under the hour.
Is there a way I can feed the in and out times into a spreadsheet and have it automatically count how many people were in the lab each hour?
I have a time table which some hours are 3 hours time period. I want to distribute to new table hour by hour this kind of hours, or hour to same hour period.
I have protected a few hidden columnx in my worksheet and have sent it to my client who has the password.
He had a complain. Every time he wants to unhide the columns, he gets an alert to unprotect the workbook. The process of going to the Review tab and pressing the unprotect tab is what he finds annoying. My questions is the following; Is there any way that the password box immediately appears once he attempts to unhide the columns?
I have a problem with excel, let say i have excel file (named A) and i also have another excel file (named B), file "A" is database, and consist of around 14 columns. so when in B, i type 1234 then in B i will get all the data that related to 1234 that consist of 14 fields and it will be placed in different place and different order.
I would like to be able to use the before save event to hide some sheets before the save then after unhide some sheets. So that the user carrys on with the sheets they had before saving but when the document is reopened the correct sheets are hidden.
This is what I have so far but unfortunately when you click close and then save changes it runs the before save code and then goes around in circles, reasking the user if they want to save changes
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim ws As Worksheet
Cancel = True Application.EnableEvents = False Sheets("Protected Content").Visible = True For Each ws In Worksheets If ws. Name <> "Protected Content" Then ws.Visible = False Next ws Me.Save For Each ws In Worksheets ws.Visible = True Next ws Sheets("Protected Content").Visible = False Application.EnableEvents = True
When I call a series of subroutines from different worksheets, ScreenUpdating = False is not working.Here is my
Private Sub Worksheet_Change(ByVal Target As Range) With Application .Calculation = xlManual .EnableEvents = False End With If ActiveCell = "No" Then Call Sheet3.DisableTBs Call Sheet4.DisableTBs Call Sheet5.DisableTBs Call Sheet6.DisableTBs Call Sheet8.DisableTBs Call Sheet9.DisableTBs Call Sheet10.DisableTBs......................
does excel have a function or some kind of method where you can have it display the range of cells that you have selected? For example, if I highlight cells A5:G7 what would be the code for excel to display "A5:G7" in a message box? I need this to be dynamic, so the next time I select cells B3:T32, it needs to display "B3:T32."
I have some data validation drop down lists in excel, I can tab through all the lists but I have to press alt + down arrow to show the list, Is their a way it can be automatically shown as soon as I focus on the tab.
how to make a date automatically show up in the current year.
For example, if A1 = 6/2/2005, and I want B1 to equal that same month and day, but 2011... How can I do that?
I know =6/2/2005 + 365*6 would work, but the date in A1 will vary... so it has to be a way of showing ANY date with ANY year with the same month/day but current year.
I need a macro where user choose a value from validation, automatically lock the other cells and should show an error message.
Here is what I am looking for. User will choose Yes or no from cell H15 validation. If user choose "NO" from the validation then all other cells has to be locked in the workbook automatically except Cell H15 and a msg box needs to appear stating: "Select Yes from the validation".
I have a spreadsheet with a master front page, which basically copies the contents of other cells in other sheets for it's own content. ie:-
the formula in cell E16 on the master sheet is ='Sheet 7'!$B$16 (Please note that cell B16 is actually a merged range of B16 to H16)
Everything works fine until such time as the orginal cell contains an amount of text which is deeper than the cell on the master page eg cell B16 on sheet 7 is 330 pixels deep while the cell E16 in the master sheet is only 16 pixels deep.
What I would like to happen is for row 16 in the master sheet to expand in depth by whatever is requried to automatically show the full content of B16 on sheet 7.
I want to make Excel show page breaks automatically when I open a new file. Please tell me how to do that. I tried changing the book.xlt and sheet.xlt files, but to no avail. I am using Excel 2003.
I wrote a small code to hide some columns if a certain cell is equal to a certain string.
The cell is actually a drop down list and when they select a certain one, I want it to hide 2 columns. So I wrote the code with sub name Action, but I want it to be running all the time. I tried to achieve this by writing the following code however it gave me error 438 for my 2nd line.
Sub Auto_Open() Range("A1").OnEntry = "Action" End Sub
Auto Merged Post Until 24 Hrs Passes;Oh, by the way error 438 states: Object doesn't support this property or method
I am looking to adapt a piece of code (originally created by Ger Plante) so that it autofilters multiple columns of a table. I have three data validated lists that need to search 3 different columns in the table and filter accordingly, but also show all if no hits are made (hence why Ger Plante's code) was perfect in most respects. I would ideally like to keep the code as a Worksheet_Change event, but can deal with it being run as a normal Macro via a button if this is necessary.
[URL]....
VB: Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("B1")) Is Nothing Then Range("A5:C5").AutoFilter Field:=1, Criteria1:=IIf(Trim(Range("B1").Text) = "", "<>", "=") & Range("B1").Text End If End Sub
What i would like to do is Hide Rows Based on Cell Value in Multiple Sheets & Multiple Columns and i need the macro to be fast
Sheet1 If the value in Range BE11:BE160 equals 1 don’t hide the row If the value is “0” or “ “ then hide the row Sheet2 If the value in Range BE11:BE160 equals 1 don’t hide the row if the value is “0” or “ “ then hide the row Sheet3 If the value in Range BE11:BE160 equals 1 don’t hide the row if the value is “0” or “ “ then hide the row Sheet4 If the value in Range O1:O150 equals 1 don’t hide the row if the value is “0” or “ “ then hide the row Sheet4 If the value in Range B1:B150 equals 1 don’t hide the row if the value is “0” or “ “ then hide the row
I own a Hair Salon which collates its daily takings using a spreadsheet. A section of this takings sheet asks staff to enter which Products have been sold and what they where sold for (RRP), so that once all products have been entered, totals at the end of the day allowing the end -of-day "till-up" process to reconcile purchases and services rendered aginst cash and cheques received.
Unfortunately there is over 600 products to choose from and this evergroes as new products are introduced by manufacturers. Names are long and often very similar, and product price manual listings are slow to reference, therefore the customer has to wait while the staff find the right product and select the right price.
All of this is subject to human error. What i would like is simply to enter part of the product name into a Cell and the right product to be visibly selectable and the price automatically added to an adjacent cell.
I have a form to round to nearest quarter but if it is less than 1 hour I need it to round to a total of 1. Can this be combined in one formula.
I also need my time to be configured so that if the start time is a PM number then end time AM it does not figure right. is there a way to remove the AM/PM from time. I have already tried all the formats from number,time, & custom.
I have a list of FLIGHT departure times that are listed in MIL TIME, however, there is no : in the format. Its just 4 or 3 digit numbers. I need to convert these to time in 12-hour clock. If I go to FORMAT/CELL/TIME and select 1:30pm it simply makes the time ZERO!
I have a code that I have put in the ThisWorkbook section, and I thought it should work - but nothing happens....
Code: Sub FirstSub() Application.OnTime TimeValue(Sheets("SetUp").Range("G1").Text), "SecondSub" ResetTime = Now() + 1 Application.OnTime ResetTime, "FirstSub" End Sub Sub SecondSub() If Weekday(Now()) > 2 And Weekday(Now()) < 7 Then Call CreatePDF End Sub
