Hi,
I am trying to conditional format cells, based on a formula. But, it is not working. Please help.
Problem statement: I have rows (variables), which include historical data (columns) and forecast/estimate. The historical data is being pulled from another sheet, forecast/estimates are hard-coded numbers. When new historical data becomes available, forecast/estimate should be overridden, by dragging (fill right) the formula. Which analysts sometimes forget to do. I am trying to highlight cells, where historical data is available, yet it is hard-coded (forecast/estimate).
When I put this code in conditional format, it does not highlight the cells. However, when I place this formula in excel, the results is TRUE for right cells and FALSE for cell, which are correct.
=AND(IFERROR(OFFSET(INDIRECT(SUBSTITUTE(FORMULATEXT($B3),"=","")),0,COLUMNS($C3:C3)),0<>C3,IFERROR(OFFSET(INDIRECT(SUBSTITUTE(FORMULATEXT($B3),"=","")),0,COLUMNS($C3:C3)),0)<>0)
What this aims to do is, get the formula (for historical pull) from start of data series (Col. B - this would be linked with historical for all rows), then fill-right the formula for appropriate cell. Check if the result of fill-right formula is equal to the actual value filled in the cell (i.e. rightly filled), or if the historical value doesn't match with hard-coded number and highlight them.
I am able to highlight all cells, which are hard-coded, but that is not what I am aiming for. Highlight cells only when a historical is available, yet the cell is not using it. Would be of immense help, if you could help me out, as why this function is not working in conditional format, and if there is an alternative way to do this. I also tried LET function to reduce the length, but same result.
Bookmarks