I have the formula but it will always change when i shift my cell down
=MAX($G$21:G35)
When I shift my cell G21 down, it will change to below
=MAX($G$22:G35)
The problem is that I do not want the formula to change. How can I do it ?
In Cell A1 I have the date. In Cell B1 is the shift. There are three shifts labeled as 1, 2, and 3. Is it possible to create a rule that will convert 1 to 0700, 2 to 1500, and 3 to 2300? Below is an example of how I need the shifts converted to time (military).
[Code].....
Using frequency in a data validation formula for ensuring unique entries.
Currently I use COUNTIF($B$2:$B$244,B2)
I have the basics set up, but need to work out how to make it calculate my pay per shift dependant on the type of shift i have worked.
I have attached a screen shot of the current page,
In it i have currently used validation drop boxes for the location and worked columns with tables just to one side of the sheet.
The shift pay is the column i am having trouble with.
I would like it to change dependant on what is selected in the 'worked' column.
For most things it should just display basic plus holiday, however if supervisor is selcted in the work column, it should display basic plus holiday plus supervisor.
a person works for certain hours and get paid according to the hours worked either by day or by night or a mix of both. Day payment is $8 when worked between 08:00 and 19:59 , night payment is $12 when worked between 20:00 and 07:59. The excel cell are formatted as datetime with yyyy-mm-dd hh:mm , the function works fine in getting the time information and checking whether the whole work is all day or all night , yet the if-then-else statements for calculation seems to be wrong!!
examples:
start = 2008-01-01 09:15 , end = 2008-01-01 11:40 , all day as it is between 08:00 and 20:00 and cost = 8/hr = 19.333
start = 2008-01-03 21:05 , end = 2008-01-04 02:05 , all night as it is between 20:00 and 08:00 and cost = 12/hr = 60.000
start = 2008-02-02 19:00 , end = 2008-02-02 20:05 , cost = 9.000 as 1 hour day = 8.000 plus 5minutes night = 1.000
Function prod(st As Date, en As Date) As Double
Dim shour As Integer
Dim smin As Integer
Dim ehour As Integer
Dim emin As Integer
Dim stod As String
Dim etod As String
pday = 8
pnight = 12
shour = Hour(st)
smin = Minute(st) + shour * 60
If (shour >= 8 & shour < 20) Then
stod = "day"
Else
stod = "night"
End If
ehour = Hour(en)
emin = Minute(en) + ehour * 60
If (ehour >= 8 & ehour < 20) Then.................
I enter for example in a cell : 0625-C0/01
sometime 0 is entered as O a letter and not zero.Is there a way to ensure that only 0 (zero) is allowed in that string using data validation rules.
my formula is in B1. If in A1, there is letter Y, the value in B1 should accept only 3-digit numbers.
View 2 Replies View RelatedI am trying to set up a data validation to ensure that when a user enters a number in column A that matches a previously entered number in column A, the user is only allowed to enter the same date for the number it matches in column B.
For example, if:
Row 3 column A = 1320
and
Row 3 column B = 16-Dec-07
and
Row 15 column A = 1320
then:
Row 15 column B must be 16-Dec-07
I have a range B1:B20. These cells are populated by the user and I want to ensure that no two entries are the same. Is there a way of presenting a dialog box that prompts the user to enter a different value if the value they are trying to enter is already entered somewhere in the range?
View 5 Replies View RelatedI need a formula that will be able to show what shift someone is on if the shift changes, for example. if one of my employees shift changes from and 8hr to a 10 hr on january 16th 2014 then I would like the raw sheet to show the old shift, the new shift and when the new shift started (date). the index formula I have will only show one shift even if there are new dates with a different shift. the formula pulls from the sheet name editor.
i really want the shifts to be different if it was change on a certain date then from that date the employee shift will reflect new shift but still keep the previous shift prior to the day the new shift started.
I've attached a sample workbook to show.
MDT Report December 201322(FILEminimizer).xlsx
Excel 2007.I have a list of postcodes (UK) which have different lengths of characters (including spaces) from 6-8, however our system seems to add additional spaces inbetween the postcode, so it could have upto 11/12 characters (inc spaces) Below is what could come out:
EH21 6PQ - 1 Space (8 Char)
EH12 9HG - 3 Spaces (10 Char)
E1 8DF - 3 Spaces (8 Char)
LL5 1GH - 2 Spaces (8 Char)
L5 1FG - 1 Space (6 Char)
What I need is a formula to ensure each postcode only has 8 characters by inserting spaces between if there's less than 8 char and trimming if there's more than 8 char
So from the above postcodes the desired results would be:
EH21 6PQ - 1 Space (8 Char) - This would be correct
EH12 9HG - 3 Spaces (10 Char) - Trim off 2 spaces from the middle
E1 8DF - 3 Spaces (8 Char) - This would be correct
LL5 1GH - 2 Spaces (8 Char) - This would be correct
L5 1FG - 1 Space (6 Char) - Insert 2 spaces in the middle
VBA automation -
Excel added the formula - but they all return "Not Found". After the VBA automation - I visit each cell, see the formula is correct and then press Control +Shift + Enter; then the correct value displays! Of course, the curly brackets also appear in the formula bar. In VBA I tried to use the:
objXL.ActiveCell.FormulaArray = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....
The Excel Formula bar for the cell is just empty (blank)
Using only the Formula property:
objXL.ActiveCell.Formula = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....
The correct formula is created in each cell, but. Until I visit that cell and use the Control+Shift+Enter - the lookup will not work. It is proof that the right formula is there, without the curly brakcets.
In Excel - my vba code successfully constructs these formulas: In essence: It checks for an error and prints "Not Found" if no match is found in the check. It test for two values in a row - matches them to two columns on a row in another worksheet, and returns a third value for the matches of the same row.
I have below formula.i need to change the cell address AK in the below formula on the value change in the A3.
=SUMIF(Working!$D$6:$D$3000,Report!B3,Working!$AK$6:$AK$3000)
For example if A3 is having value AZ i need the formula changed automatically as below
=SUMIF(Working!$D$6:$D$3000,Report!B3,Working!$AZ$6:$AZ$3000)
This formula works great and does everything I need it to do, BUT unfortunately I have since discovered that it is not compatible with several end applications that I am using with my spreadsheet.
Can somebody please suggest an alternative to the array formulae, as written in cells: O6, Q6, S6 & U6, while still giving me the same end result?
I want to edit a cell's value without changing the formula the cell contains.
View 10 Replies View RelatedHow can I shift the range of cells I want to calculate an average from? For example I want to get the average of cells A1 to A10, so =AVERAGE(A1:A10) And next I want to get the average of cells A11 to A20, so =AVERAGE(A11:A20). But I don't want to manually change the data or delete rows all the time.Is it possible to do something like: =average(A1+10:A10+10)??
View 4 Replies View RelatedThe below code is what I have and it works but what I need is for it to loop until it doesn't delete anything. How can this be done?
View 2 Replies View RelatedI've attached a simple spread which gets across the main problem. i need the totals row (currently row 7) to drop down a cell each time the data base above has an extra row added to it its pretty much a cosmetic issue but it can be useful on hugely long spreads.
View 13 Replies View RelatedI want vba code to do the changes
Search for "CAP_ACTV_LN_SEQ" keyword in column A of Sheet1 and when found go two rows down and do the following
Delete cell D128 and E128 ( if more than 1 rows then do the same for all rows till "selected" keyword detected below it eg C131 in this case ) and Shift cells in the right to two column left as shown in figure below -
Before
2.jpg
After
3.jpg
Sheet attached
Data.xlsm
This can happen by click of button in the sheet
I need a macro which will manipulate my data like this:
If a blank cell is found in column C, then I'd like to have the contents of that row in column D shifted to the right.
In the range A1:Y66, for every cell whose formula returns a 0, I want it to delete that cell and shift the cells up.
Here is the code I tried, but nothing happened:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column > 25 Then Exit Sub
If Target.Rows > 66 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = 0 Then Target.Delete Shift:=xlUp
End Sub
I need a macro that will "examine" the text of column B for every row checking to see if the text "Proper Text" is in the cell and if not, execute this code for that cell and then move on to perfrom the same until it finds an empty cell:
Selection.Insert Shift:=xlToRight
Range("B2085").Select
ActiveCell.FormulaR1C1 = "Click for detail image"
With ActiveCell.Characters(Start:=1, Length:=22).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
Note that "B2085" above is the cell number that the script stops on to execute this code.
Is it possible to use a formula to shade a cell dependent on a condition? I have tried an if formula (see below) but it is incorrect. =if('November 2006 SVOC'!B6>'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [red],if('November 2006 SVOC'!B6<'March 2006 SVOC'!C6,'November 2006 SVOC'!B6 [blue],))
View 3 Replies View RelatedI have a Start Shift and an End Shift time,
Start Shift = 2009/11/10 09:27:06 (GMT-6:0)
End Shift Time= 2009/11/10 15:13:03 (GMT-6:0)
eg. Total Time = 5.3 hrs
I would like to take if from this format, and calculate the total time difference. Sometimes the GMT codes may be -5:0 if that means anything. For the cell "Total Time" I only need it to have a decimal format.
If I use =now() in a cell it will enter the current date and time. For shift workers, this does not work.
I'm tring to figure out a formula that will only change the date at 6:00am every day. This way a person working the night shift will not see a change in the date on his sheet at all, only the day worker.
I have a cell value. For example. A1= 1-2010. Is there a formula that will make A2,A3,A4 become 2-2010,3-2010,4-2010. If A1 changes to 2-2012, A2,A3, and A4 will be supposed to change to 3-2012,4-2012,5-2012.
View 7 Replies View RelatedI want to change a cell value to a calculation based on if "X" value input to a cell.
In attached file explained all detail.
Cell_Change_to_formula.xlsx
it sounds simple but, all i need to do is change a cell value dependant on another cell value
i need say A1 with the formula
i need C1 to be say "Yes"
i need a formula to show "It Works" in D1 if C1 says "Yes"
But the formula for this needs to be in A1
I need to set a cell colour by the value of a cell in another sheet so i can't use conditional format. Is there a formula that allows the user to set a cell style, colour, font etc.
View 9 Replies View Related=CHOOSE(WEEKDAY("1 Jan " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
where C1 = the year 2006. B1 has the month Jan and I want to input that into my formula, so when I change the month, it automatically will change my formula. I tried =CHOOSE(WEEKDAY("1 $B$1 " &$C$1),"Sun","Mon","Tue","Wed","Thu","Fri","Sat")
B1 = Jan
C1 = 2006