I'm trying to get values to transfer from one cell to another based on whether the date in an adjacent cell is equivalent to the current date or not. I've tried using "Now" and "Date", but can only get my code to respond if the two are "<" or ">", I'm basically looking for a response for "=".
View 2 Replies
I have created a userform and it works fine.Following code is assigned to commandbutton to transfer data to sheet "FT".
[Code].....
I want to add that : If value in combobox2 is “ KT” then all entries are copied to sheet3
Otherwise copy everything in sheet”FT”
Other parameters remain same.
I have a worksheet, with a "cost" column, and a date column. I have another sheet with dates pre-written, and a cost column as well. I'd like to know how to make the cost from worksheet 1 go to worksheet 2 under cost, but also add up any costs that are on the same date.
View 9 Replies View RelatedAttached is an example of what I am trying to do.
I am looking to transfer the sums for a calendar month in one column to cells in another location for each month.
I put some notes in the sample attached, I appreicate any help getting this to work.
I am currently working on a workbook to have employees fill in data on what tasks they compelted for the day, and how long it took. There are 5 colums (for this purpose) Task, Description, Time, Required to complete, Completed. The tasks are predefined and listed out in each row. There is also a space for employees to select the date they are entering the data for.
I would like to have a macro that is linked to a submit button and when pressed populates this data into a database on another sheet. This database is split into two sheets (1 with time and one with tasks required/compelted). Each sheet has the list of tasks going down a cloum on the left, and dates along a row on the top. When the employee presses submit I would like this data to popuplate in the coresponding date and task fields and then reset the form on sheet 1 to all 0's.
I have a spreadsheet where raw data is entered ("RAW_DATA") for each month it is collected. There are 7 questions that are tallied and each of those responses are transfered to a separate data table for analysis (7 individual tabs within the spreadsheet). I'm currently manually data entering the values from the raw data tab to the other seven tabs.
I would like to have VBA code that could transfer the data from one month ("RAW_DATA") onto the other 7 tabs based on the date and facility identified in the raw data tab. I have not attempted the VBA since I do not know where to start with searching on two variables.
I am trying to use conditional formatting to highlight items that are either past due or coming due soon. The data to be evualuated is in a report that I extract from another system and run bi-weekly or monthly. The dates included in the report represent the date in which something was last updated, certified, tested, etc. All dates expire one-year from when they were last completed. The colors that I want are as follows:
If something is past due (date shown is more than 1 year old) or due within 30 days = RED.
If something is due within 31-60 days = YELLOW
If something is due within 61-90 days = GREEN
So, if an item on a report is showing it was "LAST UPDATED" on 2/21/2007 it should show up RED because it is due within the next 30 days.
If an item is not expired (less than 1-year old) and falls outside of the defined 90 day window then it should just remain as is with no formatting.
I have a monthly calender, with each month on its own sheet. I have a sheet named Holidays, which list the holidays. I have been able to use conditional format and highlight the dates in the months the holidays fall on, but I would like to highlight a range of cells below the date on monthly sheet. I have attached a sample of what I have and would like to achieve. I am not much good at using VBA, but would not be against using it either.
View 2 Replies View RelatedI have a column of cells containing dates. I need to change the colour of any cell which is 7 days older than todays date. I would like to leave the header and any blank cells unchanged. Every formula I've tried has changed the enire column, or if I selected a range, the entire range.
View 5 Replies View RelatedI have two cells. both formatted to contain dates.
cell a1 will have a date in it. cell a2 will have a date calculated to be cell a1 + 42 days.
now, when cell a2 reaches a date that is greater than cell a1 + 42 i want cell a2 to be formatted as fill red.
so, i have tried a conditional format as =a1+42 >=TODAY() but it always highlights as red even if the value in cell a2 is less than cell a1+42.
Not very good at this but I have the basic sheet attached. All I want is a way to highlight the first cell when the date is within 30 days of the "Due date".
Calibration Record-example.xls
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.
I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting:
Cell value is less than or equal to =TODAY()
All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.
In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
I have columns of figures with times like 5:52:54 and next to them cells with values that i wish to format based on the time.
that is if the time on the adjacent cell is before 8AM then make the value cell have a red border it does not have to be conditional formatting - Excel 2002 in win7/64
I am trying to get a row of cells to highlight a percentage based on a date range
Below is an example of what my spreadsheet will look like, very simple for managers to read and understand but I am stuck on how i can get this to display the right way.
In the example i would need the Jan column to colour for a certain percent for 21 days and continue to feb for 26 days. Im not sure if this makes sense but this is what they are asking for. Colour bars to simple show the percent of days off each month.
Name Start Date End Date Jan Feb Mar
Dale 11/01/14 26/02/14 21 days 26 days
I have attached the spreadsheet for an example : Book1.xlsx
Solve this issue for transfer value from master sheet ( Daily Report) to corresponding sheet and add value according to its Date. E.g. :
Master Sheet : Daily Report
Manpower : 35
I want to transfer the value of manpower to sheet ( D_manpower) and insert value 35 to according to month & date ( C5 & G5) shown in master sheet ( Daily Report )
Like this all data need to insert according to its corresponding sheet according to month and date
File attached : Report 2014.xlsx
I have created a sales form entry excel workbook. This consists of 52 sales weeks for the year. A week starts on a Monday and therefore the first week for this year (2005) started on the 4th January.
As I want to run reports from the data I need to ensure that dates etc are in a certain format and that the entrys go into the correct week. I have created a "form" on the first sheet which automatically adds TODAY() as order date and then has boxes for the users other information such as customer name and price.
I now want to create a button which when clicked will add this information onto a new line in the correct week sheet. However I cannot for the life of me figure out how to do this. I am aware that Excel recognises dates as a serial number and assume that somehow I can use this. I can also do the bit where I create a marco to copy the info over to the week. I would be very grateful if someone could tell me how to put it in the right week without creating numerous IF loops?
I have an 8-digit number like this :
06152002
06 = year 2006
152 = day number 152 in the year
002 = sequence of entry
Is it possiable to transfer that number into date in one cell and the sequence of entry in another cell ?
I'm building a report and need to transfer data to a History Tab each time the macro is run.
"Report" Tab contains data to be transferred: C6:F6 to the "Historical" tab B:E in the appropriate date row.
The Date would be found from the Oldest date (furthest into the past) on the "Calculations" sheet, Column U.
I have a userform with many textboxes that I am using to collect data which is transferred to a worksheet using a command button on the userform. All data is correctly transferred to the worksheet except for the text box I am using for the date.
The date is transferred from the userform to the spreadsheet but the date is left justified implying that it is text but dates that I have manually entered into the spreadsheet cell are right justified. This may seem picky but I am using a 'count' function within the spreadsheet to determine how many rows contain the date.
I am using the following code which I am entering in the format of dd/mm/yyyy, to to transfer the date to Cell A1 the worksheet 'Results'.
Private Sub CommandButton1_Click()
Worksheets("Results").Cells(1, 1) = UserForm1.Textbox1
End Sub
how to transfer the date to the cell so that it right justified, hence treated as a number within the cell.
Currently have
Code:
ThisWorkbook.Sheets("Sheet1").Range("B8:G8").Copy
Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
B8 contains a date and on sheet2 i have lots of columns, what i was wondering is If I had lots of Tabs Jan Feb Mar etc would it be possible to transfer the Cells B8:G8 to the tab based on B8
i.e. =if(month(Sheet1!B8)=1, copy B8:G8 and paste in 1st available row on the Jan Tab
or =if(month(Sheet1!B8)=2, copy B8:G8 and paste in 1st available row on the Feb Tab
I have two columns. In column B is the date of "last check". I column A is the date of "next check". I would like to have cell A2 in yellow color 334 days after the date entered in cell A3 and than in red color 365 days after the date entered in cell A3. Same thing for cell B2 related to date entered in cell B3. Yellow color in cells announces that check will expire within 30 days and red color that check has been expired.
View 1 Replies View RelatedI am trying to alert our purchasing mgr when order dates are approaching or not meeting our project deadline.
As of now i have the following rulesif order date is due today or past due - redif order date it greater than project date - redif order date is due within 2wks - yellow
Now all I need is a rule where there is an order without a due date but the project deadline is within 2wks (yellow) and past due or due today (red)
KO_04.01.14.xlsx
6 worksheets ("Monday" ,"Tuesday" etc)
1 Worksheet (call it Main)
In the Main sheet let's say there are 3 column headings - Date, Inv No, Amount
I want to copy from the Main sheet to the appropriate day e.g if the dates in the main are13/01/10 and 14/01/10 then all the info pertaining to 13/01/10 should go to the Wednesday worksheet.
I have a workbook with several sheets containing prices of products. One column has the product number and a second column has the price. We just received new prices. Can a macro read the product number from the new sheet, find the same product number on the old workbook and transfer the new price. and than change the price so I know it was updated.
View 2 Replies View RelatedI am working on a list of agents and I want to get rid of the duplicates. I know I could use conditional formating to highlight the duplicate names but it conditional formating won't allow me to find duplicates based on three cells in a row. My code below works to an extent but it misses some of the duplicates in the list and I don't know why. I have check the cells of the duplicates and they are an exact match.
Sub SearchDups1()
Dim rFnd1 As Excel.Range
Dim rFnd2 As Excel.Range
Dim iCount As Integer
Dim iEnd As Integer
Dim sAdr1 As String
iCount = 2
iEnd = WorksheetFunction.Match("zzzzzzzzzz", Sheet2.Range("D:D")) + 1
Do
Set rFnd1 = Sheet2.Cells(iCount, "D")
Set rFnd2 = Sheet2.Columns("D:D").Find( _
What:=rFnd1, LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True)..............
I thought I had finished my project but I keep getting errors, the latest one being that I have 2 comboboxes on userform "timekeeping". When I press the commandbutton "Submit", I want the values in the textboxes on that form to be placed in the spreadsheet, depending on what the selections the user has made in the comboboxes but I keep getting an error saying that the macro doesnot exist in the workbook even though it does!
The file is too big to upload here so it is found on rapidshare
[url]
I have created timesheets for employees that work in our shop. Our company manufactures products for different industries, such as mining, wind power generation, general industrial applications, and so forth. I modified some time sheet templates I found for excel to accommodate our company's actions. Each employee has their own workbook, in which the months are separated into different worksheets. Each sheet is divided further into weeks and in each weekly section the areas of information are divided as follows:
A / B / C / D / E / F / G / H / I / J / K / L / M
Work Sector / Workshop or Fieldservice / Scope of Work / Job # / Reg or OT / Mon / Tue / Wed / Thu / Fri / Sat / Sun / Total
There are 7 workbooks (one for each employee), each with 12 sheets (one for each month). I want to create a master sheet that will pull information from everyone's timesheet if they worked on a particular job. In other words, I would like to type a job number into a cell, then have excel look through everyone's timesheets and pull over only the rows of information that contain that job number
Is it possible to change the format of cell AI3 based on the format of cell C3 and D3? I have C3 and D3 set to turn red based on what is in cell C2 and D2. I would like the following done:
If AI3=C3 & C3 is red, format AI3 blue
If AI3=D3 & D3 is red, format AI3 blue
Otherwise, leave AI3 unformatted.
Possible???
I have a list containing blocks of stock price values with each block representing a series of values at a given time e.g
1400 156
1400 99
1400 74
1400 86
1400 256
The number of entries may vary for each block but is never more than 60. the next block may be e.g.
14:05 15
14:05 42
etc
and down a very long list of around 65000 entries. I'm looking for a routine that will add up all the values in each block and calculate the total for that time block. I then need to fill in an adjacent column with the perecentage that each value is of the total. A completed block may look like;..............
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
