• 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...

  • 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

Alok Mishra

New Member
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.
 

Attachments

Alok Mishra

New Member
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
 

Peter Bartholomew

Well-Known Member
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.
 

Attachments

Peter Bartholomew

Well-Known Member
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.
 

Attachments

Alok Mishra

New Member
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.
 

Peter Bartholomew

Well-Known Member
@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.
 

Attachments

Top