# Excel 2010 :: If Any Row Is Negative Highlight All Rows That Have That Value As Well

Jan 15, 2014
I'm looking for a formula in column F that will return True if there are any negatives or blanks in E for any of that item A?

False if variance is blank or all off that item has no variance?

Excel 2010

A

B

C

D

E

F

1

item

size

ordered

shipped

variance

formula?

[Code] .....

View 1 Replies
ADVERTISEMENT
Mar 16, 2013

I have a problem with adding time values. The idea of my spreadsheet is to count time worked, and then calculate any variance from the normal shift length (i.e. 7h 30m). My formulas work fine until I work less than 7h 30m in a shift.

Example:

A1=start time

A2=end time

A3=IF(A2="","",MOD(A2-A1,1))

A4=IF(A3="","",(A3-(TIME(7,30,0))))

When the duration is over 7h 30m, A4 correctly returns the value of extra minutes. But when the duration is under 7h 30m the return is #####.

PS A1:A2 are formatted in a custom format hh:mm and A3:A4 are formatted as h:mm, and I'm using Win7 & MS Office 2010.

View 5 Replies
View Related
Aug 25, 2014

Please find the attached MS Excel 2010 file <average set.xlsx>.

There is set of positive set & negative set of values available in the Column A. The values are plotted against Column A in Column B. Light green are positive sets and light yellow are negative sets.

Now I want to calculate the average for the positive set & negative set of values as shown in light majenta in the cells F4:F9. Also all majenta cells to be plotted by formula.

The Column H and Column I also same as like above , but here negative set of numbers starts first.

View 4 Replies
View Related
Apr 24, 2013

EXCEL 2010. I have a column of negative and positive times (eg. -00:52, -03:07, 06:02) in custom hh:mm format, and would like a new column rounding these times to the nearest negative or positive hour (with just the whole hours displayed), so I need a formula that would give me (for this example) -1, -3, 6, etc.

View 9 Replies
View Related
Mar 11, 2014

I am creating a template in Excel 2010 and am having some issues due to a combination of positive and negative values. Specifically, I have a named range of values that can have either a positive or negative deviation from zero. I need to return the value that has the greatest deviation from zero (either + or -) based on criteria in another named range I have used the following array formula with success for the "Y" range:

{=IF(PRB=M1,INDEX(Y,MATCH(MAX(ABS(Y)),ABS(Y),0)),MAX(Y))}

However, if there are not any negative values, as in the "Z" range, it only returns MAX(Z) for the entire range, when I would like ti to return "0.00134" (the largest deviation from zero based on the PRB range criterai. Would this be better accomplished using VBA?

PRB

Y

Z

A90B90

A90B-90

[Code].....

View 3 Replies
View Related
Jul 26, 2013

I have "Worksheet1" with Columns A and B for IP Addresses, then Column C is for a device name.

I have "Worksheet2" with a range of IP Address from F5:I260

When I enter a new device and assign it the IP addresses on "Worksheet1", I would like the IP Address to highlight in "Worksheet2"

This way I can keep track of which IP address I have used. Excel 2010

View 7 Replies
View Related
Aug 19, 2013

I have a Excel 2010 spreadsheet that contains 3 columns of (£) values. I am wanting to highlight a cell if one of these values do not match the other 2 columns. e.g below I would need the middle column to be highlighted as it does not match the other 2 cells.

25,449.47

25,451.65

25449.47

The thing to point out is that it can be any of the cells that do not match, not necessarily the middle column and occasionally all cells will not match and all need to be highlighted.

I am sure I need to use the Conditional Formatting, but cannot figure the formula.

View 5 Replies
View Related
Jun 11, 2013

In an Excel 2010 worksheet I use three columns. I need a user to be able to input a word or phrase into a cell or input box, and have Excel highlight that word or phrase every place it occurs only in column C.

Can I do that with conditional formatting? Or do I need to use a function? Or do I need a macro?

View 5 Replies
View Related
Nov 26, 2013

As per my attached file : duplicate value.xlsx

How to highlight duplicate value by using Conditional Formatting in office 2010.

Actually i Know how to use this feature in sheet "example" of my attachment.

But i want to highlight inv# 457878268 in cell E9 of sheet "My requirement" if it is exist above in cell E2 to E6.

View 3 Replies
View Related
Aug 10, 2012

Highlighed the Column and Row of the selected Cell. I have seen someone using it. Whenever he click on any cell the related column and Rows clour change. For Example if I select Cell B5. The Column B and Row 5 change its colur from B1 to B5 and Row from Column A to Column B. If this option is already available in office 2010

Column B

Row 5

View 3 Replies
View Related
Jan 8, 2013

As seen below, I'm looking for a vba code to highlight color on every Friday and through columns 1, 4, 6,9,11,14,16,19,21 upto columns 28 i.e. AH

Excel 2010ABCDEFGHIJKLMN1DateQty1Qty2TotalDateQty1Qty2TotalDateQty1Qty2Total

21-Aug-124559289374521-Aug-122721298357041-Aug-1222792338461732-Aug-121161244636072-Aug-12347918036592-

Aug-1225723358593043-Aug-12128088221623-Aug-124369158259513-Aug-1227723299607154-Aug-124096192960254-

Aug-124679386585444-Aug-122081870295165-Aug-12333751838555-Aug-124270357278425-Aug-1231793455663476-

Aug-122061336654276-Aug-1241466142076-Aug-123761939231587-Aug-123757375975167-Aug-12212203622487-

[Code] .........

View 2 Replies
View Related
Apr 24, 2013

I am trying to use condtional formatting to highlight a row in a table if the order number is NOT on another table, on a different sheet. Example: Order Number 1001 is in my table on Sheet4, Once the order has been filled is comes off my "Pending Orders" table on Sheet1, which is based on a data connection, so it updates once the connection is refreshed. I would like the row with order number 1001 in my table on Sheet4 to be highlighted letting me know it can now be removed, since I have to manually manage this table. I am using Excel 2010.

View 3 Replies
View Related
Sep 19, 2013

In column A, I need to highlight the cells green if the value is less than < the values in columns B and C, yellow if A = either B and C, and red if A is greater than B and C. But I only want the formatting to stay in column A. I'm using Excel 2010 and Windows 7.

A

B

C

Green->

444

512

482

Yellow->

364

571

364

Red->

101

99

87

View 2 Replies
View Related
Apr 4, 2014

I'm using Excel 2010 and when I highlight a range and right click 'name a range' is greyed out. I can bypass this by going to the formulas tab and defining a name from there.

Anything I can check to see why this is not an option for me?

View 3 Replies
View Related
Feb 26, 2014

I need conditional format in column Q which highlight in red, any date over 14 days old.

This column also contains texts (non-dates) and these need to be left alone.

I've experimented with a few bits of formula I've found online, but nothing has worked . .

I'm on Excel 2010.

View 3 Replies
View Related
May 29, 2013

I would like (in excel 10) to highlight cells where there is NO DUPLICATE found In Columns "E" on Worksheets 1 & 2. Is that possible ?

View 7 Replies
View Related
Jan 13, 2013

Using Excel 2010. Is it possible to change the default line thickness and fill color when selecting the ActiveSheet Target Row below?

I would like a thin border and a light grey fill - without interfering with any fomatting or conditional formatting that has been applied to the worksheet.

View 1 Replies
View Related
Jan 20, 2012

Excel 2010 Higher than past average formula?

Im using Excel 2010 and want to make a formula that will High light the number when it exceeds the past 30 days average. My information is listed vertically in row F..

View 3 Replies
View Related
Apr 10, 2013

MS Excel 2010, WinsXP

how to highlight an entire record based on the value of a single cell?

I would like to highlight all records grey where cells in a column = "closed".

View 4 Replies
View Related
Aug 16, 2013

I am using Excel 2010. I have a pivot table where I want to highlight the ENTIRE row where a certain cell value equals something.

In essence I want all the Material Subtotal Rows highlighted "Orange" But as you can tell from the picture below I am having issues with the Body of the Pivot table. I have the formula checking to find whenever it finds the word total to highlight it.

The example below is showing how I need the row to be formatted. I can get the Data (Units) section formatted no problem. It is just the other part of the pivot table that I cannot format.

For the columns from Material to SAP # I have been trying to use Dynamic Name ranges using the Offset function. However, excel turns that Name range to a range and if I filter the pivot table, it adds extra ranges to the Applies to box and starts to really mess it up.

View 2 Replies
View Related
Apr 12, 2013

I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:

Column F:

6

6

14

Column L:

3

NA

17

I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.

Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.

View 3 Replies
View Related
Jun 27, 2014

I am looking for a way to highlight groups of identical number cells in alternating grey and white. My goal is to make it clear when there is a repeating set of numbers. Below is an example of what a completed state of this would look like that I created manually. The real form I will be using this code on will have long number which is why I am looking for this added clarification. I am using excel 2010.

1

1

2

3

4

[code].....

View 4 Replies
View Related
Apr 6, 2014

How do I get the cells in column "F" to highlight all negative numbers UNLESS the following 2 conditions are met: the number in column F = "-105" AND there is an "@" corresponding in column "C". In the attached file F5 & F7 are highlighted properly according to what I want, but F6 is not (since F6's value is "-105" AND C6's value contains an "@").

View 7 Replies
View Related
Feb 8, 2011

I have one debit and one credit column. I want to higlight duplicates, with positive and negative values.

Like this.

Column A

5000

1000

10000

3000

Column B

- 10000

-5000

-3000

-2000

I know how to do the conditional formating to find duplicates when there is only positive or negative numbers, but i don't know how to match both positive and negative numbers.

View 9 Replies
View Related
Sep 25, 2012

How do I delete filtered rows without deleting the hidden rows in excel 2010?

View 8 Replies
View Related
Jun 16, 2014

calculate the number of full rows in one data base located on one sheet X to determine how many rows the macro needs to extend on sheet B (sheet B is made only of formulas for data interpretation in sheet A.

View 14 Replies
View Related
Aug 4, 2014

Abc500

abc-500

bbb300

bbb250

cac100

cac-100

ddd200

ddd300

fff-500

I want to be able to highlight data in column B where amount is identical (ignore negative sign) only where data in column A is identical.

In this particular case it should highlight 500,-500 where data in column A = ABC & also 100,-100 where data in column A =CAC.

View 2 Replies
View Related
Mar 17, 2014

I am using Excel 2010 and I am trying to hide the rows# 10 to 12 in Sheet6 and it is working perfect by clicking on radio button.

[Code].....

Now i also want to hide two more rows in Sheet7 by using below snipet but it doesn't work.

[Code] .....

View 8 Replies
View Related
Apr 19, 2012

I am working in Excel 2010 on a windows 7 machine.

I have a years worth of data by day. I want to sum the values for each week.

For example, Cell B1 is: =sum(a1:a:7)

Cell B2 is: =sum(a8:a14)

Cell b3 is: =sum(a15:a21)

Is there a formula I can write that will increment the numerical portion of the cell by 7? If I drag down cell B1, the resulting formula is: =sum(a2:a8)

I know that I can use either =sumifs or =sumproduct to compare the dates and calculate based on that.

I tried: =sum(a1+7:a7+7) but that returns an error.

Again, don't propose solutions with either =sumifs or =sumproduct since I can already write those formulas.

View 3 Replies
View Related
Aug 5, 2013

I have some code that worked perfectly in Excel 2007 but crashes Excel 2010.

The part of the code that appears to be the problem is this:

Code:

For i = LR To 2 Step -1

If Cells(i, 15) = "Delete" Then Rows(i & ":" & i).EntireRow.Delete

Next i

For info., both screen updating and calculation are already both set to manual.

View 9 Replies
View Related