Formatting Cells Dependent On Other Cells Relation To That Cell.

Jan 4, 2010

i have just read the thread started by scabertrain regarding "if" conditonal formating, but mines slightly different and i cant make head nor tale of the fomula shown.

So... Monthly budgeting... using a projected outgoing (A1) and an actual outgoing (A2) i want the colour "A2" to change depending on whether i have spent over the budgeted amount, green being underspent, orange for on target and red for over spending....

Example:

I predicted (A1) that i was going to spend £50 on a night out, but my actual spending (A2) was £100, i would like A2 to turn to red.

View 3 Replies


ADVERTISEMENT

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

Formatting Cells Dependent On Results From Other Worksheets

May 30, 2009

I have a workbook with three sheets.

Sheet 1 is an original 'in progress' work.

Sheet 2 is a copy of sheet 1 taken on a particular date.

Sheet 3 is information extracted from Sheet 1.

On sheet 3 what I need to do is change the font colour of a cell to red if the information it contains in relation to Sheet 2 is different i.e Sheet 1 cell and Sheet 2 cell are different

For example

Sheet 1 cell value = Deb
Sheet 3 cell value = Deb
Copy of sheet 1 taken to create sheet 2 on a certain date
Sheet 1 cell value changes to = Bed
Need Sheet 3 cell to change to = Bed as it is different from sheet 2

I did try conditional formatting but it will not work with info. from another worksheet that I can see. Maybe someone out there has another solution.

View 9 Replies View Related

Conditional Formatting To Change Colour Of Cells Dependent On Text Within Them

Sep 8, 2013

How to colour cells depending on text in other cells, for example,

I would like cells D26:AA26 to turn light red if the letters 'FSM' are in cell 'E26' even if other text appears in it, eg 'SA+/FSM'
I have been playing about with conditional formatting but can seem to solve the mixed text issue?

I have included an example below,

Do you use a "*FSM*" around the formula?

[URL] ...........

View 7 Replies View Related

Automatically Color Cells In Relation To Data Entered

Aug 30, 2013

how to do basic formulas in Excel but I are hoping that there is a macro or a VBA or something that can be made which might work for what i need.

I have decided to start a small tour shop (I make the reservations for guests with a hotel in the area and take them on tours of our region) and are looking to make a booking sheet for each month. Nothing complicated just simple. see the attached .xlsx file.

I was wondering if there is a way that when the fields in column A & B are filled in if it could automatically color the corresponding dates in for the same row in the calendar area? like I have manually done on sheet "October 2013"

[URL]

View 6 Replies View Related

Excel 2003 :: Font Size Dependent On Cell Value (not Using Conditional Formatting)

Nov 28, 2012

Excel 2003.

I am creating a heat map using two variables from a questionnaire: firstly the number of people impacted by a question; and secondly, the level of the impact. Respondents are asked to give these values in two adjacent cells. These are then multiplied to give a weight eg 1000 people x level 3 impact = 3000. I have used Conditional Formatting to colour the cell according to the impact level (0 = none - white (default), 1 = moderate - tan, 2 = significant - orange, 3 = high - red). What I want to do is then change the font size to give an indication of the number of people impacted, eg 1 - 1000 font 8, 1001 - 3000 font 12, 3001 - 8000 font 16, 8000 - 2000 font 24 etc. The size of font and colour will then represent the number of respondents and their impact.

I can't use Conditional Formatting as the value of 3000 could be derived from 1000 people at level 3 (red), or 3000 people at level 1 (tan). Is there another way of setting the font size depending on the cell value, eg by VBA?

View 9 Replies View Related

Creating Dependent Cells...

Mar 11, 2009

I have two columns with correlating data. i created a validation drop down list and would like the corresponding number to populate a cell based on the drop down list selection. i.e....

View 2 Replies View Related

Count - Dependent On 6 Cells

Dec 7, 2013

I have a spreadsheet that I'm trying to fill with data. There's a lot going on in the sheet, but 1 really useful thing I could incorporate is a running count.

Explanation: I have about 2000 rows of data at the moment.

A row would be finished if it's column J,K,L,M,N & O have data (text) in ALL of them; IE 6 cells of data in order for it to be complete.

So, a running total. If say 200 rows have data in J to O (all 6 cells) the count would show 200. My rows of data start from Row 5. I'd like to add the formula to cell N2.

View 7 Replies View Related

List Of Dependent Cells

Apr 24, 2008

Is there a way to quickly create a list of all dependent cells?

View 9 Replies View Related

Referencing Cells Dependent On Sheet Name

Oct 28, 2008

I have a workbook that has a number of sheets with various project stages and dates on them. These details are arranged in small tables displayed vertically down the page. Each worksheet's name is the project code - e.g. 30033_99

I have now been asked to create an overview page that displays all these stages and dates on one page. What I'm trying to do is get Excel to look at the column with the project code and then pull information from the worksheet with that name.

View 4 Replies View Related

Cells Dependent Upon How Workbook Is Opened

Jun 9, 2009

I have a workbook with about 30 tabs in it. It is currently protected by a password. The workbook itself has a couple of 1000 formulas across the tabs.

What I would like to do is to lock certain cells on each worksheet depending upon if the workbook is opened as "read only" or "in edit mode" (when someone uses the password).

So when someone uses the correct password, I would still like certain cells to be locked, or after the initial password is entered for editing, another popup box with "would you like to edit cell formulas? Y/N (only on selective cells), if no, then no editing on those cells allowed, if yes, then prompted to input another password to do this.

View 14 Replies View Related

How To Highlight Cells Dependent On Time

Apr 11, 2014

I would like to know if it’s possible to make Excelhighlight a cell dependent on another cells Time. Here is the link to thespreadsheet I have created. [URL] WhatI would like is cells with the times above them (G5 through AJ5) to automaticallyhighlight dependent on the time in the cells (C6 and D6) I highlighted the cellyellow to show what I mean. T

[URl]

View 4 Replies View Related

Formatting Cells From Another Cell

Nov 8, 2008

Hi All, I have Column I, which has a drop down menu in it, which if "WITH NOU" is selected - i would like it to change the colour of the text in column B, to a dark blue?

View 9 Replies View Related

Excel 2007 :: Conditional Formatting Empty Cells Based On Full Cells?

Nov 17, 2011

Working in Excel 2007. I am using excel for a data log (basically) and want it to format all empty cells in a row yellow if there is data in column A

Basically, If i have a value in A2, I want any empty cell between B2-G2 to be filled in yellow (as an idicator to the inputter that the cell needs to be completed).

there is already conditional formatting on these cells, which i want to maintain for the non-empty cells. I also have "0" as a value, so I couldn't use the basic conditional formatting setting it =0, it highlighted cells with $0.00, which i do not want.

View 5 Replies View Related

Deleting Rows That Have Dependent Cells. Prevent !#REF Value

Oct 27, 2007

Deleting rows that have dependent cells.

I have one sheet that uses a very simple formula for copying certain values from one cell to another sheet “=VALUE(Sheet1!B5)”.

My problem is that rows have to be deleted in sheet1 as the information eventually becomes expired and the following is displayed in my cell in
sheet 2 “=VALUE (Sheet1!#REF!)” Is there a work round this, to prevent the formula showing a #REF! Value.

Row 5 is replaced and the same formula has to be re-entered. Excel 97 used in our office unfortunately.

View 10 Replies View Related

Dependent/Linked Drop Downs In Cells

Apr 29, 2008

Now he has another task that I am not having any success with. He has asked if we can make an Excel spreadsheet that allows us to access a list of customer units from a drop down menu which is in another workbook, and once you choose that particular unit, another drop down menu will allow only those customers who are assigned to that unit to be selected from that sub-menu. I hope that makes sense.

Say customer A1 (we will call him Delta Co, 2-1 Aviation) is selected from this first drop down menu (which I haven't figured out how to make yet either). Then this selection would access another worksheet in that other workbook with a new drop down menu with only the four customers in that unit. As an example we will use the names SSG Wilson, SGT Jackson, SFC Jones, and SGT Phillips.

So when the Supply Tech here opens the spreadsheet, the first drop down menu, he will select Delta Co, 2-1 Aviation. Then the next menu will only show four names, and he will select SFC Jones.

Once we get this going, I may be able to figure out the other things the commander wants, if not I will be back.

View 9 Replies View Related

Conditional Formatting: Cells Filled By Red Until The User Enters Text In Those Cells

Jul 18, 2006

Is there a way to set up a conditional format for several cells so that the cells are filled in with red until the user enters text in those cells??

View 5 Replies View Related

Excel 2010 :: Save Cells As They Are / Register Cells With Formatting?

Jul 2, 2014

So i've been trying to do this invoice/inventory/client , I couldn't find a way to somehow register the whole invoice as it is, so i can later print/visualize it if i lost the copy or something.

It would be best if it just stayed stored in a variable and not always visible, and only appeared if i wanted to check/print it.

The whole invoice would be from A1 to F30

Excel 2010 btw

View 5 Replies View Related

Conditional Or IF - Formatting Another Cells Font Based On Different Cells Text

May 3, 2013

What I am looking to do is;

If cell A2 = "Closed"

then I want cells B2 and C2 to strike through its own text.

I.E.
A2= anything other than "Closed"
then B2 and C2 = Blah blah blah

but if

A2 = Closed
then B2 and C2 = Blah blah blah

View 5 Replies View Related

Excel 2013 :: Lock Cells Dependent On Another Contents

May 23, 2014

I am looking for a macros to lock a cells dependent on another's value.

I have a spread sheet for quote numbers I don't want the employees to take a quote number (A) unless all cells have been completed in cells (C-K).

however there is another condition within this where if cell K says yes then lock Cells N O and P, however if it says No these cell must also be completed to take a quote number.

I have also added a macro to force enable macro with a welcome page and a macro to log the user name date and time of entry, I don't want to loose this it seems each time I try a macro for the locking cells or data validation it disables these macros.

View 1 Replies View Related

Gantt Chart: Color Cells Dependent On Criteria

Dec 5, 2006

I have a list of task owners that are assigned tasks. The tasks are mapped out in Gantt chart form with time shown by an "X".

what I want to do is to colour the cells "X" with the associated cell colour of the task owner and should the owner change, the cell colour changes.

View 5 Replies View Related

Conditional Formatting Of Blank Cells And Date Cells?

Jan 17, 2013

I have attached the sample. I need the cells without the employee or without a boss to highlight a color and i also need the date of certification to highlight if it is more than one year old.

conditional formatting.xlsx

View 4 Replies View Related

Removing Duplicates Between Two Cells Reliant Upon Formatting Of Two Cells?

Jul 30, 2014

Creating a project tracking sheet that is as automted as possible so that people dont have to populate lots. I have managed to get it to create a new sheet and populate according to a filled out combobox. The user then populates a 'milestone' section. When a button is clicked these are transferred to a GANTT chart type sheet and pasted. Before they are pasted the macro checks if the project title exists, if it does not it first pastes the title LEFT INDENTED. Then follows by pasting the milestones CENTRED. If the title exists, it inserts the milestones under the project title by copying and pasting all milestones. Hence, if milestones are regularly added, it will begin to add duplicates of the same milestone. I need to remove these. I would usually be able to do this however there is often the same milestone in numerous projects which I CANNOT remove as it would loose data. I would like a code that says between the cell I was searching for (Rng) and the next cell which is also left indented to remove duplicates. Basically I can't use x1enddown because it needs to stop at the next cell which has the same formatting... i.e. only duplicates to be removed out of the cells which are centred. I would then like it to repear this action for the whole document and move the next left indented cell and do the same.

It feels like a kind of backward conditional formatting is what I am looking for

Here is my code as it stands

Dim FindString As String
Dim Rng As Range
FindString = Range("D2").Value
If Trim(FindString) <> "" Then

[Code]....

View 9 Replies View Related

Conditonal Formatting A Cell Based On Other Cells

Jul 30, 2009

I'm doing a Comparison of Price quotes from some different vendors and I want to highlight which one is the lowest.

4 Rows with Totals
The Cells are B21, B43, B65, B87

I need each value in each location to be compared to the other 3.

The other thing is that I also C through V that need the same thing.

View 14 Replies View Related

Conditional Formatting Certain Cells When Another Cell Is Highlighted

Aug 21, 2009

I want to change (via conditional formatting) the background colour of cell H64 and K72 when I select / highlighted / activate (i.e. just left click in the cell) cell C66.

I don't seem to be able to find in the formulae something to indicate if C66 is selected / highlighted /activated.

View 6 Replies View Related

Formatting Row Of Cells Based On A Cell Content

Feb 16, 2010

I want to format a range of cells based on the name listed in a cell in column A (starting @A6). I currently do this manually. It is to help me visually see the line I am working with and for what person.

I am almost certain that is going to take a VBA, but I know little to nothing about setting one up.

I have attached a sample. I am using Excel 2003.

View 7 Replies View Related

Formatting Multiple Cells Based On Value Given In One Cell

Nov 1, 2013

How multiple cells can be formatted based on one cell value. See the screenshot attached to understand what I am trying to achieve. I am very new to excel formatting, vba etc.

ExcelFormattinQs.jpg‎

View 4 Replies View Related

Conditional Formatting A Cell If Two Other Cells Match?

Sep 5, 2013

My Goal: If the value of G8 (which is the result of a count formula) matches the value of G10, then apply formatting to G10 through H11.

View 3 Replies View Related

Variable Cell Relation / Reference In Macro

May 29, 2012

I am building an Excel sheet for my company which keeps track of who has done what with a new client in a step by step process. I have it set up so that once a department clicks complete in a box it automatically emails (VIA a macro) the next person who needs to do the next task. Some tasks are done by the same person no matter what, other tasks are done by a project leader which is different depending on the client.

The problem I'm having is that, within the macro, I need to reference an email address in a cell X cells to the left in the ".TO" line.

So, in Column A I have the CLIENT, Column B The PROJECT LEADER, and Column C The PROJECT LEADERS EMAIL. Then the next 22 Columns are labeled steps with drop down boxes, once the have selected "COMPLETED" it triggers the macro and send the email, just not on the ones when it needs to email the PROJECT LEADER, since it's a variable. Below is the macro, how I can have the ".TO" line in the email reference the email address in Column C to the left of it?

Sub Mail_Workbook_1()
Dim OutApp As Object
Dim OutMail As Object

[Code]....

View 2 Replies View Related

Change Validation List Value And Copy / Paste From Dependent Cells?

Jun 12, 2014

I have a sheet which autofilters according to a validation list in cell E2 using the code.

I have then used subtotal arrays to calculate the mean, median, max, min and total count for whichever values the filter shows.

What I want to do next is have a code which will select each possible option from the validation list (triggering the autofilter) and copy and paste the values from each dependent formula into a new sheet.

Attached File: dummy 1.xlsm

View 7 Replies View Related







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