+ Reply to Thread
Results 1 to 3 of 3

Conditional Format with formula (fill-right, drag formula)

  1. #1
    Registered User
    Join Date
    10-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    2

    Question Conditional Format with formula (fill-right, drag formula)

    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.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Essex, UK
    MS-Off Ver
    various
    Posts
    2,351

    Re: Conditional Format with formula (fill-right, drag formula)

    you might try applying the below CF rule (to C3) and applying to requisite range

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    the above should only highlight those values that do not match the historics - where said historics are non-0

  3. #3
    Registered User
    Join Date
    10-26-2012
    Location
    Mumbai
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Conditional Format with formula (fill-right, drag formula)

    Thanks a ton! It works.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Conditional Format formula does not correctly FILL adjacent cells when values not equal
    By GregM56 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-15-2020, 12:18 PM
  2. Replies: 3
    Last Post: 07-03-2018, 06:47 PM
  3. [SOLVED] How to change the drag down fill formula?
    By nova312 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-05-2017, 03:22 PM
  4. [SOLVED] Formula that will allow drag to fill row counts from all other worksheets
    By jts5814 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-24-2016, 01:06 AM
  5. How to drag/fill a column with a 3D formula
    By BG23 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-17-2015, 07:03 AM
  6. Replies: 4
    Last Post: 09-29-2006, 01:51 PM
  7. how to fill formula in any cell and drag it?
    By vumian in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-10-2006, 10:46 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1