How To Conditionally Populate Dates
Jan 4, 2012
I do have a cash-sale & cash-deposit template as follows.
The cash from the sales proceed needs to be deposited to bank on the next working day but in reality the same is not happening.
The stores are depositing the cash for more than one days sale and then the same is getting submitted to bank.
I would like to populate the Dates(in the "Desired Result" column from D2:D11).
The desired formula needs to check the non-blank cell in "C" column and then fill in the dates in the "D" column.
For example the sales amount from 1st-3rd December is deposited on 3rd December and hence the desired result across D4:D2 needs to be 3rd December.
Similarly the sales amount from 4th-7th December is deposited on 7th December and hence the desired result across D8:D5 needs to be 7th December.
ABCD1Date Of SaleSale AmountDeposit AmountDesired Result212/1/201157335 12/3/2011312/2/201128879 12/3/2011412/3/20112328411883312/3/2011512/4/20116294 12/7/2011612/5/20116894 12/7/2011712/6/201138270 12/7/2011812/7/2011262787474212/7/2011912/8/201133308 12/10/20111012/9/20119279 12/10/20111112/10/201166956886512/10/201112Total236516262440
Spreadsheet FormulasCellFormulaB12=SUM(B2:B11)C12=SUM(C2:C11)
View 2 Replies
ADVERTISEMENT
Dec 12, 2006
I have a workbook (attached) which has two sheets called “Team List” & “Masterfile Data Dump”.
TEAM LIST
This is the sheet that I want to populate when the data dump is a pasted into the sheet “Masterfile Data Dump”
MASTERFILE DATA DUMP
This sheet is where my data dump will be pasted to, columns A to Q and column R is a formula to work out the FTE
Calculation
The reason I want the list to auto populate is that it will change week on week and is a time consuming task keep updating it. I want to be able to type a Team Leaders Name into cell F2 and it populate the list of the agents in that persons team Cells F4:G18 (Name & Surname) along with there FTE (full time equivalent) in Cells I4:I18.
The problem I have is I have no idea how to calculate the FTE sum as the data dump can have one to five shift patterns (rows) for one agent (See my example on the “Master file Data Dump” Sheet of Joe Bloggs. /I have tried to use a VLOOKUP which I have used but this will only return the first value or first rows value of that agents name.
View 4 Replies
View Related
Jun 11, 2014
I have spent too many hours Googling and trial with mostly error in attempting this. I formatted the very first cell A1 for today's date =TODAY(); this is also dirrectly above the column with all the dates in it. I just want an entire row's contents to turn red if it is 15 days old or older. This =$A1<TODAY()-15 somewhat works, but it only turns the date cell red, not the whole row. It also makes blank cells turns red, which I would like to remain blank (another formula is needed I imagine).
View 3 Replies
View Related
Mar 28, 2009
I've asked this similar question on another message board (the Microsoft help community), but failed to follow-up quickly enough for the support I've come here to ask for. My situation involves a spreadsheet that is used to track days worked over the course of a week. I have the days of the week listed in one row (Sun - Sat) and in the row above, a place to put the day (i.e. - 3/28). I asked for help with a macro that would allow the user to place the date into any of the cells above the days of the week, check to ensure that the corresponding day of the week is correct, and if so, populate the other remaining empty dates automatically. Below is the macro that was kindly provided. It works perfectly as long as my data is in the referenced ranges (A1:G1, etc); however, my actual data is in range L6:R6 for the dates and L7:R7 for the day of the week. (example below). I've been able to pick apart the code to understand how to change most of the macro to accept input from the different range; however, I'm unable to comprehend what I'm missing to complete the modification.
View 6 Replies
View Related
Aug 6, 2008
I am trying to find a formula that will automatically populate two columns on the same worksheet that a date is being entered in a different column. We train 468 new bus drivers every year and to accomplish this, we run 26 classes of 18 students with a new class starting every two weeks at our training facility. Each class is in training for six weeks (30 working days). The first 19 days they are under the instruction of our full-time instructors. The next nine days they are out on ‘line instruction’ with regular drivers. On days 29 and 30 they are back in the training department for final evaluation and graduation. On Day 31 they are turned over to Service Delivery as regular full-time drivers.
If I enter a class start date of April 6, 2009 in cell B4 on "2009 Conventional Classes", the formula would automatically fill in D4 and E4 columns with dates that are 29 days later and 31 days later respectively but, would skip weekends and any statutory holidays (these would be represented by a different background color on the calendar templates).
View 2 Replies
View Related
Jun 11, 2014
I have a total summary page, which sums up a bunch of data for around 2 weeks. Is there any way to have the corresponding dates appear on the summary?
Ive attached a example spreadsheet, showing a basic total summary, and the quantity, the highlighted red section is what I would like to appear if possible ...
View 2 Replies
View Related
Jul 3, 2009
How do i get cells to automaticly populate dates in sequence based off of one cell. I.E C1= 4/Jul/09 I want A2-A7 to populate the dates in sequence Jul 4 - Jul 11. Is it a formate issue?
View 4 Replies
View Related
Oct 8, 2006
I'm need to add a droplist with periods 1,2,3,4 etc... that references a cell then populates that same reference in a cell raange. Example: cell E1 contains the droplist with periods 1-4 as choices to select from, when you select period 1 it populates the dates jan 15 in cells E4:E7, if you select period 2 it populates dates jan 31, etc. how to add the droplist, how to expand the list, show the formula and how to expand it as I will have more data than this short example.
View 3 Replies
View Related
Dec 20, 2013
I want to display the percentage of occupancy for each month for condo rentals.
I have columns with dates that represent bookings throughout the year. check in and check out dates to be more precise. (A1 and B1 for example) and I have columns with months Jan, Feb, etc. (C1, D1, etc. for example)
The problem I run in to is that I can calculate the number of days between the dates and the fraction of the year (*12 to get a monthly value, but I fail to understand how I can break that up so that I can populate each month with a correct percentage of the total term.
E.g. A1=1-jan-2014 and B1=20-jan-2014 is an easy one because it only covers 1 month:
formula: =(YEARFRAC(A1,B1,3))*12 gives me 62,47% which I could just use as is for that month.
So far so good, but when the period covers more than one month or when the months overlaps, I don't know how to break the outcome up into the appropriate months to display the correct percentage for each month in its own column.
e.g. A1=1-jan-2014 and B1=20-Mar-2014 gives me a result of 253% with the same formula.
100% for November, 100% for December and 63% for January, which I could somehow formulate to break up over different Months.
But then it gets more tricky. What if the booking starts on a day other than the 1st of the Month?
e.g. A1=5-jan-2014 and B1=20-Mar-2014
The result of the formula (243%) is correct, but isn't sufficient to put the correct percentages for each month in their respective column.
What formula(s) should I use to break down the percentages to match the correct fraction of each individual month?
I attached an example of what I have so far for your review : test.xlsx‎
View 8 Replies
View Related
Mar 26, 2014
I've just started working on an FTE calculator and wish to populate a 12 month calendar with FTE depending on the start and finshed dates.
FTE Calculator non nursing.xlsx
I've attached the file. In Column D the user would select the month the staff start and in the Column E the month the staff will finish. I would like the fte that is calculated in Column Z then to populate in the 12 columns AB:AM (Jul to Jun) with corresponding month start and finish.
View 2 Replies
View Related
May 8, 2009
how to make a certain type of date automate. It's kind of hard to explain, but basically, I'd like to make it so that when I enter a date in one column, another column will automatically populate with the 1st of the next month. For example:
If I enter 4/26/2009 in the 1st column, column 2 will read: 5/1/2009
If I enter 1/19/2008 .................................................. 2/1/2008
Also, it's very important that if the FIRST date is already the first of the month, then the second column will read the same. For instance: If I enter 3/1/2009 in the first colum, the second column will ALSO read 3/1/2009.
View 3 Replies
View Related
May 30, 2013
I'm using Excel 2010. I need to populate a daily calendar with the number of nights spent, extracted from the Date of Arrival and Date of Departure of individuals.
View 2 Replies
View Related
Jul 31, 2013
I am trying to auto generate a calendar based on two drop down menus - Month and Year.
Once the month and year is selected I want to import all work orders onto the calendar based first on the "Labor Name" found in the list of work tab, then assign each work order for that labor name to the respective date on the calendar for the month.
August PM Schedule Demo.xlsx
View 2 Replies
View Related
Aug 11, 2009
i have two fields with dates - one field A1 for date authorised (for a data request) and one field B1 for date actioned (data request)
i need a formula to populate in C1 the following:
if A1 is blank then C1 is 'not actioned'
if B1 is blank then C1 is 'not complete'
if both contain dates then C1 to calculate the number of working days between the dates eg. A1 10.08.09, B1 11.08.09...C1 = 1 working day
View 3 Replies
View Related
Nov 29, 2012
I have a long row of data with words/numbers in each cell. Whenever I see a cell starting with W, I want to add up the value 2 cells to the right of that cell starting with W.
For example, if these following 9 cells in the same row read W1, 90, 100, M2, 90, 100, W2, 90, 40, whenever the cell starts with W, I want to sum up the value 2 cells to the right of that cell starting with W. In this case, I am adding up the values 2 cells to the right of W1 and W2, which is 100 + 40 = 140. How can I do that?
View 2 Replies
View Related
Dec 14, 2006
Is there a way to activate a Macro with a conditional in one of the cells? It's like this, I want that if the content of A2 changes to "True" then C2,D2 and E2 change to bold and the background color change to yellow. Is there a way to do this?
View 14 Replies
View Related
Mar 17, 2009
on the "all data" sheet, there are two rows that have a Y for file missing - these are to be marked, so that they may be focused on finding their file
currently, i am doing as such and then filtering by that column to print out a list, and would like a simplified solution (populate sheet "missing") with just those rows who have missing files - so that another user may easily view such data without having to fondle around with the master list
View 2 Replies
View Related
Apr 14, 2009
I'm building a complete part number reference for all service parts of copiers of a certain brand. Every row contains three columns; partnumber, description and machine type. Problem is, some parts are used in more than one machine. When the list is complete I want to do the next: If partnumber in (column A) is found in more than one row, I want to join the information of the other columns in two cells. (see attached example).
View 2 Replies
View Related
Oct 18, 2009
I have this file where i delete columns which are extra, in my real file most of the cells are formulas or links . Basically i need a macro which looks in row 4, and if it finds any zeros ( number 0 ) in the cell it deletes that whole column.
The zero is a indicator for me when i work on these files if it is needed or not. Included the file as an attachement.
View 2 Replies
View Related
Nov 23, 2009
I have a list of numbers that is imported and converted to text. I need to have all these strings beginning with zero "0" except the ones that begin with 1.
To summarize:
If the string's first caracter differs from "0"(zero) and differs from "1"(one) than prepend a "0"(zero). If the first caracter is 0(zero) or 1(one), leave the string as it is.
I am attaching a xls file with 2 columns: Column 1 is how the list is now and Column 2 is how I need it to be.
View 4 Replies
View Related
Feb 23, 2008
I need to lock a cell (F21) if J26 is less than 100. Can't work out how in conditional formatting. Is there a different way?
This is what I need to do:
F21=(IF,J26<100,Locked,Unlocked)
View 10 Replies
View Related
Jun 2, 2009
Can I program column B to show a certain value depending on the presence of a string of text in column A? For example, if A1 contains the word "Barbie", then cell B1 shows the value: "Ken"
View 5 Replies
View Related
Feb 2, 2010
I have a spreadsheet which has general usage and summary data at the top for ease of use. Begining at Row 15 the data is a standard table which includes columns for the following.
Cust Name | Status | Loan Amt | etc.
I have created a macro to make it easier to add new data rows at the top of the data already in the file (this makes it easier to view with the newest data at the top). I would like to create a conditional sum of the data in "Loan Amt" if the value in "Status" ="Active". I had no problem achieving this with the conditional sum tool but the problem is that the range changes whenever a new row is added to the top of the list. Unfortunately the range in the conditional sum adjusts as the old data shifts down for the new row and the sum does not contain the new row data.
View 2 Replies
View Related
Aug 9, 2013
How I can calculate the average fix time for each error code. The fix time will not be counted if it doesn't have a fix time. The distance between errors is not always the same, and the fix time is not the same.
How can I:
-Search through the Fix time and essentially add one to the count (only when it requires fixing)
-Only count the fix time for the previous error that has occurred.
-I have tried different combinations of using the MATCH and INDEX formula but have only received errors
-I can add additional calculation cells
-I can use VBA(although it will add to the already fairly long run time)
Error Code
1
2
3
4
5
6
Error Count
1
0
2
1
0
1
Avg Fix Time
Fix Time
Error Code
1
3
4
0:32:21
3
6
0:05:26
View 2 Replies
View Related
May 13, 2009
I want to add to it so that if the "Name" entered into the target cell is longer then the maximum allowd length it inserts an abreviation of the words (and increments where nessisary)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo StandardName
If Target.Address = "$B$4" Then Sh.Name = Target
Exit Sub
StandardName:
Sh.Name = "Sheet1"
End Sub
View 9 Replies
View Related
Feb 5, 2010
I want to create a 'virtual switch' so that if I change one cell from 1 to 0, that a formula in another cell becomes active. I can't just have the formula cell * the 1/0 cell, I need to comment out the formula or it draws in too much data.
View 5 Replies
View Related
Jun 21, 2006
I have what on the outset seemed a fairly basic task. In VBA I wanted to conditionally hide worksheets based on a value in my main worksheet.
I have 1 "main interface" worksheet with quite a few "result" worksheets as part of my workbook. Instead of scrolling left and right in the tabs menu everytime I wanted to veiw a particular worksheet I wanted to rather hide all the worksheets except the "main interface" and the relevant "result worksheet", which excel would identify based on a value in my main worksheet.
I wrote the following VBA code to do this but for some reason I cannot get it to work correctly. Perhaps I have made a simple logic error? I am fairly new to VBA. Any advice? ......
View 9 Replies
View Related
Dec 14, 2006
If you look in the file you will see a records sheets and a form sheet. I want to see if a record has been broken. All the times are in seconds and the distances are in centimeters. How can I find a certain row, compare and act based on an if statment?
View 2 Replies
View Related
Oct 12, 2007
I am currently using the following code to hide each row when there is no data between a certain column range. I need to add to this code so that it will also hide each column when there is no date between the row range.
Private Sub Worksheet_Activate()
Dim HiddenRow&, RowRange As Range, RowRangeValue&
ActiveWindow.DisplayZeros = False
Application. ScreenUpdating = False
For HiddenRow = FirstRow To LastRow
Set RowRange = Range(FirstCol & HiddenRow & _
":" & LastCol & HiddenRow)
RowRangeValue = Application.Sum(RowRange.Value)
If RowRangeValue <> 0 Then
'there's something in this row - don't hide
Rows(HiddenRow).EntireRow.Hidden = False
Else
'there's nothing in this row yet - hide it
Rows(HiddenRow).EntireRow.Hidden = True
End If
Next HiddenRow
Application.ScreenUpdating = True
End Sub
View 3 Replies
View Related
Dec 4, 2007
Attach is a Demo –Sample file
The formatting etc is same for real file – Note that the real file will have up to 65000 rows of data like this.
My Problem is to remove conditionally some duplicate rows - however my difficulty trying to thinking about this is that I cant use a unique value comparison as the columns I to L they all are X s so when a X is present in columns I to L need to be conditional on comparing values in other columns
What is needed is to repeat remove duplications in each of the four columns I to L (titled E1, E2, E3, and E4 ) when there is a match of string in columns F, G and M – but don’t delete if on same row there is a value in columns O to S (E5 to E9).
In the demo file – I have highlighted in yellow the rows that would be removed if the code works.
So;
IF there is an “X” in column “I” (E1) and the string in column F (personnel number) is the same as row below it, and the string in column G (person name) is same as below it, and the string in Column M (Team number) is same as row below it, and there are NO values in columns O to S (E5 to E9) on same row – Then delete the duplicate/s – retain only one.
Repeat for columns J, K and L (E2, E3 and E4)
View 4 Replies
View Related
