I need an IF- function where true is returned to B2 if A1 starts with "ABC", i.e. A1 can be "ABCD", "ABCEF", "ABC G", etc.
In many databases an asterisk (*) can be used for these puposes, but I have no luck with that one.
I have the following formula to count the time between two dates and result in an answer with months/days:
=DATEDIF(E14,E15,"M")&" months "&DATEDIF(E14,E15,"MD")&" days"
The problem is that the function does not count the start date. Is there any way to modify this formula so that the start date is included?
For example if the start date is 02/01/2012 and the end date is 02/28/2012 it results in 27 days but I want it to result in 28 days or 1 month.
how to go about this or if there are any better date functions out there?
Ihave this exel sheet i nedd function to write the starting date and time immediatly after i enter the name in the last colume ineed from exel to write the date and time emmideatly if the case is (done, cancelled ,or rejecteted)if the case is (select status )i want the cell empty but if the case is (pending)iwant to the program to calculte the deffirence between the ClosingDatetimee(which written by the program)and the delivering date(which entered manually by user)
View 9 Replies View RelatedI'm trying to develop a new daily timesheet for my production workers, where non-production items are recorded in 15 minute intervals. The user would put in "Clock in" by the corresponding time, and the same for "Clock out" at the end of the day. Any non-production items will be type in next to their appropriate time. Since clock in and clock out times will vary, I need to set up a formula that searches the array of cells for the day, finds the "Clock in" and "Clock out" values, and counts any blank cells in between them. Basically the blank cells will equal production time, and the result of the Count function will be multiplied by 0.25 to get the hours.
I am having a very difficult time finding a way to set the "Clock in" and "Clock out" cells as the range for the Count function, because it won't always be the same cells. What would be the best way to automatically have excel find the cells containing these values and set them as the range criteria for a Count function?
The formula at the bottom was one of my initial attempts, but it didn't work. I took out the '=' for the screenshot, so that wasn't the problem.
I want to build a Macro that basically moves right of the cell I am in a few cells and then copies and pastes this info into another worksheet and then prints this worksheet.
However, I can't seem to build a macro that will run from whatever cell I am in.
Everytime and try it just goes from the cell I was in when I recorded the macro.
For instance, if i build the Macro in row 1, but then want to run the marco in row 23, it keeps going back to row 1 whenever I press play.
I know there must be a way of telling it "move right 5 cells from whatever cell is highlighted when the Macro is run...etc"
I'm trying to write a macro that will find a value beginning "ber" and paste it across to a different column. I have had a go at it but I keep getting the offset flagged up.
It needs to move 15 columns across and 7 rows down if that is of any importance.
I have a macro that I run repeatedly in a certain type of spreadsheet that does an export on a selection, pulls up a "Save As" message box and saves as a text file. There are 9 sheets in the workbook that contain the same data analysis for a set of samples, the only difference between them being the number of data (e.g. some may have 1500, 1400, 1600, etc.). I only select three columns of data to export (I6:K????) that contain counts (1,2,3,....), x-coordinates and y-coordinates. The counts column (I) uses a formula that only counts if there is an x-coordinate next to it (Col J).
[Code].....
Generally, what I do to make quick work out of selecting the variable ranges is to select Sheets 03-11 (the first nine sheets) and select K6:I6 (starting w/ K6). Then, on each individual sheet, I do CTRL+SHIFT+DOWN to select all the relevant data (if I started my selection with I6, then it would select all cells that contained formulas which may or may not have x,y-coordinates adjacent to them). Once the data is selected (I6:K????), I run the export macro and save the data as text. I would like a macro that can automate the selection for each Sheet 03-11, excluding Sheets "all", "data" and "summary", and run the export macro, first prompting me for a file location and a file prefix. When the text file is saved, it uses the file prefix and Worksheet name to build the filename, i.e. "pathprefix_wkshtname".
I've attached an example workbook : 20120511_Au-cit_pH5_test.xlsx
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 that will monitor payment schedules, in which both payment frequency and the payment start date are inputted by the user.
As such, to make filling out the column(s) fool-proof, I want to grey out cells in which data should not be entered.
For example, if the payment frequency is every 6th day, and the payments are to begin on day 0, then days 0, 6, 12 (etc) should be left white, whereas the remainder of the cells should be shaded.
I can achieve this using multiple conditional formatting rules in excel2007 with iterations of formulae of the type:
I have the following data
A | B
_______________________
WD |100 From Bangalore
CR |Hyderabad 50
CR |Mysore 70
WD |900 From Kolkata
_________________________
and so on in A and B columns. In the C Column depending on the cell value I want just the places like Bangalore, Hyderabad, Mysore, Kolkata etc only to be displayed like shown below.
A | B | C
___________________________________
WD | 100 From Bangalore | Bangalore
CR | Hyderabad 50 | Hyderabad
CR | Mysore 70 | Mysore
WD | 900 From Kolkata | Kolkata
______________________________________
The Places in column B are not at finite position and that is the big problem i am facing when using Left or Right function. Could anyone help me with a proper Excel function to get this done. The thing is the whole cells should be searched and accordingly the output should be obtained.
I am trying to search three columns on a worksheet, that contain a range of customers, product names and the amount of that product sold to the to the customer.
On a separate worksheet I have created a table, which I hope will show the customer, the product and the amount sold. So basically I need either a formula or piece of code that can match the customer and product, along with the amount sold and display it in one table. The data is by nature not kept in alpha or numerical order and my problem lies in being able to search through each row and extrapolate the necessary figure.
I have a column that is filled with text of varying lengths and I'd like to search through each cell in that column looking for a specific word or words. Unfortunately the length of the text varys greatly between each cell and there is very little uniformity so I need to be able to search through the entire entry in each cell and then highlight that row if a specific word or words are found.
I'd also like to be able to add a number "1" in another column on the same row if the search finds a word or words. Any help would be greatly appreciated.
If the cell only contains the exact word or string I'm looking for then it's easy but I can't figure out how to search through text in a cell that contains more than I'm looking for.
example: Lets say I'm loooking for "caught fire"
column Q contains:
1 "The computer caught fire after several hours"
2 "A house on the hill caught fire"
If "caught fire" exists in the cell being checked, then highlight the row and put a 1 in a specific column, lets say J.
Would like to write an IF statement where it reads if any cells in a column are highlighted and gives the sum of those highlighted while ignoring the ones unhighlighted.
Is there any way to do this or an alternate method that would possibly work?
Concatenated cells. My issue is how do i search through a sheet with cell values that have been concatenated?
Lets take an example:
I would like to search for a string call sales in sheet1 which would display all the employees and schedules for that department (do note that employees can be part of not just one department). but my cell formatting is as follows:
Sheet 2, Column 1
Employee name
E.g. Row 2 Employee1
Sheet 2, Column 2
Department (cells concatenated)
E.g. Row 2 Sales, Marketing (Concatenated Cell)
Sheet 2, Column 3
Work schedule
E.g. Row 2 8 AM to 5 PM
So when you search for sales, it will display employee1 and work schedule.
I am trying to get the following equation to search for cells that contains the term 3R. The issue is I have several different 3R formations as follows and want it to count all of them.
3R
3R FIB
3R S Nasty
3R S Nasty FIB
This is what I am currently using but I want it to be more detailed. Where my search is for everything on 1st down with 10 yards to go in a 3R formation.
=SUMPRODUCT(--(C2:C350=1),--ISNUMBER(MATCH(Criteria,{10},0)),--(H2:H350="3R"))
23
20
1
10
-19
R
Run
PISTOL HVY
3R
[Code] .......
The following macro searches for missing combinations. This macro will search the complete list and will return any missing combination from "1, 2, 3, 4" to "7, 8, 9, 10".
I need to make some changes in this macro, so that it will search for missing combinations only within a specified range of cells (and not the whole list). For example (see excel file attached), I would like to place a search within range("G23:J183"), from combination "1, 2, 6, 9" to combination "4, 6, 8, 10". In this case, it should return only 9 missing combinations.
Attached File: Example Find Missing Combinations.xlsm
The following code works fine to determine if a particular character occurs within the selected range of cells:
Code:
Sub CheckIfCharacterIncluded()
For Each MyCell In Selection
If InStr(MyCell.Formula, "#") Then
MsgBox ("The " & "#" & " character was found in cell: " & MyCell.Address & " at position " & InStr(MyCell.Formula, "#"))
End If
Next
End Sub
However, I would like to extend this functionality to check for multiple characters, using some sort of array that contains all the characters I want to check for e.g. "#","*","£" and so on, without having to repeat the above code for each character for which I need to check.
How can I create a multiple if statements that returns a specific response if the cell is blank?
For instance, =if(K6=[date],"Carry Out"), if(K6=[blank],"Break Out"))).
The easiest way of explaining what I'm after is to say, I have letters of the alphabet, in their own cells, and I want to find them by way of a search. I don't mind how this is done, but it would be good if for example you entered A, C and E, any cells containing those letters changed, maybe became bold, or the cell filled with colour.
View 9 Replies View Relatedhttp://www.excelforum.com/excel-prog...problem-2.html
I have a workbook with a macro that searches through worksheets to find certain values and place the location of the value in a worksheet cell. The following line ontains the found cell.
I need to search column C for the text "X". If an "X" is found, I need to copy the text from the cell directly left of it and paste it into a seperate worksheet into cell B2, then go back to the original worksheet and delete the cell with "X" on it and the cell to the left.
View 9 Replies View RelatedI am trying to search text cells to return a word count within a particular row of cells and I am currently using the following formula:
=COUNTIF($D4669:$EI4669,$O$3), where cell o3 contains the word to search and $D4669:$EI4669 the data.
However, this formula misses data that contains characters such as "," etc.
I would like to select all cells in column A that begin with the text "SP". Some cells will be contiguous but others will not. For instance in one case, I would like the macro to select cells with the text "SP" which would result in cells A1, A2, A3, A10, A15 being selected. I am working on building a macro that will then do other things to these cells/rows so this is the first step.
The below code will select the first cell with "SP". How would I alter this code to only search Column A and select multiple cells? or totally different code.
Code:
Sub test()
Dim r As RangeSet
r = Cells.Find(what:="SP", LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then r.Select
End Sub
Column A & B has a list of Supplier Part numbers and Buyer Part numbers as below.
Supplier P/N
Buyer P/N
HGFYE/12
111111
HYEYDH/14
222222
[Code] ..........
Cell D2 is an open cell that any data can be entered into as a search term. What I am trying to do is search for a Supplier P/N that have the characters "H", "G" or "E" in it, so entering "HGE" into cell D2 would display the results into columns F & G as below.
Supplier P/N
Buyer P/N
HGFYE/12
111111
HDGTEY/56
333333
I can easily do a formula for 1 character or a string of characters.
To complicate it further, if the search term has in this example has "YFF", I would like the same formula/code to workout that the result in F & G should show this time
Supplier P/N
Buyer P/N
YHDHFF/58
555555
I am using Windows 7 and Excel 2007.
I'm using the following code to delete select rows one at a time. I need the last row in the range to remain therefore I prevented the user from deleting the row one up from the row that contains "Total Hours" (which is always in Column B). The code works great as long at the user clicks into a cell in column B. If the user clicks into a cell in column A, C, D, E, F, G, H, or I then the code allows the user to delete the last row.
I believe I need to search entire rows to determine if the row contains "Total Hours" .
[Code] .......
Attached File : Staffing Report 1.44.xlsm
Why won't Excel recognise any entry in a cell that contains a ~. For example I have lists of job numbers such as FI9CMR~002 or RE1RE~~004.
The ~ is essential to us in order to pad out the number to 10 characters. This data is exported into excel from another database.
I have a column with number of Km from location x to location y (A), and a column with fuel supply (B). I what to know how many km make the driver after the fuel supplyes (787 Km - for exemple from bellow).
I was thinking to SUM values from column A starting ROW 3 (column B) (first cell non blank), but i don't know how to match SUM and ROW functions.
A B
1 150
2 200
3 250 50
4 120
5 260
6 157
I have a spreadsheet where a column has many cells being empty and others with values. I need to use copy-paste skip blanks to another column so it only overwrites cells that contains values. BUT The cells in the column appears to be empty, not blank, when I try use the copy-paste skip blanks it doesnt work. However, when I press delete in every empty cell the copy-paste skip blanks works for those cells.
Do you got a fast method to make all the empty cells blank?
I have a spreadsheet for which I have to set up a formula to get the minimum value from a range of cells, but that range can include blank cells, errors (#DIV/0) and zeros, all of which I want to be ignored. I can work out how to ignore EITHER the zeros
(=MIN(IF(C10:G100,C10:G10)),
or the error cells
(=MIN(IF(ISNUMBER(C9:G9),C9:G9)),
How to exclude both. If I try to combine both of these exclusion criteria it doesn't work and I end up with the answer #DIV/0, which is one of the values I want it to ignore.
I have the problem to get the starting and the ending time in a timetable work sheet, adding the starting hour in the first cell and in the second the ending.
View 5 Replies View Related