How do I express in a formula if ce406= any one of the following results, then Yes, if not then No:
1 2 1 1 1 2
1 2 1 1 2 1
1 2 1 2 2 2
1 2 2 1 1 2
1 2 2 1 2 1
1 2 2 2 2 2
2 2 2 1 1 2
2 2 2 1 2 1
2 2 2 2 2 2
I tried the following formula, however, it did not give the desired result: ...
In my example booklet. I've got two sheets.
Sheet 1 has 9 inputs (rows) from 5 sources: A,B,C,D,E (columns)
Sheet 1 also has flag columns to match inputs based upon similiar values (names) in description columns.
Sheet 2 - has two tables,
first table matches values of different sources w/ similiar descriptions
onto the same row.
table two: displays, min. value for each row in first table ...
I am trying to pick out certain bits of information from the below "example" set of data:
A
1
A
1
B
1
C
1
[Code] .......
My aim is to record the letters that are recorded against both numbers (note: in my data there are more than 2 sets of numbers). For the example above the solution would be:
A
1,2
B
1,2
...because these two letters appear against both 1 and 2.
There are some letters that are duplicated against the same number which is making it hard for me to work out. I don't care if the same letter appears against the same number, I just would like to know instances when a letter appears with a different number, and if possible what that number is.
I have a code... and I am trying to use it with a different file.
The files is set up the same, the code has been unedited, but it's not working
Sub findAndReplace()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim searchFor As String
Dim searchCol As Range
Set ws1 = Sheets("SBK") 'sheet for which we look in column A
Set ws2 = Sheets("Sheet1") 'sheet we try to match with column E
'last row on the sheet with the updated information
lastRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row....................
I want it to look in SBK at column A and match it to column E of Sheet1. Then copy collumns DEFG from SBK to DEFG of sheet1.... Is there some reason it's not doing it?
I have a sell price in J2 and I want to find out if it matches a sell price in any of the cells form K,L,M,N,O or P and if it dose return the corresponding column header
e.g. in J2 I have 2.40 and it matches M2 therefore I want it to return the Header in M1 which is SSL2.
If it doesn’t match anything I want it to return “Tier”
I'm looking for click event code for a list box called lbActiveItemList that finds a match for the selected record in Column 1, to the Sheet ReturnData in Column A from row 6 on (using the G column to find the last row). Upon finding a match it should close the userform, and make the G cell for the matching record the Active Cell.
View 7 Replies View RelatedI have a list of people and the dates they completed activities on. I would like to do a vlookup (or index/match) that returns the most recent event. For example, if I was after the most recent date that 'rogers' went 'fishing' it would be 03 Feb 07....
View 9 Replies View RelatedI have made a database to record drawing numbers.
In column A is the drawing number and in Column B is the revision number.
I am trying to write a macro that will alert the user when they are trying to enter a drawing that contains BOTH the same drawing number AND the same revision number as one already in the database.
Since drawing numbers will be reused and only revision numbers changed when a revision is entered, I need the function to check both column A and Column B then display a msgbox if there is a match.
I need a formula that returns the sum of cell N12 from multiple worksheets where cell A9 matches a particular month and cell K7 matches a dept name (such as Scientific).
To explain further, in say 50 worksheets, I want to pick out all the March sheets, for Scientific, and sum all the N12 cells from those sheets.
I've been trying to alter an array formula i've used previously, but gotten completely lost.
Need to kown if Index and Match can be used if you have to criterias. Below is a glimse of my sheet. The data does come from 2 sheets, I don't know if that is another reason, doubt it, but needed to tell you just incase. I have sales information and I just want a quick view of the sales by associate by date. My 2 condition are the associates id# and the date.
=INDEX(($J$2:$Q$4),MATCH(AND($A2,C$1),AND(N2:N4,M2:M4),0),9)
my range is from J2:q4, my criteria is column a2 Sales asso Id and C1 the date, and match is N2:N4 & M2:M4, witch is the 9th column.
I have a problem finding the correct formula for counting matches with conditions between 2 non-equal ranges in Excel. The sheet is a try at making a working schedule template a bit automated.
For Week 1 each cell in the H16:H25 has a drop-down list (originating from BD30:BD50) where a work position can be chosen. The fixed list in BD30:BD50 starts with “<<SELECT>>” which is the default choice for the cells in H16:H25, and then “HOLD” before continuing with various work position names. K16:K25 is shift number 1 on Monday, L16:25 is shift 2 on Monday, and so on until Shift number 6. Then the rest of the days of the week follow (each with 6 shifts). Monday through Sunday (with 6 shifts for each) ranges over K16:AZ25. In the cells in K16:AZ25 the following can be entered: “x” (work), “o”(off), “-“ (leave).
The issue is the formula in each of the K26:AZ26 cells which are to total each of the shift columns . I want to count all the “x” in each column, but ONLY if the positions chosen in H16:H25 matches one of the positions in the list in BD30:BD50. NOT if a cell in H16:H25 displays “<<SELECT>>” or “HOLD” (even if it has a “x” entered in one of the Shift cells).
For example: .....
hope the below example is clear to understand and makes sense .....
View 10 Replies View RelatedI am trying to write a code in excel vba where I need to match specific text in multiple columns and if text is matched then I need to insert new blank column after the specific text.
My code is like this.
Dim Col_num As Integer
Col_num = Application.WorksheetFunction.Match("Customer Defined 06", Range("6:6"), 0) + 1
Columns(Col_num).Select
Selection.EntireColumn.Insert
Cells(6, Col_num).Select
This code is working fine in single condition but when I enter another condition then its giving me an error i.e Type Mismatch.
I m trying to evaluate part of a string to determine if a number and if 6 char's in length. If Yes, return the 6 digit number, else 0
My formula below returns a #VALUE! error
It worked before I added the 6 char length condition, but sometimes return values of greater length that I do not need.
=IF(AND(ISNUMBER(VALUE(LEFT(C1809, FIND(" ",C1809)-1))),LEN(VALUE(LEFT(C1809,FIND(" ",C1809)-1)))=6),VALUE(LEFT(C1809,FIND(" ",C1809)-1)),0)
I seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.
I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.
Dim n As Double
n = Now()
sn = Str(n)
p = InStr(sn, ".")
first = Left(sn, (p - 1))
l = Len(sn)
d = l - p
S = Mid(sn, (p + 1), d)....................
Im trying to have a formula look at two diferent columns and if they meet the criteria add the third column.
I tried using sumproduct but It wont give me anything but error messages or a zero.
Looks like this
=SUMPRODUCT(A:A="Stewart",(G:G="Fiduciary"),C:C)
Need it to say:
if column A = Stewart and Column B = Fiduciary then add up the amount in Column C
I am creating an asset management sheet. For the formula I am trying to work out there uses 3 fields : ID, start date, and end date.
What I want to do is be able to show if the ID is duplicated within another record with an overlapping date. So an item is flagged if it is in the list within the same dates as another record. I tried a few countif formulas but with no success.. I may just be approaching the problem incorrectly though.
I am looking to create a macro that will create a new sheet when data is added on a summary sheet. Example.
1. Summary sheet called "Variations" contains columns that will contain the information needed for new sheet (Columns A to D)
2. When data is entered on "Variations" sheet: Column B, then macro automatically creates new sheet renamed to e.g. VO1 (Number used on "Variations" tab) and is a copy of "Master" tab.
3. Data entered in Column A to D on "Variations" tab is automatically entered onto new sheet created (e.g VO1). Shown is blue on attached file. Additional data is updated on "VO1" sheet and this then links back to "Variations" tab
I'm trying to find a macro that will run allowing the user to select a 'starting record number' and a 'finishing record number' when printing.
I have a spreadsheet that feeds from a master list in excel, from over 5000 records.
I need to print the s'sheet with any given indivdual record's information at any given time.
Individual prints are fine. However if I wanted to print from record number 1500 to record number 3000 it would take me all day.
Is there a way I can set up a macro so an option form pops up? allowing selection of "From record" and "to Record" ?
The added piece I now need to put in is how many times a cell value is changed in a day (I will add to a macro to return it to 0 at the end of each day). The way in which I would approach this is by using a combination of a cell_change and count function. Could I ask somebody to advise if this is the best way?
View 7 Replies View RelatedWhen a cell changes i would like the cell in the next column to record the date of this change. After achieving this i want to set up conditional formatting to compare this date to the current date and if they are the same then change the original cell's colour to show it changed today!
I started using the code below (from ozgrid) which was working fine BUT my cell value in column A is actually taken from a different column. when the different column cell value changes my cell in A changes but the script below does not seem to see this as a change. it only records the date if i physcially change the cell in A rather than another cell prompting it to change.
based on my overall objective and my plans to date please can someone suggest a solution?
current code below....
I have an Excel worksheet full of data. I would like to search in a specific column for cell that contain "xyz". If found cell = xyz, then I want to delete the whole record or row.
Is there a formula or option to do this?
I am currently looking at each record and deleting it manually. With a worksheet of more than 2000 records, this become a huge task!
Could it be possible to "sequentially offset" a vlaue, say, of A1 such that, for instance, when A1 changes from "5" to "6" (via formula, not direct input), A1=6 and, say, B1=5, and so on? You know, like keeping a value record of A1, one step back, in B1.
This would be UNBELIEVABLY valuble to me. Thus, would I be in eternal debt to he/she who would resolve this here.
I'm working on a spreadsheet that has a counting number value on the cell L2 and a score on cell L4. These cells will be changed during the day, L2 always increasing, but not with a uniform increment. The interval between the changes also varies. Each time L2 changes, L4 may or not change. I'd like to keep the record of the changes on L2 and L4, each one in a row, together with the date and time when the changes where made to L2 and only add one new row if the L2 value has changed. The records should start from line 6 on...
See the attached file for more details. Currently I'm doing the updates to the record rows manually, I'd like to make it automatic every time the value of L2 is changed from the previous value to a new one.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$L$2" Then
If IsNumeric(Target) Then
'Stop any possible runtime errors and halting code
On Error Resume Next
'Turn off ALL events so the Target * 2 does not _
put the code into a loop.
Application.EnableEvents = False............
The below code works fine except when there is no recorset found then i want to display 0 in the cell but its not doing it. The code just leaves it blank if no recordset found.
[Code].....
What I'm trying to put together is a sheet where i work out how much margin i will be making on a sale (which i done). The bit I'm stuck with is the recording the data on a separate sheet.
What i have done so far.
I created a Macro button to insert and copy cells C6, C7 & C8 (the bits i need). Which works but it takes the formular with it so everytime i chage the figure on sheet 1 it changes in sheet 2.
So what i'm asking is there a way to mirror what is display rather that copy?
I am trying to create a continuously updating record or log of the numbers in a particular cell that has numbers that change frequently. I want every number that was every in that cell recorded in a table or column so I can analyze those numbers. Please note that I do not have much VBA experience.
View 9 Replies View RelatedWhat i would like to do is record a cell on sheet1 in sheet2 when it reaches a certain number i.e 2. The cell in sheet1 changes all the time but sheet2 must not refresh. Is this possible with a formula?
View 9 Replies View RelatedI am looking to identify when
a) New comments are entered in cells
b) When changes are made to existing comments
c) New cell entries
d) Changes to existing cell entries
I have mocked up a typical example between sheet 1 and 2. I have found tools that will identify cell entry changes (i.e. c & d) but not on cell comment changes (i.e. a & b).
Im trying to record a macro that will enter a formula in a cell each time I run the macro
Drop the Lowest2:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-MIN(C5:L5)-SMALL(C5:L5,2))/(COUNT(C5:L5)-2))*Scale!$B$2*10)
Drop Lowest1:
=IF(ISERROR(AVERAGE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10),(IF(ISERROR(AVERAGE(C5:L5)*Scale!$B$2*10),"",AVERAGE(C5:L5)*Scale!$B$2*10)),AVERA GE((SUM(C5:L5)-SMALL(C5:L5,1))/(COUNT(C5:L5)-1))*Scale!$B$2*10)
I know I can write the formula in a simpler way, but I was asked to make it like this.
The problem is that when I enter the formula in the cell while recording. I got a message: "Unable to record" so this part can't be recorded