Free Microsoft Excel 2013 Quick Reference

Conditional formatting based on value in another column

Hi,

I am trying to create a conditional formula to highlight cells with a 'W' in column M based on the name in column N (in the attached example I've used the name Bob).

So in other words, if there is a 'W' in column M and on the same row the name 'Bob' is in column N, then I would like the cell containing the 'W' to be filled.

I've tried a few times to make this happen but no luck so far.

Thanks in advance!

wace


Related Results

  1. VBA method of copying values from RAND() to a range based on values in another range
  2. Set Color of Cell Background Based on value in another cell
  3. Copy values from one column based on values from another column
  4. Lock a Cell value based on value in another cell - (W/O involving VBA Code or Macro)
  5. Excel Locking cells based on value in another cell
  6. Excel Locking cells based on value in another cell
  7. Selecting cells based on data in another column
  8. I need a macro that will sort my information based on values in a column
  9. Conditional Formatting based on data in another spreadsheet
  10. Format a column with two different formats based on value in another col
  11. Calculate average in a column based on criteria in another column
  12. Conditional Format based on value in a column
  13. Conditional Formatting based on values in multiple cells
  14. Populate excel sheet based on values in a separate sheet
  15. Macro to Group Rows Along a Range Based on Values in a Column
  16. How do I sum dollars in one column based on dates in another?
  17. Conditional formating based on text in adjacent cell
  18. Conditional formatting based on names in one column and amounts in another.
  19. VBA insert formula based on value in another column and loop rows
  20. Format Cells Based On Values In Pivot Table
  21. Input data or Lookup data based on value in another cell
  22. How to populate each row value in a column based on the corresponding row value in another column?
  23. Adding numerical values based on multiple values in another column
  24. Excel 2007 Display value based on values in another column range
Take a look at the attached workbook. Columns F, G, H contain the percentages which correspond to the options selected by the user in Columns C, D, ECells F4, G4, H4 contain the maximum value of these percentages.Columns A and B are conditionally formatted so that the option and percentage cells corresponding to this maximum percentage are highlighted.Pressing the F9 key recalculates the various random numbers on the worksheet, and the appropriate option and percentage cells are again highlighted
I need the "Chosen SPP" field to be locked in once data is added to the next column. For example, once the user has acheived a result in C5, that result will be locked into C5. The user will then move on to Column D, select their options and acheive a result in D5. That result will be lock into D5. The user will then move on to Column E... etc. etc.

Originally Posted by ConneXionLost The RAND() function will give you a new set of data every time the spreadsheet recalculates. As long as you're relying on the RAND() function as the source of your data, it will place the problem of "locking in" the selected values well beyond the capabilities of any LOOKUP formula or conditional format solution. Especially if you intend to do this with multiple examples.

I recommend you begin a new thread with the suggested title of: "VBA method of copying values from RAND() to a range based on values in another range".

Hopefully one of the VBA experts here can help you.

Good luck,

Set Color of Cell Background Based on value in another cell::

Hi,
Here is my example. I need to set the fill background of cell b4 based on the value that it is c4. Obviously you can't do this using conditional formatting because it would only work for c4. It looks like this:

b4 - blank cell
c4 - "complete"

I need b4 to be green if c4 = complete and i need it to be red if the cell is equal to something else.

Thanks

Hi Guys,

I'm trying to solve following issue:

Lets say I have two columns

a 10
b 10
c 20
d 30
e 40

Now I want the value a to be repeated 10 times (as the value in column 2 is 10) in colum 3 (new column) and then b 10 times under a the value a in column 3, and then c 20 times under b, and then d 30times under c etc. So it needs to check how big the number is in column 3 (Thus jump also under the last value in column 3 and paste there) and repeat it as many times as needed and jump to the next value in column 1 and repeat it.

Looks like this:
a
a
a
a
a
a
a
a
a
a
b
b
b
b
b
.
.
.
.
.
.
etc.

A similar code can be found here: http://www.ozgrid.com/forum/showthre...494#post616494

But this code repeats only 4 times and has a static value. I want it to repeat, based on values of another column.

Thanks a lot for your help!
NangNang

Hi

This is James from Singapore.

Sincerely need your help here as I have been trying to figure out how to solve this problem.

I am doing an assignment for an accounting project.

I have attached the project sample in this thread:

Attachment content and MY QUESTION:
1) Column B shows the calculated ratios

2) Column D and E allow user to "copy data" from Column B

3) To avoid the data in Column D and E from being changed accidentally, I have created another drop down selection with 2 options " to protect data now" and "let it change"

4) the thing is, I don't know how to lock the cell value if the user has selected "to protect data now"
Can any kind souls here advise?

Thanks.
Question on Locking Cell based on value in another cell.xlsx

Hi all

My code locks another cell based on value in another cell. I got it working just for 1 row but i would like to do it for the subsequent rows (E10, E11, E12, E13...)... any help??

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If [E9] = "A" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Protect ("")
End If

End Sub

Thanks in advance!

Hi all

My code locks another cell based on value in another cell. I got it working
just for 1 row but i would like to do it for the subsequent rows (E10, E11,
E12, E13...)... any help??

Private Sub Worksheet_Change(ByVal Target As Range)

If [E9] = "A" Then
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = True
ActiveSheet.Protect ("")
Else
ActiveSheet.Unprotect ("")
ActiveSheet.Range("G9:K9").Locked = False
ActiveSheet.Protect ("")
End If

End Sub

Thanks in advance

I'm trying to make a formula within a cell that selects other cells based on cells in another column containing data, is this possible? An example below.

----A-----B
1 393 474
2 378 428
3 353 ----
4 336 ----

The final product would work like this.
B1:B2 have data, so I would need a cell formula to select A3:A4 automatically and sum them together. So, once I enter data into B3, A3 would automatically be taken out of the cell selection.

I have Excel 2010, thanks for any help!

I need a macro that will sort my information based on values in a column. I have numbers 11, 12, 13, and 14 in column D. I need a macro that will check column D and move all rows with the value 14 (I want the information from A B and C moved with it) to a new table, move all rows with value 13 to a new table. and move all rows with the value 12 to a new table. Thanks very much.

I have a column of names. I am trying to conditionally format the names (Green or Red) based on data from another spreadsheet in the workbook. Is this possible?

See enclosed file sheet1.

I have a column (Calc4) where i want to format this column with two different formats based on the values in Calc1 column. For the different formats see enclosed file. Is this even possible?

I want to calculate the average of numbers in one column based on crieteria
in another colume.

If the value in column A = x, sum and average the corresponding values in
column B

Hello,

I am working with the attached example and would like to make all cells highlighted if row A is = to 2 or another number.

I have highlighted all cells and in the formula in conditional format I entered: if($A3=2), but it isn't working. Any idea what I'm doing wrong?

Thanks,

Karen

Im trying to colour a cell based on data in two cells. E.g.

a1 and a2. both have to number 1 entered. So i would like c1 to be green, BUT only when both cells have 1 entered. two conditions does not work, because it only requires one cell to have 1, to turn c1 green. can anyone help?

Thanks

I am trying to populate an excel sheet ('Actuals') based on values in another
excel sheet ('Budget') in the same file. When the Week Number is chosen, from
drop down list (based on 'Budget') I need certain columns to populate. For
example if user chooses Week 1, I need Column C, F and J on the 'Actuals'
sheet to read Y but if week 4 is choosen, Coumns D,I, M and N will be Y.

How can I make this happen?

Thanks

I need a macro to group rows based on values in a specific column.

I have a dashboard with data from row 21 to row 5000. The cells in column GM have the following values: "Hide", "Not Hide", or are blank. The data is sorted based on values in columns A, B, and C. As a result, there is no inherent order to the "Hide" or "Not Hide" values in the cells in column GM.

I need a macro to go down column GM, and group any row where the value in column GM is "Hide". In some cases, only a single row will be grouped with a "+" sign. In other cases, consecutive rows will be grouped as such.

I want to go with grouping rather than filtering because with grouping, data hidden along the range of the dashboard is more readily accessible.

Any help would be greatly appreciated.

Thank you very much.

How can I sum the total numbers contained in one column based on dates in
another column?
Example: I need the total of 5 units sold int the month of January 2006. I
have the sale ammounts in one column and the closing dates in another.
What forumla would I use for this?
Thanks in advance for any help.

Hello.Can somebody tell me what the formula is to format one cell based on text in another cell eg If text in v 42 contains ! then format text color in u 42.Thanks in advance

For my example, in column B I have a list of guest names. These names will repeat based on their entries.

In column C I have amounts next to their name.

If the total of all amounts next to their name equal $1,000 or more I want all the rows that their name is in to be highlighted.

I attached an example.

I appreciate any help creating a conditional formatting to do this.

thanks,
Nick

Hello,

I am a bit of a novice with VBA and would appreciate some help please.

I am trying to write a macro to populate column B with a formula based on the value in column C, then loop through until last row. I've modified some code I found but can't get it to work properly. Basically, if the letter "P" appears in a cell in column C a formula is to be placed in column B of the same row, but if the letter "F" appears then an alternative formula is to be entered. Also, if the cell in column C is blank then the code should skip to the next row without altering that row. Hope this makes sense!

Sample code follows and test file is attached.

	VB:
	
 CalcColB() 
    Dim x As Integer 
     
    For x = 3 To Cells(Rows.Count, "A").End(xlUp).Row 
        Select Case Cells(x, 3) 
        Case Is = P 
            Cells(x, 2) = "=RC[-1]*100" 
        Case Is = F 
            Cells(x, 2) = "=RC[-1]*1000" 
        Case Else 
            Cells(x, 2) = "=""""" 
        End Select 
    Next x 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for any help.

Dear Experts,

I am faced with a small challenge whish is beyond the scope of my current level of knowledge.
I have a pivot table and next to it I added some calculations which are based on data from the table. In my "real" file, the cells next to the PT are formatted with conditional formatting based on other values in the sheet (see attached sample file).
What I now need to do is add borders to the cells in columns “D:G” only if the cells in “B5” and downwards are not empty.
I have tried doing it with conditional formatting but there are some other rules which contradict this setting.
The idea is to make the cells in columns “D:G” look like they are a natural extension on the PT.
However, the table can grow or shrink (in length) and I want to see borders only around cells that are on the same rows as the data in column “B” (staring with B5).
I think that whatever solution you can provide (if you can...) should be based a workbook_change event, because in my “real” file, the length of the table is changed whenever I select a different “page” of the PT.

I thank you in advance for your time and attention

I have an Excel 2007 s/sheet with two key input columns. One has the value "New" or "Stock" from a dropdown list. The other selects an item name from a dropdown list (or enters a new item name) based on VBA code found at http://www.contextures.com/excel-dat...ation-add.html. This code automatically adds the new item name to a data validation list.

My issue is this: If the selected value is "New", I wish the user to input data into three other columns (eg, price, description, weight). If the value is "Stock", I wish to look up values for those three columns based on the item name with the data held in another worksheet. That is, I want to conditionally do a VLOOKUP or have the user fill in a cell, based on the value "New" or "stock (data value in another cell).

What is a good way to do this?

How to populate each row value in a column based on the corresponding row value in another column?

We are pretty new at macro thing. We would be appreciated if someone here show the code sample on how to do that with detailed procedure!

Please help
I am trying to add numerical values in one column based on multiple values
in another column.

A B
Red $24
Blue $16
Green $21
Blue $15
Green $12

I would like to add the values in column B for all rows that have a value of
red and green in column A. Thank you in advance for your help!

I have an amortization schedule of my mortgage. I want to show the date when
I can cancel my PMI, the date will change based on how much extra principle I
pay each month. So, I want to search a column for a specific value. Once
that value is less than a constant, I want to pull another value and display
it from the same row that first meets the criteria. I want to search a
range of values (I1:I20). I want to locate the row where the value in that
"I" column first goes below 100,000. Now that I have the row, say I10, I
want to display the date value in that row, say A10.

Thankx