Conditional Formatting: Fill Cell if Formula Overwritten

Dear all,

Cell A1 contains a formula.  

How would I go about conditonally formatting A1 such that the cell will be 
filled if the formula is overwritten by a hard number?


Thanks in advance, 

Neil
0
Utf
4/14/2010 1:59:01 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1832 Views

Similar Articles

[PageSpeed] 36

Any reason not to lock the cell and use worksheet protection to keep people 
from accidentally overtyping the formula?

Here's one option, I'm sure there will be others.

Start in your cell of concern (lets use A1), it has a formula in it such as 
=A2+B3
In another cell somewhere put in the same formula, but subtract 1 from the 
result as: =A2+B3-1
Let's say that was cell J1.
Then for your conditional format you can use:
Cell Value and 'not equal to' and enter =$J$1+1

This doesn't guarantee instant recognition of the overtyping.  Lets say that 
the answer from the formula is 27 and someone types in 27.  The format will 
not change, but as soon as a value in the precedent cells (A2 or B3) changes, 
then you'll get the alert.

Another way would be to use VBA and the worksheet's _Change() event to 
monitor the cell.  And as I said, someone may come along and offer a method 
that doesn't use the 2nd cell as I did.  BTW: that second cell can be hidden, 
on another sheet or even on another sheet that itself is hidden.


"Neil Pearce" wrote:

> Dear all,
> 
> Cell A1 contains a formula.  
> 
> How would I go about conditonally formatting A1 such that the cell will be 
> filled if the formula is overwritten by a hard number?
> 
> 
> Thanks in advance, 
> 
> Neil
0
Utf
4/14/2010 2:29:01 PM
John Walkenbach describes a method here:

http://j-walk.com/ss/excel/usertips/tip045.htm

Actually, his approach is the opposite of what you want, in that he
colours a cell that contains a formula, but perhaps you can modify the
approach.

An alternative is to lock the cells with formulae in and protect the
sheet (with or without a password) to prevent accidental deletion/
overwriting of the cells containing formulae.

Hope this helps.

Pete

On Apr 14, 2:59=A0pm, Neil Pearce <Neil.Pea...@Gleeds.co.uk> wrote:
> Dear all,
>
> Cell A1 contains a formula. =A0
>
> How would I go about conditonally formatting A1 such that the cell will b=
e
> filled if the formula is overwritten by a hard number?
>
> Thanks in advance,
>
> Neil

0
Pete_UK
4/14/2010 2:34:17 PM
Reply:

Similar Artilces:

Fill the entire column with a function
Hi, Maybe it is too simple - But I have no Idea :-( I have a sheet - which all columns are retrieves data using SQL Query (From Sql Server), but the first column is calculation based on the other columns - How can I have the first cell in each row populated with this formula? (Currently - after refreshing data - I am dragging the formula for the new rows added. I am sure there is a better way) Can anyone help please? See http://www.mvps.org/dmcritchie/excel/fillhand.htm#filld for a macro equivalent of Ctrl+D It will look to the cell to the left to see how far to copy down. Ctrl+D wi...

AND conditions before summing up
Hi, Let say my data are as follows:- A B C D 100 6 T 10' 200 7 E 15 300 6 D 10 400 5 F 20 500 6 T 11' Question:- I will sum up the numbers from Col D if the number in Col B is "6" AND the text in Col C is "T". How do I come up with this formula? Does sumif works here? If yes, how? In this case, my answer should be 10 + 11 = 21. Hope somebody can help me out here. Thanks. "Ginger" <Ginger@hotmail.com> wrote in message news:024501c3a8f5$90a9b200$a001280a@phx.gbl... > Hi, > > Let say my data are as f...

Short date format
When I enter a birthdate that is prior to 1925, the display is 20.. instead of 19.. I would like to make the default year 1901. For instance, if I enter 2/2/02, the display should be 2/2/1902 insteand of 2/2/2002. Thanks for any help you may render. narfla Try enterin the year as 4 digits. The problem you are having is how a DLL sets the dates for entries of 2 digits. Changing it, changes it for everything on the computer. Right now the date of century change is 2029. That can be altered in Control Panel >>> Regional Settings >>> Regional >>> Customize -- ...

Adding overscore to character in a cell in an Excell spreadsheet
Can you do this? I know how to do this for a floating object (insert object, equation editor, add overscore) text box, but not how to do this within a cell. I would like to create a column in a table with a reference to crystallography. I would like to type <one one one bar> as <111> with a bar over the third one (or the second, or the first). Some of the crystals have indices other than 1 (i.e. <1122> with a bar over the first 2). Is this possible to do? Thanks, Joe ...

Conditional Format on "Active Cell"
I have a spreadsheet where the rows in Column A are descriptions and Columns B thru H are Mon thru Fri. As you arrow down entering dollar amounts by day for each description I would like the Description is say A44 to bold or change color when the cell in say H44 is active. Is this possible? Thanks, Mike I'd suggest you take a look at Chip Pearson's RowLiner add-in: http://www.cpearson.com/excel/RowLiner.htm But if you just want to change the color of the cell in column A: Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) Dim rArea As Range ...

Carriage return in a text cell
Is it possible to enter a carriage return in a text cell so that two individual items appear on separate lines when the text is wrapped? -- Bob http://www.kanyak.com Bob Press Alt and Enter together. Regards Trevor "Opinicus" <gezgin@spamcop.net> wrote in message news:139hsmmnardv125@news.supernews.com... > Is it possible to enter a carriage return in a text cell so that two > individual items appear on separate lines when the text is wrapped? > > -- > Bob > http://www.kanyak.com > Alt + Enter This is one line Alt + Enter This is nex...

Change cell color on update
How do I change a cell's color if it has been modified? I am sure I Use conditional formatting, but not sure how. I have a contacts spreadsheet and I need to send it around to other memebers of my company for them to update, when I get it back, I need to know what has been modified. How to do this? Thanks! CMG Switch on Track Changes Tools Track Changes Highlight Changes Complete the dialogue box Click OK Sara -- Message posted from http://www.ExcelForum.com Hi Have a look at Tools / Track Changes. This will highlight any changed cells and what the changes are. -- Andy....

fill
I have the lookup function: =LOOKUP(A3,'Item Price List'!A2:A1726,'Item Price List'!D2:D1726) which finds the value in A3 on another worksheet in column A an returns the value in that row in column D. I'm trying to fill down, but I only want the "A3" to increment itself How do I stop the A2:a1726, and the d2:d1726 from incrementing also? Monaga -- Message posted from http://www.ExcelForum.com $A$2:$A$1726 $D$2:$D$1726 And look up relative and absolute references in Help. -- HTH, RD ============================================== Please keep all correspo...

Copying and Filling Cell With Functions
I have an Excel page that has refrences to another worksheet ie =May!B2 I want to create a generic spreadsheet that I can just copy for each month but when I do I get ='[May Commissions Sheet.xls]May'!B2 is there a way to advoid this? And also when I try and do a fill instead of trying to copy I get =May!B3 or I tried typing in cell M2, =May!B2 and in cell M3, =May!C2 and then tried to do a fill that way for Excel to get an idea of what i was trying to do and it ends up putting =May!B4 in M4 and =May!C4 in M5 and =May!B6 in M6 etc. Can anyone help me cause I am about to lose i...

XML commands
Hi. I found a very helpfull hint to find formula-cells. The trick is to use the get.cell function. I would like to learn a little bit more about that function. What does 48 or 6 mean in get.cell(48;.... or get.cell(6;... I can't find documentations at the net ? Regards Peter Get.Cell(num, cell reference) is a function from XL4 to return information about the formatting, location or contents of a cell. 48... if cells contain a formula it returns True; if a constant it returns False. 6 ... returns the cell formula Peter Noneley has a free download at... http://homepage.ntlworld.com/no...

copy formulas
I have a excel worksheet that i want to copy the data and the formula to a new worksheet .The worksheet was linked not embeded so it won work without the link. When i try to copy it i only get the data not the formulsa . Any help would be a great help -- philthedi ----------------------------------------------------------------------- philthedil's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=799 View this thread: http://www.excelforum.com/showthread.php?threadid=26406 Replace all equal signs with r=, copy what you want, then reverse the replace, i.e. edit&g...

How can I use dates in a formula?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I want to grab todays date in an excel formula, and subtract it from another date. <br><br>For example, our quarter ends on march 31, 2010. I want to figure out the number of days left in the quarter. What would even be cooler is to only factor in work days.........anyone know how to do this? > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3349185821_330156 Content-type: text/plain; charset=&qu...

Auto fill multiple sections with different names
I have weekly time broken down by category, however, only the total line has the corresponding names. How do i fill the above cells with the corresponding name on the total line. For example(where blank insert last name on total line) Col A Col D blank wk27 blank wk28 blank wk29 blank wk30 Smith Total blank wk27 blank wk28 blank wk29 johnson Total thanks Try this: 1. Select the cells in column A 2. Press F5 3. Select 'Blanks...

Making fields conditionally dependant on the values of other fields?
I am working on an order-entry form for my users, and need to make the value of some fields conditionally dependant on the values of others. For example, if FieldA = "NONE" then I need FieldB to have one value. If FieldA = "YES" and FieldC = "OUT" then I need FieldB to have a different value, and so on. What is the best method for achieving this in Access 2000? It can't be done through tables. You could probably accomplish it on a form, using VBA, or else as computed fields in a query. Note that if you have rules defined as that, then you should NOT be ...

Enter the cell value without leaving the cell
Is there a way to use the keyboard to enter a cell value without exiting the cell when you press the ENTER key? I use the mouse to click the green check mark in the formula bar, but I would prefer to use the keyboard to accomplish the same task. Thanks, CRTL - ENTER thanks "Chris Hedlund" <chedlund@mwdata.net> wrote in message news:eH3DAXMEGHA.1088@tk2msftngp13.phx.gbl... > Is there a way to use the keyboard to enter a cell value without exiting > the cell when you press the ENTER key? I use the mouse to click the green > check mark in the formula bar, bu...

Cell Number Format
Is there a way to format a cell's number from 1.2 to 1,200,000? I know how to format millions to ones but how do I convert from ones to millions? (I don't want to have to multiply in another cell). Thanks!!! I could be wrong but, suspect there is no way to achieve what you want using format. You could use the sheet change event to multiply numbers entered by one million and format the result as you wish using code below. Just right-click on the tab of the appropriate worksheet, click view code and paste. Note. this will multiply value in column C only. You will need to modify ...

Adding in another conditional format on the same cell
Dear everyone.. I have a cell containing "conditional format" when 2 weeks before the due date ...the cell colour changes to "orange"....it turns red when it reaches the "due date" ..... Now.......i need to add in another condition ....when i add in another column e.g. "B" and the font colour will change to "yellow" ....i think its something to do with....if equal to....then it would change to this colour.... Can someone teach me the formula pls? urgent....thanks everyone.... Hi! What exactly are you trying to add as another condit...

cell referencing from a Combo Box
I'm trying to make the result given in a combo box make data appear i the cells (or text boxes) to the left and right of the combo box. Th data being drawn from is a range of cells in a column that is part of table with the same arrangement as the combo box/cell arrangement. just want to be able to choose data from that table, and by choosin one element in a row, have the rest of the row appear around it. I've been trying to find a formula that will allow a cell to refer t the result in the combo box, and give its result as the cell dat adjacent to that given in the combo box, as th...

SUM rows in column if cell in different column is empty
Hi, I have a spreadsheet that has many rows of data. I'm trying to get a total for a column ("G") in a cell ("A2") but only on rows where the cell in another column ("J") for that row is empty.I tried SUMIF and couldn't get it to work. Can this be done with some kind of formula? Any help would be appreciated. Thanks, gg =SUMIF(I:I,"",G:G) -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) <nospam@iamtheonewho.com> wrote in message news:1173743011.019953.66950@v33g2000cwv.googlegroup...

Copy text from cell and paste it in a separate text box (contents to be linked)
Dear Experts: With some cells in a colum selected (say 10 selected cells in a column: contiguous range) I would like a macro to copy the contents of these cells and paste the contents of each cell in separate text field. Now here it comes. The pasted contents in the text field should be linked to the cell contents, i.e. whenever the cell's contents is changed, the contents of the text field should reflect this. Is this feasible? Help is much appreciated. Thank you very much in advance. Regards, Andreas Again, Detail and before/after examples. -- Don Guil...

simple formula for some
Hi - I hope someone can help with a formula problem- with 3 columns covering dates/ date ranges and using another for a % complete figure I have 1 column with a range of week dates covering 6 mths ( Col A) I have 1 column with lots of dates ( Col B) and another with a % complete figure ( Col C) against the dates what i would like to do is a "count if" ( or something similar - help) for the following in a seperate cell - I would like col B to "look" at Col A and count the no of times this date is the same but only if the date in Col C i= 100% . any idea...

How do I reference a worksheet name through a formula?
I would like to reference a cell to obtain a worksheet name rather than have the worksheet name in the formula. So rather than have Sheet1! in the formula I would like to have a cell that contains the text "Sheet1" Check out the Indirect function. Regards, Fred "pv6901" <pv6901@discussions.microsoft.com> wrote in message news:DD37DFEF-3191-4856-8B8A-BD88FBA332A4@microsoft.com... >I would like to reference a cell to obtain a worksheet name rather than >have > the worksheet name in the formula. So rather than have Sheet1! in the > formul...

Adjust text to fit within an Excel Cell using C#
I am developing a console application in which I have exported data from dataset to an Excel. I need to adjust the text to fit within the cells without using wordwrap. As my requirement is to achieve the data in every column in a single row. where my data in each column keeps varying. Below is the method code used to generate the excel sheet report. private static void GenerateWorkSheet(XlsDocument doc, DataTable dt, string workSheetName) { Worksheet sheet = doc.Workbook.Worksheets.Add(workSheetName); if (dt != null) ...

protection, formulas and sharing with dummies
I am having a protection problem and have tried the unlock and hide solutions without success... I have a workbook I am sharing with co workers. Many of the cells have formulas in them that change cell contents based on inputs. Not all the data is availabel at once and the workbook is meant to be a "tracker" type that provids alerts when days have passed etc, based on formulas and conditional formatting values. I need to make it idiot proof, ie. when someone type a number into the wrong cell (say one that should have remained empty) by mistake, then tries to delete the er...

How do I format a cell, so the user must use a user calendar?
I created a user calendar by means of a macro. I want to set a cell in the workbook so when someone clicks on the cell the calendar automatically comes up so they can select a date. nathan See Ron de Bruin's site for code. Adjust the range to suit http://www.rondebruin.nl/calendar.htm Gord Dibben MS Excel MVP On Mon, 16 Jan 2006 09:25:03 -0800, nathan <nathan@discussions.microsoft.com> wrote: >I created a user calendar by means of a macro. I want to set a cell in the >workbook so when someone clicks on the cell the calendar automatically comes >up so they can se...