A vehicle has been assigned to collection, time of entry appears in Load cell coresponding with vehicle eg.e vehicle 1 'A1' = load 1 'B1'. I can get the exact time to appear in the cell currently but when I try to assign a second truck, the load 2 'B2' cell AND the load 1 'B1'. Im sure that there is an easy way to do this but i just cant figure it out. Can someone please help me out. Can i disable the cell that is updating?
I am working on a system where the system is having "australia time". Now, I want a worksheet where it should reflect the India Time after pressing a specific key in a specific cell.
View 3 Replies View RelatedI am looking for a formula to change a condition based on the time of day. Essentially, before 1pm I would like the formula to be:
=((I12/100)*25)/D12
After 1pm I would like the formula to be
=((I12/100)*25)/B12
I tried various ways with "=if(now()> ..." formulas to no avail because of the way Excel handles Now().
I'm trying to extract some data from an online page, but I require a specific cell at an exact time, each day.
For example, I would like to acquire the main data from the following page: [URL]
into excel, but additionally, I require the ESU14 (Sep '14) Open cell at exactly 0700 BST. I am currently using the 'Data' - 'From Web' feature to scrape the necessary fields into Excel however I haven't found a way to acquire one of those cells, at exactly a certain time, every day, even when Excel et al aren't open.
I am trying to pull a specific time frame of data from worksheet, in a large file, into another active workbook. A fiscal month. I don't know how to at all. I figure it should be a And IF and Vlookup but do not know how to execute it all.
View 1 Replies View RelatedI have a lot of rows of data in this date and time format "2/29/2013 10:00:25 AM" and I would like to remove only the time and leave the date
View 2 Replies View RelatedI build a VBA that search for 445 files in folders on specific path.
The code opens each file, take each time the same 31 rows, filter them and paste in the main data base file and then close the origin file.
So, for 445 files it takes about 6:30 minutes.
I need to use the Worksheet Change event in a particular sheet in a specified column which works fine if the data is already there in the sheet and then changed however,the data is in this sheet is actually a Sub-Set of a Main sheet i.e certain filtered records are being copied from Main Sheet and then copied to this IBSL Sheet.
After the data is copied I have to check each record manually and then categorize each record as Fresh , Rebooked , Cancelled , Tranch or On-Hold.....These 4 criterias are added in the Column 38 and the same thing has to be repeated in the column 40 , so when i change the data in the column 38 the same category has to be updated in the same row in the column 40..
But the problem is that the data is first copied from the Main Data Sheet into the IBSL Sheet using a Macro so then this even t gets fired and goes in the DEBUG MODE...
I need this to happen when i change the category manually..I am adding data validation at the same time while copying the data in to the TEMP sheet.
So what can be done to achieve..
Rather than manually typing the time in 5 min increments across a range of cells, i want to have a start time, then the next cell add 5 mins. EG: 09:00 next cell 09:05 and so on.
View 7 Replies View RelatedI got Excel for Mac 2011 installed. I have a table called 'Raw" that holds data from the beginning until the end of 2013 (in total ~7000 rows). I have columns for Category, Start and End time, and time period in Hours, Minutes, and Seconds:
Code:
A
B
C
D
E
F
Category
Start
End
Hours
Minutes
Seconds
Transportation
2013/01/01 01:00:00
2013/01/01 01:15:00
0:15:00
15:00
900
[Code] ...........
I'm trying to get an overview of how much time I've spent in January on Transportation. I would need to look at the Category entries for Transportation, then only use the ones which have the Start set to 2013/01, and then take the Hours/Minutes/Seconds, and summarise it in a new table called 'Data':
Code:
A
B
C
D
E
F
Month
Category
Occurrence
Hours
Minutes
Seconds
2013/01
Transportation
48
[Code] .......
I have figured out a way to count the occurrences for Transportation for January:
=COUNTIFS(Raw!B:B,">="&"2013/01/01 00:00:00"+0,Raw!B:B,"
I have been wracking my brain around how to put this formula together.
Is it possible or it can't do more then 1 cell at a time?
Here is an example:
If F1= button request and E1= Scoring then OK (take no action) also i want to add that Folder in F and Corrupt in E would = OK
If it doesn't match to highlight the cell in red
I'm trying to copy data from one excel sheet to another excel sheet. However, the data to be copied is dependent on the 'client name'.
To explain this further, in the first list I have a detailed report on our clients and the services provided to every employee of that company/client.
However, the sheet two only needs the names of the employees that belong to a specific client.
This can be done manually by setting a fliter on the name of the client/company, but I need to be automated. To ensure only that specific company/client company's employee name is copied.
I want to extract the following details into another collumn (bold ) from data in column B1 into C1. The information i want begins with 3 digits with a '/' then other digits follow.
M / AS308 - :308/65588 POUN
S33 / 33 - :133/01504 TR
NI101 - :101/915635 T
N101 - :101/915635
I have a big data sheet. I have approximately 2170 rows and 50 columns. In some rows I have a series of consecutive zeros. I need to build a formula that gives me the date ( located in row3) right before the first zero in the series of consecutive 12 zeros.
For instance in row 1340 I have a series of 12 consecutive zeros. The first zero happens in column AC, therefore I need the formula to retrieve cell AD3. however every rows has zeros located in different cells therefore I need a general formula.
I need a quick macro that will search column D for the words "balance forward" then delete the entire row. The macro needs to loop through many rows and it contain more than one "balance forward" reference.
View 9 Replies View RelatedI have a spreadsheet, where I am measuring dimensions for a cpk test. My question is, can I use vba to format the calculated cpk cell so that if it falls below a certain value it replaces that value with whatever I have specified in vba? Was wondering is this is possible.
View 5 Replies View RelatedI have an Excel 2003 spreadsheet with multiple sheets - one main one with all data and others which have only the data that falls into certain criteria. I'm wondering if there's a way to enter the information on the main sheet and have it automatically populate the correct "sub-worksheet" depending on what is entered in a specific column.
I'm not describing this well but I've attached a sample of what I mean.
I have created a form where a date and remark is added every few days. I am using Phone Number as the base of VLookup. I want a code so that the date and remark which is entered in a Text Box, is stored in the first available blank cell pertaining to that employee. I want the first date and remark to be under "Date 1" and "Remark 1".
Thereafter, if another remark is subsequently added for that employee, the form would ask to enter his phone number to identify which employee the remark pertains to. Then the Text Box where remark is entered should be stored under "Remark 1" if there has been no remark before, "Remark 2" if "Remark 1" already exists, "Remark 3" if "Remark 2" already exists and so on.
I do not want to enter it in a new row. I want to enter the remark in the next blank cell of the row which matches the employee Phone Number.
Untitled.png
I have data in several cells, and I want to extract only the information in the cell up to a /. For example "joesmith/shipping" I would like to have only joesmith in the cell next to it.
View 2 Replies View RelatedI'm basically creating a file where a userform comes up when you open the workbook, blocks the access to the actual workspace so that no manual entry can be done. What the userform allows you to do is add a new entry to the database (the userform has some text boxes where we type in stuff like location and name or age) after having found the next empty row in the enormous database (3.000 lines)
After having clicked the OK button in the userform and added the data to the database, I would like the userform to have a pop-up message box with specified text like:
"The number assigned to this entry is:" and then I would like to add a variable with excel using the cell in column D of the same row of the new entry. So the end result would be :
The number assigned to this entry is: 01
to shade rows of data in a table if a cell contains a specific value, such as the word Total.
View 9 Replies View RelatedI have number in cell (A1) = 100. when I enter number in any cell of column(B) for example (B1)=10. then in cell (C1) the result of (A1) - (B1) = (90) and if I add in cell (B2)=10 then in cell (C1) the result of (A1) - (B1+B2) = 80 .accumulatively in cell (C1). and any number in column (B), the result will be (A1) minus any number in column(B) accumulated in (C1)
second question
I have number in cell (A1) = 100. when I enter number at cell (B1) = 10 then the result would be in the adjacent cell (C1) = (A1) - ( B1) = 90 and If I enter a new number in cell (B2) = 10 then the result would be in the adjacent cell (C2) = (A1) - (B1+B2) = 80 and If I enter a new number in cell (B3) = 10 then the result would be in the adjacent cell (C3) = (A1) - (B1+B2+B3) = 70 and so on. I want the result to be add automatically to adjacent cell in column (C)
I was wondering if there is a formula that will search a cell for a word or other specific criteria then if it finds it, puts the requested data / word in the formula cell
Example:
If I have a list of vehicle descriptions all in different formats:
CAR1 1.4 SRI 3 door Hatch
CAR2 5 door saloon GSI 2.0 V8
2.2 CDX 5 door CAR3 Estate
CAR3 Estate 5 Door CDX
Say I want to know which ones are CDX varient I need the formula to look in the cell and return "CDX" or "YES"
How can I pull data from a specific cell from a closed worksheet in VBA?
Not sure but I think that Workbook("wb1.xls").Worksheet("Sheet1").Range("A2") only works if the worksheet is open.
ColA contains Purchase Order (PO) numbers and I want to return the department number or project number (which is contained within the PO) in ColB eg.
A B
PO # Department/Project #
AP001234P1234 Want to return everything before the 'P'
PR004444P4444 Same as above
TR2008-1234 No need to change
6501P1234 Want to return everything before the 'P'
I was using IF function but there are too many variables then I thought about the 'P' but not sure how to go about it?
how I might 'trigger' a macro to run when a certain cell was filled with any value at all. I have the macro working fine (it's a simple copy and paste special) but I want it to run automatically when the cell A20 has a value in it.
View 9 Replies View RelatedI'm trying to do is definetly simple to a lot of you, but I'm struggling on how to get started. I attached a workbook that clearly shows my work! I'm almost there but not quiet. Basically the user will be able to make a slection that will be used to lookup values from a different sheet. Each selection triggers a ranking number at the bottom (red pattern) in the worksheet. Now I would like to capture all scenarios and display the ranking.
View 4 Replies View RelatedI need a formula to extract data after "<li>Color: " & before"</li>"
A
B
1
ORIGINAL
RESULTS
2
blah blah blah<li>Color: White</li>some informations
White
3
blah blah blah<li>Color: Red</li>some informations
Red
4
blah blah blah<li>Color: Black</li>some informations
Black
5
blah blah blah<li>Color: Red</li>some informations
Red
I have already use this formula but I get the "#VALUE!" error even if the cell contains the "<li>Color: </li>" data.
[Code] ....
Sometimes, some cells does not contain the "<li>Color: </li>" data, I don't want the "#VALUE!" error, I just want a blank cell there.
This is an example with the real values I want to extract Example.xlsx
This is the formula that works
[Code] ......
I need to add something to the code below to have my first row of data entered into cell "Q8". As it is now the data is starting on row Q9 instead of Q8, but the rest of it is filling in correctly.
[Code] .....
I have a spreadsheet with my Periods along row 10. e.g. C10: "1", D10: "2", E10 "3", F10: "4", G10: "5" etc. (green on the attached sheet). I have my departments along column B, e.g. B11: "Baked" B12: "Fresh" B13: "Frozen" (yellow on the attached sheet)
what I need and cannot work out is some VBA code that will populate two variables (lets call them Period & Department) when I click on one of the figures. For example if I click on cell: if I click E14: Period would have the contents of cell E10, and Department the contents of cell B14.
if i click G14: Period would have the contents of G10, and Department the contents of cell B14 again. I know how to get the click on the cell to work properly etc, and I have code to slot these variables into that works very nicely, I just can't get this bit to work!!!!