+ Reply to Thread
Results 1 to 4 of 4

Excel cannot use relative ref in conditional formatting criteria for data bars

  1. #1
    Registered User
    Join Date
    06-04-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    2

    Question Excel cannot use relative ref in conditional formatting criteria for data bars

    I am having an issue finding a work around for the limitation that data bars cannot use relative references. I would greatly appreciate the help.
    This is an inventory tracker I have built for almost every item we have in stock.

    Basically I need column E's data bar's maximum to be set to the corresponding cell in Column L. Of course since excel will not allow relative references for data bars, it will only reference the top cell for all rows.
    The only work around I can find is setting a new conditional rule for each and every row. Given the insane number of items I am working with this is not a viable option.
    Is there some sort of formula I can use to have my data bars reflect Column E's value compared to Column L's value relatively?

    I have hidden the columns & rows unrelated to my issue.
    excel example.png

  2. #2
    Valued Forum Contributor
    Join Date
    03-24-2020
    Location
    Thailand
    MS-Off Ver
    Office 2021
    Posts
    968

    Re: Excel cannot use relative ref in conditional formatting criteria for data bars

    Try using helper-column to get the relative percentage you want to show in the data bar.
    In the data bar setting refer to the column where these figures are.
    Attached Images Attached Images

  3. #3
    Registered User
    Join Date
    06-04-2020
    Location
    USA
    MS-Off Ver
    Office 365
    Posts
    2

    Re: Excel cannot use relative ref in conditional formatting criteria for data bars

    This is useful but it is still pulling from an absolute cell out of the column and basing the data bars off that. I need it to base each row's data bar relative to it's row all the way down.

    Thank you

  4. #4
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,888

    Re: Excel cannot use relative ref in conditional formatting criteria for data bars

    Picture of a problem and picture of solution
    Ben Van Johnson

+ 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 Formatting w/ Data Bars
    By NoMotion in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-20-2015, 02:24 AM
  2. Data Bars Conditional Formatting
    By benny66 in forum Excel General
    Replies: 7
    Last Post: 07-08-2014, 11:34 AM
  3. Conditional Formatting using Data Bars giving me Relative Reference Error
    By excelstudent74 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-31-2013, 12:58 PM
  4. Conditional Formatting Data Bars
    By Wak1234 in forum Excel General
    Replies: 0
    Last Post: 12-15-2011, 05:52 PM
  5. Replies: 5
    Last Post: 09-19-2010, 05:33 AM
  6. Excel 2007 : Conditional Formatting - Data Bars
    By flaviocn in forum Excel General
    Replies: 3
    Last Post: 08-09-2009, 06:23 PM
  7. Excel 2007 : Conditional formatting & Coloured data bars
    By alex Kurtz in forum Excel General
    Replies: 7
    Last Post: 01-29-2009, 06:20 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