1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help in hatching automatically

Discussion in 'Ask an Excel Question' started by Alok Mishra, Sep 9, 2018.

  1. Alok Mishra

    Alok Mishra New Member

    Messages:
    8
    Want to color it automatically with reference I just need to put chainage and offset and it should be hatched automatically. Can Anyone Help me? Please see the excel file for more detail. Thank you.

    Attached Files:

  2. Alok Mishra

    Alok Mishra New Member

    Messages:
    8
    The sheet I have colored manually is just an example. Please refer the file for more detail. I have given the example also to make it more understandable.
    Thank you
  3. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    375
    I have played with this. Picking out the cells with direct references rather than intersecting rows and columns would probably make the solution less obscure. Mind you, the problem is somewhat obscure :) itself!

    Attached Files:

    Thomas Kuriakose likes this.
  4. Alok Mishra

    Alok Mishra New Member

    Messages:
    8
    Please explain me also how you have performed sir. If you can give me steps then it will be a lot easier for me.
    @Peter Bartholomew
  5. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    375
    My use of defined names may have confused you.

    The objective is to develop formulae Is_K? and Is_Y? that can be evaluated within each cell of your grid and determine whether a conditional format is active or not (in the attached, I have evaluated Is_K? in the cell as well as evaluating it as a conditional format criterion).

    A key element of the formula is
    = ( MEDIAN( K Offset.range, offset ) = offset )

    I have used two names to identify the (vertical) bounds on the quantity with reference name 'K'. One name is the row range 'K' and the other identifies the four values in the 'Offset' columns. The two ranges separated by a space is the intersection of the row and columns and evaluates to give
    =$R$5:$S$5

    The name 'offset' is a relative reference that looks up the current row label on the 'vertical' axis.

    The MEDIAN function returns the middle value of a set of values. When there are three values comprising upper and lower bounds and a test value, the test value will be returned only if it is within the bounds. This gives a single test that replaces separate checks against the upper and lower bounds individually.

    Multiplying the above formula by
    * ( MEDIAN( K Chainage.range, Chainage ) = Chainage )
    causes a 1 to be returned only if both the row and column are within range.

    I hope this helps.

    Attached Files:

    Thomas Kuriakose likes this.
  6. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    375
    I may have misunderstood your requirement. The conditional formats I provided allowed the user to define the areas of blue and yellow. What it did not attempt was to take a pre-defined area and then change its reference colour.

    To achieve that, I redefined the fixed rows 'Y' and 'K' to be determined by looking up a colour with MATCH and returning a dynamic row with INDEX.

    'Blue': = INDEX( Definition, MATCH("Blue",Ref,0), )
    'Yellow':= INDEX( Definition, MATCH("Yellow",Ref,0), )

    The rest of the calculation goes through as before.

    The (exclusive) use of names is a foreign concept as far as the bulk of the spreadsheet community is concerned but here I have been able to modify the definition of two ranges and in doing so change 8 cell references. The changes then propagate through without further modification.

    Attached Files:

    Thomas Kuriakose likes this.
  7. Alok Mishra

    Alok Mishra New Member

    Messages:
    8
    Thanks for your help so far. But my concern is what if I want to use this technique in another excel workbook and in a larger area with more colour references. How will I be able to do this? The formula you made and all other things, I just want those steps badly sir. But believe me you are awesome. Btw how will I perform this exercise in another excel workbook in a larger area and with more color references? If you can guide me step by step then it will be very helpful for me. Even a video will also work if you can otherwise as you wish sir.
  8. Peter Bartholomew

    Peter Bartholomew Active Member

    Messages:
    375
    @Alok Mishra

    The key point is that I do not believe it possible to define the formula for a conditional format and then change the format itself by formula.

    The process has to be the other way around. You define a separate conditional format for each colour. A formula is used to return TRUE/FALSE for each cell in the region but that formula needs to contain MATCH (or possibly VLOOKUP) to look for the first instance of the colour in order to select the actual data to be used from your table.

    You can test one of the conditional formatting formulas within the conditionally formatted range, but to develop the next, you would need to assign the formula to a Name (e.g. 'is_Blue?') and use the Name to make the Conditional Formatting evaluate the formula.

    The attached file shows how an additional condition may be added. It involves a new conditional format with the formula 'is_Orange?' and a range 'Orange' that is used in the formula.

    I suspect the way in which I use names makes the workbook difficult for you to follow. If anyone else thinks they can express this solution, or another, using a more conventional approach please feel free to chip in.

    Attached Files:

    Thomas Kuriakose likes this.
  9. Alok Mishra

    Alok Mishra New Member

    Messages:
    8

Share This Page