Conditional Input In A Cell Based On Data In Two Different Cells And Other Values

Dec 4, 2009

I have lot of data in three columns like this. The first column is "Name", the second is "Comment" wherein I want the macro to write some comment, the third one is DOB. The problem is that the names in column 1 repeats many times. I want a macro to write in column B "either Old or Older or Oldest" based on the Name and DOB. Thus David with DOB 13 Sep 1982 be marked Oldest in Column B and David with DOB 25 Aug 1988 be marked Older and David with DOB 24 May 1990 be marked Old. Similarly William and Rita should be marked either old or older or oldest. As the data are enormous I do not want to refer the actual name in the programming. I want to call them using a variable in programming.

NameComment DOB
David 25 August 1988
David 13 September 1982
David 24 May 1990
William 24 March 1980
William 25 July 1987
William 13 August 1989
Rita 17 July 1990
Rita 24 April 1989
Rita 13 June 1988
The example file is attached

View 14 Replies


ADVERTISEMENT

Conditional Format Color Of Cell Based On Values In Range Of Cells?

Oct 24, 2013

I would like to format the color of cell A1 on Sheet 1 based on true or false values from cell range A1:A10 on sheet 2. For instance:

1. If all cells on sheet 2 in range A1:A10 were false then cell A1 on sheet 1 would be red.

2. If some cells on sheet 2 in the range A1:A10 were false and some were true then cell A1 on sheet 1 would be yellow.

3. If all cells on sheet 2 in range A1:A10 were true then cell A1 on sheet 1 would be green.

View 5 Replies View Related

Conditional Formatting Columns Based Off Cell Input

Jun 24, 2014

Instructions are in the attached excel as well. Please see attached

If a person puts in a price in column A, that results in column B "Everyday Price Per Unit" being greater than column C "NDP", then highlight column A cell input red.

=Column B>Column C, then turn input cells in Column A Red.

First four highlighted are examples of thise formula scenario

** All based off an input into column

How to use conditional formatting to do so???

Excel Conditional Formatting 06.24.14.xlsx‎

View 5 Replies View Related

Changing Cells Colors In Relation To Cell Input - NOT Conditional Formatting Ques

Mar 22, 2008

I'm a average excel user, i know my way around it and kow common and basic formulee, VB isnt a strong point, but i am willing to attemp anything for this, let me explain

Lets say in colum A I have a list of items, in colum B i have a check box, currently the check box is red, if i tpye a x it turns green due to conditional formating. That works fine...onto the problem...

Lets say in colum A i have my items but they are repeated...eg A1, A145, A166, A122 are all the same item, what i want is regardless of where i place the x each check box under coloum B either gets a X and turns green, or just turns green, i dont mind...

I dont think conditional formatting can cut this, though i may be wrong. Ive tried to use the IF function under condtional formatting but ran into problems...

View 9 Replies View Related

Cell Values Based On Input Of Another Cell And Sheet

Apr 2, 2007

I want to tie the values of some cells on Sheet 2 to what's put in into corresponding cells on Sheet 1. Example

If Cell B3 on Sheet 1 has the string "B off" put in, Sheet 2 should automatically get "R1" in cell B12, "B off" in cell C12 and "ID123456" in cell D12. If cell B3 on Sheet 1 is empty, B12:D12 should be blank.

View 4 Replies View Related

Create Documents Based On Cell Values And Input Tabs In New Document

Sep 12, 2013

I want to create a macro that creates a new .xlsx document based on cell values. And input 4 tabs into each document with specified tab names. I have a document that pulls from external sources and fluctuates with amount of data per day. I envision something that will make a new document named for the contents in cell A1, then function as a control+down to create a new document for A2, then A3 until there is no content left (should mention these are lookup formulas, and if there is no data it pulls a value of "0")

Is there a way to put in the code, the tab names it would create in each new document, or would that have to look to a cell value for the naming? The tab names would be "GS", "MYSS", "COLL SHEET", and "WIRE".

View 1 Replies View Related

Conditional Formatting Based On Top 10 Values In Adjacent Cells?

Jun 3, 2014

I have a matrix in excel to show % and $ for multiple columns - set up like this:

Rows Part 1 Part 2 Part 3
Criteria 1 % $ % $ % $
Criteria 2
Criteria 3

the $ columns are conditionally formatted to highlight the top 10 values on the matrix. is there a way to have the corresponding % cells also highlighted? The % and $ aren't both top 10, so the % would have to be highlighted based on the $ top 10 cells.

View 6 Replies View Related

Lock/unlock Cell Based On Another Cells Input

Feb 25, 2009

I want cell g12 to be locked unless cell h7 is Grass Fire or Timber Fire (H7 is a drop down validated cell)

View 8 Replies View Related

Protect Or Unprotect Cells Based On Cell Input

Nov 21, 2006

I am working on my spreadsheet and was wondering if anyone knows if cells can be Protected or Unprotected based on the value of another cell?

Here is what I am looking for:

If cell "A16" on the "Input Sheet" equals AR then Protect Cells J16, K16, L16, M16, N16, and O16

If the value of A16 on the Input Sheet DOES NOT equal AR then leave those cells unprotected.

Has anyone ever done anything like this? On my spreadsheet it would allow the user to skip the unnecessary cells as opposed to having to Tab through them. Not a big deal but definitely would be a time saver and a lot more user friendly.

If you have any questions or concerns just let me know. I will wait to see what the Excel Guru's come up with.

View 9 Replies View Related

How To Conditional Format Data In One Table Based On Values From Another

Aug 12, 2014

how to conditionally format the data in one table based on the criteria specified in another table on a different worksheet.

The Table I'm trying to format has a series of Products and Volumes that'll be available on different dates. Table 1.jpg

The Criteria table has each product and a corresponding Key Data. Table 2.jpg

All I want to be able to do is have the cell corresponding to the criteria to be highlighted but can't for the life of me figure out how to do it. If it makes a difference I'm working offAttachment 338501 a Mac. Excel Version 2011 14.4.3

View 3 Replies View Related

Excel 2010 :: Input Multiple Cells Based On Master Cell?

Dec 9, 2011

I am trying to create a UserForm in Excel 2010 that will look for a part from our database (or on a specific Sheet, possibly a hidden sheet), then add specific data from that part's row into a new line.

More Specifically:

| Part # | Description | Category | SubCategory | Cost | Labor Hours | Weight | Etc |

I want to be able to input the Part #, and have it automatically add specified information to sheets 1 and 2 and those two differ. 1 is our Quote Sheet, and 2 is our bill of materials.

Also, I need each piece of information to go to a specific column (ex. Cost always goes to Column K)

What I have now is a broken UserForm that references a DOC file with a Table, but I need something a good but more complex.

Ideally, what I want is to have 4 ListBoxes where you choose Category --> SubCategory --> Make --> Model then have several CheckBoxes below that would determine whether to add a part (Cost, Labor, Weight).

The reason for this is because we have no need for the Cost or Labor Hours in our Bill of Materials, but need that information for our Quotes. And we generally don't need Specific Dimensions in our Quotes, but DO need them in the Bill of Materials...

Can I make it so that it will insert Pre-Specified information into both sheets at the same time?

View 1 Replies View Related

Conditional Formatting Based On Two Cell Values?

Sep 21, 2013

I want to do conditional formatting based on two cell values.

i want to do conditional format the column 'R' based on clolumns 'J' and 'R'.
column 'J' is a text column named PRIORITY which can hold the values P1,P2,P3 an P4.
Column 'R' is nothing but difference of two columns 'M' and 'H'(both are time stamps).Column'R' is in below format.
=TEXT(M4-H4,"h:mm:ss").

column M and H are in below format:

eg: 9/11/13 9:41 AM

Now i want to format the column'R' like
if column J ="P2" and column 'R' > "00:10:00"(10 mins) =it should become red.
if column J="P3" and column 'R'>"00:30:00"(30 mins) = it should become red.

View 7 Replies View Related

How To Do Conditional Formatting Based On Two Cell Values

Jan 14, 2014

highlighting dates on a premade calendar.

The calendar is made as below:

Tasks
1-1-2014

1-2-2014

1-3-2014

[code]....

The data table is given as below:

Tasks

Date

Task1
1-3-2014

Task2
2-14-2014

I need to highlight the cell in the calendar that matches both the task and the date in the data table. For example- in the situation above, D1 would be highlighted.

View 3 Replies View Related

Autofilter Column Of Data Based On Cell Input?

Jul 30, 2014

I have a table of data which I would like to filter based on a manual cell input instead of the autofilter option.

The table has tickers in column B and has information from column B to U. I would like to filter the data based on a manual ticker that I enter.

View 6 Replies View Related

Shading And Locking Multiple Cells From Input Based On Value In Separate Single Cell

Sep 23, 2013

Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.

View 5 Replies View Related

Conditional Copy Row To Another Sheet Based On Cell Values?

Mar 17, 2014

This is also in sheet1 module ( It cuts the respected row and pastes in sheet2 when date/time is populated in column 14 by double clicking).

[Code]....

View 3 Replies View Related

Conditional Formatting Based On Cell Values In A Range?

Jul 30, 2014

I have a range of cells where each row relates to an employee.

In Cells (Columns H:L) I want the range of cells on that row to turn blue if the user puts a "H" in one of the cells.

Also it will need to be adapted for "O" , "Y", "X" - each with different colours.

This needs to be repeated on each row for each person.

View 5 Replies View Related

Excel Conditional Formatting (Color Cell Based On Their Values)

Dec 29, 2013

Here i want to color my cell based on their dates for example : 01-Jan-2014 to 10-Jan-2014.

For the respective date i want to color my bars from G10 to P10.

View 6 Replies View Related

VBA Code To Find Row Number Based On Data Via Input Box And Highlight Cell?

Jun 21, 2014

I want to enter a unique ID into an input box which will tell which row that id is available and then it will again ask me which column the cell needs to be highlighted. Once i enter the data, it will then ask me what is the change in data (again via input box). i can then enter the change data and then it stops.

View 1 Replies View Related

Excel 2010 :: Conditional Formatting - Highlight Values Based On 2 Columns Of Numerical Data

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

Fill Conditional Cell Values Based On Unknown Number Of Rows

Dec 15, 2009

I have a set of data in column R, with an unknown number of rows that looks like this

Days Late
-28
150
3
16
41
.
.
.

and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:

View 4 Replies View Related

Conditional Format Cells In Jan - Dec Columns Based On Next Column Data?

Jan 29, 2014

Wondering if it is possible to conditional format cells in "Jan"-"Dec" columns based on "Next" column data?

What I need is (lets say fill colour to differ) in one cell per row, based on latest input and "next" data.

Month Avg
Date
Jan
Feb
Mar
Apr
May
Jun
Jul

[code]......

So for example:

row2: May is latest input so need fill in cell +2("next" column data) from may. In this case July

row3: Feb is latest imput and "next" also 2. Result should be Apr

row8:May is latest input, "next" is 6. Res should be Nov.

Finally, only one cell per row to be highlighted so need to overwrite colour fill when new data input in a cell already colour filled.

View 9 Replies View Related

Input In Multiple Cells To Generate Input Into Single Cell?

Feb 22, 2014

I have a spreadsheet with 5 columns. A1 to A5

Entries are made into columns A2 to A5 but only ever one entry across all cells

If an entry is made into A2 to A5 the I need a "P" to be placed into A1

View 4 Replies View Related

Generating Values In Number Of Cells Based On Adjacent Cell Values

May 22, 2014

What I have In Column B, I have the datesIn Column I, I have engineers name What I need I want a macro to generate Serial Nos. (1,2,3....... n) in column A If an only if the date in column B is today's date and the engineer's name matches with the PC's username

The following is my code

[Code] ....

Above code runs without errors but does nothing.

View 4 Replies View Related

Transferring Of Input From Input Cell To Output Cells?

Jun 8, 2014

I would like to know if it is possible to transfer values of cells this way?

I've attached the excel file for easier reference.

View 3 Replies View Related

Conditional Format X Number Of Cells Based On A Single Cell?

May 4, 2014

I am looking for a method to conditionally format a variable number of cells below a single cell into which data is entered.

For example ABC = 6, DEF = 12. Therefore, if I enter ABC into D1 then D1:D6 should be conditionally formatted.

View 3 Replies View Related

Conditional Formating: Highlight Cells Based On Text Of Another Cell

Aug 28, 2009

I have created a drop down box that allows me to choose multiple different Text Options. Example: Cell B4 has dropdown that lets me choose the Text Options, "House", "Car" and "Truck".

Next, I have 5 or so other columns underneath with their own text. What I want to do is have different cells highlight themselves depending on what is in Cell B4.

Example, if "House" is selected in B4, I want cells B7 and B9 to highlight. If "Car" is selected, I want cells B7, B8, and B10 to highlight. If "Truck" is highlighted, I want cells B9-B11 to highlight. Is this possible to do? I've tried using If/Then statements, but those don't seem to work for this type of thing.

View 3 Replies View Related

Conditional Formatting - Shade Group Of Cells In Row Based On Value In 1 Cell?

Jul 28, 2013

I have a spreadsheet where I want to be able to Fill cells green in Column B to Column N for any Rows that have a value in Column L. All other Rows should remain the current colour (orange).

I can Fill the cells in Column L using conditional formatting but am not sure if I can use Conditional Formatting on other Columns so that their Fill colour is determined by the value in Column L?

View 3 Replies View Related

Conditional Formatting Date Cells Based On Entry In Adjacent Cell

Jul 22, 2008

I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.

I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting:
Cell value is less than or equal to =TODAY()

All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.

In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.

View 14 Replies View Related

Conditional Formatting To Colour One Cell Based On Text From Range Of Cells?

Oct 22, 2013

what i would like to do is change the fill colour of D68 if the word Air appears with in D5:D65?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved