Conditional formatting not updating automatically
Let's say you have a worksheet with thousands of rows of data.It would be extremely difficult to see patterns and trends just from examining the raw information.In our example, we have a worksheet containing sales data, and we'd like to see which salespeople are meeting their monthly sales goals.The sales goal is 00 per month, so we'll create a conditional formatting rule for any cells containing a value higher than 4000.Tutorial Common wisdom says you just can't apply conditional formatting in a Google Apps spreadsheet using data from a different sheet. Long Story Short For you advanced users who want to skip ahead: You apply the conditional formatting using a custom formula and some variation on the INDIRECT function.But here at THE Journal, we laugh in the face of wisdom, common or otherwise. In addition to specifying the column and row, you also specify the name of the sheet to pull the data from ("Sheet2," for example).For example, a conditional formatting rule might be: If the value is less than 00, color the cell red.
Now, any existing reference to the cells in the table such as =SUM(C2: C5) will expand to include any new data added to the table.One of the most common issues that arise in Excel is that a range that contains data will eventually expand as more data are added or even contract as data are removed.If we have a formula, or an Excel functionality (such as Data Validation or a Pivot Table), or a chart, that refers to such a dynamic range, we must adjust the formula whenever the size of the range changes.Can someone imagine using Microsoft Excel without formulas? And hardly anything could compare to the frustration caused by Excel formulas stop working all of a sudden.When this happens, a bunch of questions immediately flash across your mind. Why doesn't this formula update its value automatically?