I have a value in cell B23. I C23 & C23 is blank and numbers 0 to 18 is in cells E23 to U23I. I need VBA code that hightlights the row up to the value as indicated in B23. For example: B34 contains 4, the code should highlight the two blank cells up to cell J23.
View 7 Replies
=IF(AND($P2="Yes",COUNTIF($L:$L,L2)>1,$Y2="Market Specific"),TRUE,FALSE)
But it is not doing exactly what I want it to, and I am struggling to work out what I need to change to get it to do so.
I want the formula to do is look at a three Columns of data, and to highlight Cells in Column Y when the following criteria are met:
The Row is active (determined by the "Yes" part), that the Row is "Market Specific" and that there is more than one Row where these Criteria are met (this is where I thought COUNTIF would work, but it's not).
I am trying to set formula to change row colour when certain criterias are met the options must be availiable for all rows but Governed of one colum.
Eg. If Cell A1 = "Contacted" then row will change to colour Green,
"Left message then row will change to amber
"Not contacted" then row will change to Red
"Do not contact" then row will change blue
If these can be set as drop down menu to save operator typing them that's even better.
Once one is set I can copy the formula into the remaining collums.
I have read other threads dealing with this issue, but none seem to help me! I have a workbook with two worksheets, both with the same number of rows in them. I want each cell in worksheet1 to be compared to the cell in the same row and column in worksheet2, and if the worksheet1 cell is greater in value then the cell should turn red. I have tried the previously mentioned tactic of selecting the whole column in worksheet2, assigning it a name, selecting the column in worksheet1, and applying conditional formating based on the name. However, the result is always that the whole column in worksheet1 turns red, regardless of whether the cells are greater in value or not.
View 9 Replies View RelatedI have 3 data series, which are each futures contracts. Each of these futures contracts (the one on the left expires soonest) has an expiration date and as that date approaches, one will need to replace it with a new futures contract (the data series immediately to its right) having a later expiration date. This process is called a ‘roll’. I am seeking to create a continuous data series, which will incorporate the appropriate ‘roll’ based on two rules.
The rules are as follows:
1) Where the volume of the 2nd contract exceeds the volume of the 1st contract for 10 consecutive intervals, roll on that date.
2) Once the roll date has been identified, roll at the specific time interval on that date that has the highest aggregate volume (between the 1st and 2nd contract).
I have laid out the original data in a tab entitled ‘original data stream’. This is how the data will appear at first. In the tab entitled ‘Roll Example’, I have shown what I would like to happen to the original data streams. You will notice that in this tab, the data series are the same as the previous tab, except that they are not aligned as before. The data series to the left ( cells B4: D808) expires ..................................
When activating Autofilter in a wide table, the only way to determine which criteria field was selected is to try and identify which drop-down arrow has become blue. When working on a laptop, it is rather difficult to identify the difference between the black arrows and the blue ones. Is there a way to have a macro or event procedure that will cause the selected criteria field (or fields) change its background color (into yellow, for example)? This way, the yellow fields will "stand out"… No more searching...
View 3 Replies View RelatedI have a file with telemetry data for n individuals. For each individual I have data on a number of days, over the course of a couple of years. On any given day, I have from 1 to several "fixes" for an individual. Fixes range from 1 to 3, with 3 being the "best". Note that I do not necessarily have data for all individuals on a given day. What I want to do is extract the row with best fix for each individual, on each day for which I have data for that individual. If there is more than one best fix for an individual on a given day, then I want to choose the first best fix (temporally). Right now my data is set up with columns:
INDV DATE TIME FIX ....other attributes
INDV = unique code identifying the individual
The data is sorted by INDV, then by DATE, then by TIME.
I figured I could do this using nested IF statements, where "TRUE" would be added to a new column (e.g., HIGHFIX) in the row of the first highest fix on each day for each individual, but have not been successful in doing this. I've also tried conditional formatting. Again, no success here.
I need a macro that will highlight a row based on a cell criteria. I have used conditional formatting but there is a lot to format and has dramatically slowed the sheet down. However, I know nothing about macros/vbas.
E.g. If it states JI sent then I want the whole row to be highlighted light green.
App form needed = peach
Waiting list = light blue
Cancelled = red font strikethrough
DNA = purple font strikethrough.
Also - it could well be that in the row it has App form received, JI Sent and DNA all in 1 go. So ideally I would want that row to be highlighted light green with purple font strikethrough.
Booking Status
JI Status
Date JI Sent
Attendance
Cancellation date
Chargable / non chargable Cancellations
Chargeable / non chargeable DNA
Date taken
Time taken
Event Title
Start Date
Time
App form received
JI Sent
[Code] ......
I have a large file of invoices, and some have been paid. The problem is, sometimes it is not paid against the correct invoice, so I have a lot of credit and debit that should offset each other. I want to go through the sheet and highlight all the rows that has a matching ID and an a 0 value when total amount column was added up to another row. I would like it so that it highlights a different color for every pair of offsetting amount, but the same color highlight is fine too.
View 2 Replies View RelatedI have a table with 3 columns with an unkown number of rows (text, date, date) that is being imported daily.
I want to create a 4th column with dates starting from today and each subsequent row be one day earlier. I want to look at 30 previous days.
I then want to count the number of rows (looking at column 1-3) with the following criteria:
Countif column2<= "date in colum4" AND column3< "date in column4"
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
E.g i have this code
ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.
View 1 Replies View RelatedI am trying to make an "intelligent" auto filter that with filter with increasing restriction until a certain criteria is met.
The list runs from A5:G20. In coloumn G is the number of hours associated with each event. And in A1 I have the percentage of items showing/whole list so it I have 15 rows on the list, and I filter so that only 5 are showing, cell a1=33%
How can I make a macro that will autofilter until the the a1=5%
Like having filter criter = equal or greater then 1 hour,
if a1 > 5%
Then criteria + 1 hour
If A1=<5%, then stop.
basically a seed criteria of 1 hour, adding 1 hour until the value in a1 = 5%
I am trying to set up a sumif statement with two criteria where if the second criteria is true, the total in the sum range returned is divided by two.
I currently have this:
=SUMIF($G$6:$G$41,"digital",I6:I41)
I want to add in "digital/creative" as a second criteria (from the same criteria range) but I only want 50% of the result of these to be totalled.
is this possible?
Macro for AverageIFS, with multiple criteria in the same criteria range....
View 9 Replies View RelatedWhat I would like to do is return the total qty of items on the 'QUOTATION' tab that are upholstery and this having been determined by whether there is a Y (for Yes) or N (for No) against the equivalent item in the 'BASIC_LIST' tab.
So in the below example, the total of Upholstered items would be 182 as items 'ST-11' to 'ST-13A - Tray' do not have a 'Y' against the matching item references in the 'BASIC_LIST' tab.
I would like this Y/N column to be the determining factor as opposed to any other criteria or filtering. (Also the Items column in the 'QUOTATION' tab is only ordered in the way it is for ease of view for this query!)
Picture 2.png Picture 3.png
SUM VALUES OF ITEMS THAT HAVE ALREADY MATCHED ANOTHER CRITERIA.xlsx
I can't seem to get this to work the way I need it to do. Let's say I have a 2 sheet workbook. Sheet1 is called "Order" and Sheet2 is called "005". On sheet2 (005) I have 9 columns that are populated. Row 1 is my header row and then row 2+ is all my figures. On sheet 1 (order) I want A1 to look at sheet2 (005) in F1:F10000 to find all cells that have "N" in the cell. I then want it to return value in column A that corresponds with the "N". I want it to list all the ones from Sheet2 (005).
Sheet 1 (order)
A
052611
052806
052843
Sheet 2 (005)
__A_____B____C_____D_____E____ F
052611_________________________N
154272_________________________Y
125485_________________________Y
052806_________________________N
125478_________________________N
052843_________________________N
how I can write is in a formula?
I have a macro I use to filter a large file that I receive each day. Each row of data has an identifier in column C, and this is where the macro looks to determine whether or not to remove that row. Currently, my macro will only remove rows in which column C contains any of the following strings: "B0A0", "B0MH", or "SRB". This part of the macro looks like this:
[Code] ......
I want to add additional functionality to this macro. I will always need rows removed where the identifier in column C contains one of these static strings, but there are times when there or other strings that I would like the macro to remove. Ideally, I'd like to have a range on a worksheet where I can type a string of characters in each cell, and the macro will look to this range when removing rows in my large file. How can I modify this existing code to incorporate this?
Say, for example, that I want to designate the range B2:B50 on Sheet 4 as the range where I want to type these strings.
I've got a sheet where I've got products in one column.
The second criteria is "week #" for which the row remains consistent but the column varies e.g. "week 8" could be in cell L12 today and in L18 tomorrow.
I would like to return the value of the cell at which a particular product and week intersect. e.g. if "product a" is in B20 and "week 8" is in L18, I want the value of cell "L20" returned.
Product to determine Row #, Week to determine Column # for returning value.
If they interest multiple times, I would like to sum up the values intersecting.
Neither product nor week # is unique in the sheet.
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)
-Didnt get thru this bit to start building on the Function! keep giving me #Value!
I have a spreadsheet which lists letters issued, the date issued and the potcode. I need a formula that counts, on a weekly basis, the number of letters issued to postcodes in Sutton and Bexley.
I have a count sheet set up on one worksheet and the list of postcodes applicable to each area are listed on another worksheet. I have been playing around with sumif, sumproduct etc, but these don't seem to work as I am pointing the formula to a list of postcodes and not an individual postcode. For example, the formula for one week needs to tell me, the number of letters issed to Sutton between 26/07/08 - 01/08/08. This is what I tried -
=SUMPRODUCT(('MU63 NC'!$F$2:$F$10>=COUNT!B$2)*('MU63 NC'!$F$2:$F$10<=COUNT!B$3)*('MU63 NC'!$D$2:$D$10=Sutton!$A$2:$A$50))
But I get #N/A - if I change the last refernce to a specific cell instead of a range it works, but this will make the process very lengthy as there are lots of postcodes!
I'm using the code below to color every row where "MAIN" and string
starting by "FFR0" coexist, but it doesn't work
In a workbook. I have a cell with a hyperlink to different sheet in the same workbook.
I want to make it so, that when someone clicks on the hyperlink and is taken to the cell on the sheet i linked to, that the whole row of data highlights.
The highlight can only be turned on when someone clicks the hyperlink.
Does this require VBA script?
I am using Excel 2003.
I have a spredsheet with names going down column A and then a lot of data in the next 20 columns. It is sometime a challenge to see for which name the data is associated with becuase I have to look across a long row...
Is there a way in Excel, that when you click on a cell, it would HIGHLITE the row that you are in at that moment?
Is there a script to add a boarder or highlight every 5th row in excel?
View 15 Replies View RelatedI need a code that will highlight every other row in a some color.
I would like the macro to start in row 7 and use column A as a reference to know when to stop (last populated row in column A is where it needs to stop).
The rows that get highlighted can all get highlighted in the same color, and the entire row can be highlighted.
The twist to this is that there are hidden rows in the range and I want the macro to disregard those.
I have a list of data that has (Col A) item, (Col B) lot# and (Col C) quantity. In Columns DEF I have the same but column E (Lot#) is blank. Is there a formula I can use that would match Item and Quantity (A and C) with (D and F) and then drop the Lot # form column B into column E if there is a match?
Item
lot
Amt
Items
Lot #s
Yds
10429.50.0
N49950
2.5
0201.019FLAME.0
T37530
9
[code]....
I am working on closeouts and I would like the late due dates to be highlighted in three ways: past 30 days, past 60 days, and past 90 days. Also, I would like the highlighted to be disappear when the form is sent..
View 3 Replies View Relatedi have got a spredsheet, with clients names on it and in cell Q i have the clients account number, when iam looking at the clients name i also want to see the number, is there any way to highlight the cell i am on.
What i mean isusing the arrows keys i want to make the row highlighted, is there a way to do this
