Highlighting Cells If Specific Data Appears In Adjacent Cells
Mar 18, 2014
I have a couple of spreadsheets that has several columns each containing several hundred thousand rows of codes. To quickly analyze this data, I am trying to come up with a conditional formatting formula to highlight the respective cells when specific values occur next to each other. A particular code will show up in many cells, but the code that is the respective adjacent cell is always different. I need to know when row A contains, for example, '9928559' and row B contains '36415RT'.
View 3 Replies
ADVERTISEMENT
Feb 11, 2009
this seems like such a simple thing to do but I just cannot suus it out. Basically I want to Highlight a cell depending on another cells value:
ie. I have a value in Cell A1 and a value in Cell B1, in Cell C1 I have an IF statement that dsplays the word 'NO' if the values in A1 & B1 are not equal. In Cell D1 I have the word Fault. I would like to highlight Cell D1 if the Cell C1 displays the word 'NO'
View 3 Replies
View Related
May 16, 2009
I have Column A with 380 cells filled in with numbers (A1-A380) i.e. numbers like..456758, 567848 etc etc
Column B has a subset of those numbers, maybe 80-90 or so (B1-B90).
What I want to do is either highlight the numbers in Column A that also appear in Column B in yellow, OR delete them from Column A altogether
View 8 Replies
View Related
Sep 25, 2013
Any method for colour highlighting a row (specific number of cells) as and when any cell of that row is selected, and return to normal when deselected.
View 4 Replies
View Related
Jun 7, 2013
I'm having issues with conditional formatting. I used the INDIRECT formula to highlight specific rows.
For example, column A has the days of the week. I wanted the whole row highlighted if it was a Saturday. I used:
=INDIRECT("a"&Row())="Sat"
What I want to do now is to highlight specific cells (Columns F and G) if column A has Friday in it. I do not want the whole row highlighted if it contains Friday.
View 4 Replies
View Related
Jul 20, 2009
On the attached sheet I am trying to indicate that enough data has been entered by highlighting the entire column. Any 3 cells in Rows 7-13 and 18-36 will be filled in with any of the values from cells O6-O11 (hidden). When the total = 6 i would like to highlight the entire column to indicate it has been completed.
The aim is two fold: to ensure that the correct number of points is allocated in each race, and secondly to indicate which column is the next to be filled in (as human error sometimes misses the column and adds the points to either the previous or next columns). You could call it idiot proofing the sheet. At present i have the cells in Row 37 conditional formatted to show this, but would much prefer the entire column to highlight.
View 2 Replies
View Related
Jan 24, 2014
I'd like to search for a specific value like (1) in an array of cells like (A:A). The result needs to be the average number of a different column (B:B) in the same row as the searched value (1). And the resulting number should be rounded up to the nearest 10.
Example below: (Find value = 1 in A:A) ... 1 is found 4 times.
A | B
1 | 295
3 | 123
1 | 400
5 | 425
1 | 354
1 | 400
In the example above there are 4 (1's) found. By adding all the values in column (B) of the same row, we get 295+400+354+400 = 1449, dividing that in 4, the average is 362.25, rounding that up to the nearest 10... THE RESULT I'M LOOKING FOR IS: 370
View 6 Replies
View Related
Apr 14, 2014
I am trying to find a solution for highlighting cells in a column that are repeats, ie. >3. I also need these cells to only be highlighted if the adjacent cell in the next column contains specific text. I have tried using conditional formatting with a countifs formula to no avail.
View 2 Replies
View Related
Apr 4, 2013
I have a list of data:
2
140
1
660
10
140
0.92
660
2
130
0.18
660
4
510
0.44
820
4
510
I want to have a formula that finds the sum of the values in Col 1 (Qty) for the rows that equal, eg: 140, in Col 2 (Product) So that I can have a list of Products of the Qty that relates to each product. (there are products in increments of 10 from 10 to 920, that is, 92 products)
View 2 Replies
View Related
Jan 30, 2014
Any method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.
View 14 Replies
View Related
May 17, 2014
I have a large compilation of data on an excel work sheet (I am using 2013). All cells relate to each other by means of dates, names, and other numeric values such as stock numbers, id numbers etc... In one column some stock numbers are repeated. I would like to know if there is a way to set up the program on this worksheet to highlight all repeating stock numbers, not only in the column but across the cells as well, in lieu of doing so manually.
View 1 Replies
View Related
May 23, 2014
I am trying to build a staff roster. The staff rotate over a 4 week cycle. the name of the staff member, and their shift needs to be looked up from the key then matched with the particular week. the name and shift then need to populate specific cells.
I have attached the worksheet so you can see what i am trying to achieve.
View 2 Replies
View Related
Mar 26, 2007
I need to highlight a cell when its value exceeds parameters based on the production line it comes from. If the line is K11, then i need this cell to highlight when its value is either < 0 or greater than 221. If the line is K21, than it needs to highlight when its value is <0 or greater than 474.
View 9 Replies
View Related
Apr 18, 2006
I'm using this formula (in conditional formatting) to compare a cell with the previous cell (e.g. E11 compares to D11). If it is higher/ lower/the same, E11 is coloured accordingly.
=MATCH(E11,Grades,0)<MATCH(D11,Grades,0)
=MATCH(E11,Grades,0)>MATCH(D11,Grades,0)
=(D11)
However…. sometimes the data is not in D11 but C11 or B11. How do I tell excel to first of all look in D11 and only if the data is not there, to look in C11, then B11?
View 2 Replies
View Related
Mar 27, 2013
How to populate data in non-adjacent cells from information that is stored on another sheet. Sheet 1 is essentially setup to be visually pleasing to the user, but the data stored behind it is on another sheet stored in a more logical manner. I would like to auto-populate the nicely formatted sheet with information from sheet 2 when an item from a drop-down list is selected. I have found quite a few ways to do this using offset, etc. but in my case the values will be pulled into cells that are spread around the design sheet in no set order.
View 3 Replies
View Related
May 26, 2006
In column A, I have unit numbers. Column B is blank. I need help copying the unit number in Column A down in Column B until a new unit number appears in column A. For example, in A6 is unit LS2, A12 is unit LS24, A17 is unit LS34. I would like to be able to copy A6 into B6:B11, A12 into B12:B16, and A17 into B17:B22, etc.
I have the code to copy A6 in to B6 and down, but I don't know how to make it stop at A12. Attached is also an example.
View 4 Replies
View Related
Feb 5, 2008
For the first grouping of data (Ex 1) I need to do the following; if the value in column J is less than 49 then delete the data in the corresponding row from column E to K. For the second grouping (Flx 1) if the value in column R is less than 49 then delete the data in the corresponding row from column M to S. I would like to be able to do this for all groups all the way to group Flx 5. All my sheets are set out in this way but I am not sure if I can do this using a formula or a macro (which I am not very experienced at writing).
View 3 Replies
View Related
Feb 19, 2014
I'm trying every conceivable angle I can think of in vba and coming up empty. What I have is a spreadsheet similar to this:
Column A Column B
1. 1234567
2. 2345678
3. 3456789
4. 4567890 A0001
5.
6. 9876543
7. 9876543
8. 9876543
9. 1234578
10. 3456789 A0002
What I would like to do is to fill in B1:B3 with A0001 and B6:B9 with A0002. The number of rows in each set of data will vary, but the identifier (A000X) will always be located in the row in column B adjacent to the last entry for that set of data in column A and there will always be empty cells between data sets. The number of data sets will also vary.
View 5 Replies
View Related
Feb 10, 2009
I've got six columns with drop-down lists in every cell. I don't want column six to be selectable if the first five drop downs don't have data in them too.
Equally, I want the data in column six to be undone if columnns 1-5 are then unselected. Can I do this?
I know that using data validation or VBA is an option but I'm not sure exactly how to set it up this way. I also don't want anyone screwing with what can and can't be entered according to these rules - I want to keep it watertight!
View 9 Replies
View Related
Jan 5, 2014
I have a workbook with 30 worksheets. Each sheet has 84 rows of data (start in 15 columns (A to O). I would like to create a summary sheet that only shows the most important data from each sheet.
The summary sheet would have 12 lines of headers and formatted crap at the top.
The Summary sheet header columns would be:
Site (A), Date (B) Health (C), Status (D), Critical (E), Task (F),
Dependencies (G), Owner (H), T-Date (I), Task Date (J), Mitigation Date (I)
The data in the sheets are not in that order, of course.
That
1. puts the name of the sheet I am copying from in column A
2. the deadline date in Column B (that date is always in C10 of each worksheet)
3. and copies Cells from Column A,B,G,H,I,O in any row in which the value in A is not "good" into columns C through H. I would like to paste those rows into the summary sheet. I have code that loops through the sheets and rows in each sheet to find the rows to copy. I can copy cell values directly from the active sheet to the summary sheet, but because I am copying a cell at a time, it takes 7 minutes. Yes I am impatient :)
Here is the code snippet where the copying is done:
Dim sh As Worksheet 'current worksheet
Dim DestSh As Worksheet 'worksheet in which to paste summary
Dim Last As Long
Dim CopyRow As Long 'row to copy
Dim LastCopyRow As Long
[Code] ........
ExitTheSub:
Application.Goto Sheets("KMARollup").Cells(1)
End Sub
I think there must be a way to use ranges to build an array of cell values and paste only once but I am lost here.
View 2 Replies
View Related
Nov 17, 2009
I have 2 columns of data and want to be able to merge as follows
COL A COL B
4123567 123.45
4125467 900.56
4356456 456.32
need to get result of
COL a
4123567
123.45
4125467
900.56
4356456
456.32
View 4 Replies
View Related
Jun 14, 2008
I am trying to merge text, in four adjacent cells in the same row (say cells A1,A2,A3 and A4), into a single cell (say cell A5).
I would like a comma or full stop and then a space between each item merged (cell A5).
The text to be merged may appear in any one of the four cells (cells A1 to A4). Those cells without text are blank.
Only where all 4 cells are blank, will I need cell 'A5' to indicate this.
The formula needs to be relative as I will need to copy the formula down the spreadsheet so that it applies to additional rows.
View 9 Replies
View Related
Apr 2, 2008
I’m trying to make my life a bit easier, by adding a few macros and formulas to the spreadsheet (Everything was done completely manually before I got here!!!).
What I would like to do is take two columns, which contain a start and end time for work shifts, and colour them GREEN once I have entered a name in the Worker column (Along side the two with the time), and also to fill a cell with a Yes or a No. I’m aware of auto conditioning, and I’ve tried to have a play to get this to work, but I just can’t work it out.
I have posted a link to an image which shows what I want. I hope I've explained it well enough!
http://img530.imageshack.us/img530/6239/excelspfk0.jpg
View 9 Replies
View Related
Aug 11, 2008
I would like a macro that when run, finds empty cells in a column within the used range and fills them with the same formula in the other cells in the same column but relative to the row.
I have a basic understanding of VBA so if someone can set me on the right track i'll have a go myself as i appreciate this would take a while to write out from scratch.
View 9 Replies
View Related
Oct 17, 2008
Code:.....
I am constantly editing this (we currently have over 100 accounts) and therefore the totals are changing.I have a formula for Total but I need formulas for the other two, based on when the cells in columns F and J are blank or have dates in them: For active, the total is the sum of all numbers in column M but only when there is a date in column F and a BLANK in column J. For yet to enter, the total is the sum of all the numbers in column M but only when both column F and column J are blank. At the moment, my accounts run from row 6 to row 142, with the first line of totals in row 145, however this is constantly expanding.
View 4 Replies
View Related
Jun 28, 2008
I have multiple sheets cataloging multiple vehicles' mileages in multiple areas (one sheet per month). I want to reorganize this data by Vehicle ID rather than month.
Here is an example spreadsheet to help explain
I have attempted this by writing this formula ...
View 3 Replies
View Related
Nov 12, 2009
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
View 9 Replies
View Related
Feb 15, 2007
I am trying to use a MIN() function on a row of numbers; the row is not completely full (some days data was not recorded) and when I have a blank cell, the result in the MIN() frame is 0.0. This is unless of course I have a negative number (which I don't, but for the sake of argument)
This is my current function:
{=MIN(IF(AND(MOD(COLUMN($D10:$DL10),5)=1,CELL("CONTENTS",($D10:$DQ10))0),$D10:$DL10))}
It works like:
Take every 5th column starting at the second.
Try to ignore any cell that contains 0. (This I am trying to get to filter out the empty cells; I have tried ISBLANK(), ISNUMBER() and still get nothing)
Then return the cell to the MIN() function.
That is the long winded thing of it all, basically it can be summarized as this:
{=MIN(D10:DL10}
I have also tried some other code which I was told did the same thing essentially (which it does) but is shorter and nicer:
{=MIN(IF($B$5:$DQ$5="Q*",$B10:$DQ10))}
Which will check for the proper heading on each Column.
These functions work fine with AVERAGE/MAX as far as I can tell, just not with MIN().
As a side note, none of the cells leading up to this function contain "0".
View 9 Replies
View Related
Apr 30, 2014
The spreadsheet that i use includes a table that some of the cells are displayed as blanks( include an IFERROR function ) I need to calculate the XIRR for a range of cells in a row that includes some of these "Blank" cells
View 2 Replies
View Related
May 25, 2008
I am linking several cells in Sheet1 to other sheets throughout the workbook. Sheets2-10 to be exact. The problem is when no data is in a given cell in Sheet1 a '0' shows up on the linked cell in Sheet2-10. The problem is 0 can be a correct response sometimes and if 0 is always to show up someone is going to misunderstand the task.
I just want to stop blank entries from being linked over as 0. I have a ton of cells being linked and don't want to do conditional formatting on each individual cell. Is their any easy way to stop this?
View 10 Replies
View Related
