Excel Conditional Formatting reference cell until data is Manually Entered

microsoft excel

I have a spreadsheet, that has a sheet for predicted data and another for manually entered data. the sheets look identical.

I would like so that the prediction shows on the Manual sheet, but in light grey (conditional formatting). however once the actual data is entered manually into the that cell, i want it to see that is the live data and remove the conditional formatting, as well as allow other formulas to see that it is now live data and use that to do what it normally does.

Previously I've used the isblank function so that if the say A1 or A2 fields are blank then to stay blank, and if several other cells see that A1 or A2 are blank then not to show anything either, i want this still to happen but for A1 and A2 to show the data that has been predicted on the prediction sheet.

I hope I've managed to explain that well enough. Is there a way to do this?

I'm using Excel 2016 if that helps

Best Answer

If the prediction is being generated with a formula, it should be simple enough to select the table and choose 'Use a formula to determine which cells to format', and into the box enter =isformula(ref). Replace the ref with the cell name in the top-left corner after you have selected it all.

Related Solutions

Excel Conditional Formatting Multiple Data Bars and Data Icons in one cell

Well, after many hours searching the internet I was able to find an Office Excel Blog that had my answer. http://blogs.office.com/b/microsoft-excel/archive/2006/02/24/conditional-formatting-trick-1-multi-coloured-data-bars.aspx

The idea is to provide a formula for each conditional format to tell it which cells to format and which cells not to format. There is no interface for this built-in to excel so we need to turn to VBA to apply the formulas.

selection.FormatConditions(1).formula = "enter formula here"  
    -- Quotes are mandatory and the Conditional Formats start with "1"
       at the top of the list   
    -- If you make a mistake selection.FormatConditions(1).formula = "=TRUE"

Adding formulas to excel conditional formats

I wasn't sure if this format will persist from one session to the next. I decided to open the Excel file and take a look at its XML contents. In the worksheets folder (after you add the .zip extension to the file), click on the spreadsheet "Sheet1" and at the bottom is the information for the conditional formats. The XML on the left is from before I applied the conditional formats formula and the XML on the right is after.

enter image description here

Excel Conditional Formatting greyed out

There are multiple worksheets in this document. One of the other worksheets had a group of cells highlighted/selected. Unselecting them allowed Conditional Formatting to be accessed.

Best Answer

Related Solutions

Excel Conditional Formatting Multiple Data Bars and Data Icons in one cell

Excel Conditional Formatting greyed out

Related Question