Any XL Xperts in?

TTony

This has got me stumped.

I understand that the cells are shaded according to the conditional formatting rules.

But I can't understand how those rules have been defined.

Any help???

Phil_aka_Pip

I'm looking at it with Libre Office, and it's not giving away any clues as to where those formulae or conditional formatting rules are defined.

Sometimes you can dig inside a spreadsheet by dumping it in hex and looking for the names of things you recognise. Can't say that has helped here either. Neither has an attempt to export the spreadsheet as a csv file.

 

TTony

Thanks for looking @Phil_aka_Pip, but I think it's going to need looking at in "proper" Excel to reveal its secrets.

digitalscream

I can't see everything that's going on (not familiar enough with LibreOffice), but I can see plenty of conditional formatting and defined styles - looks like it's all done purely like that, no VBA involved. At least...no VBA involved in the main view - the period selection doesn't work in LibreOffice, so I presume there might be some VBA there.

stickyfiddle

I can't see any VBA in there looking in excel 07

I was literally doing this manually last week.

I do it with numbers in each cell:

blank = no activity

1 = plan

2 = actual

or whatever, then use conditional formatting to set number and cell the same colour depending on its value

I assumed this did the same thing with If statements to populate the cells according to start period and length, but there's no formulae in the cells, so not sure how it's doing it.

TTony

I don't think there's any VBA.

I understand how conditional formatting works in general, but what I don't understand (I have a brain addled with cold medications and lack of sleep at the moment) is the formulae / names that set the conditional formats in this specific worksheet.

There are custom names defined, and maybe custom formulae too, both of which seem to drive the conditional formatting.

stickyfiddle

I've looked some more. It's all in the range names (Ctrl+F3 to see them). They're cleverly defined such that they move according to what inputs are put in. I've not seen that done before. 

dafuzz

If you go to the formulas tab and click Name Manager... is that what you mean? I'm no excel bod tbh

TTony

stickyfiddle said:

I was literally doing this manually last week.

I normally do it semi-manually do (using IF statements to put a value in the cells, and then conditionally format the cells based on the value in them).

But the MS Template is far cleverer, more flexible, and more powerful.  Also, as you say, there are no formulae in there, so it's all done by using formulae within the conditional formatting rules.  So, I wanted to understand how its working, and change the horrible colours/patterns that they use.

I've used dynamic range names before (they're also very useful & powerful!) but my head just isn't understanding what's going on with this spreadsheet!!

Paul_C

is there any VPL?

dafuzz

The colours are defined in the Conditional Formatting section of the Home tab...

What specifically do you not understand / are you trying to accomplish?

stickyfiddle

TTony said:

stickyfiddle said:

I was literally doing this manually last week.

I normally do it semi-manually do (using IF statements to put a value in the cells, and then conditionally format the cells based on the value in them).

But the MS Template is far cleverer, more flexible, and more powerful.  Also, as you say, there are no formulae in there, so it's all done by using formulae within the conditional formatting rules.  So, I wanted to understand how its working, and change the horrible colours/patterns that they use.

I've used dynamic range names before (they're also very useful & powerful!) but my head just isn't understanding what's going on with this spreadsheet!!

It's basically all in the range definitions. Then each range gets conditionally formatted to a different colour. I'm pretty sure that''s all it is.

All of I9:BP34 is being formatted so that if it's defined as "Actual" then it goes light purple, etc. Formula in conditional formatting is just " =Actual "

TTony

dafuzz said:

What specifically do you not understand / are you trying to accomplish?

I'm struggling to understand how the rules (that determine how the various conditional formatting is applied) work.  

The rules use the named ranges, and the ranges are named dynamically.

Most of the dynamic ranges include a reference to the named range PeriodInPlan and/or PeriodInActual, so I need to understand how those ranges are defined.  Looking at the formula, it's not too complicated, I just can't get my head round what it's actually doing, because I'm being thick today.

And I'm trying to understand this so that I can use this template in some work I'm doing.  I don't want to use a template that I don't understand fully!

The "highlight" function is pretty neat too, IMHO.

dafuzz

Ah fair enough. More coffee may be the solution!

musicegbdf

http://office.microsoft.com/en-gb/excel-help/quick-start-apply-conditional-formatting-HA010370614.aspx

does this help ? or is this too basic..

TTony

musicegbdf said:

or is this too basic..

Just a touch ... but thanks for the thought!

BigMonka

wow, the formulae for defining the ranges are well over my head, but as you said the conditional formatting using fairly simple formulae as it just uses the ranges. My mind is blown (and I consider my self fairly competent in excel)

samzadgan

TTony said:

dafuzz said:

What specifically do you not understand / are you trying to accomplish?

I'm struggling to understand how the rules (that determine how the various conditional formatting is applied) work.  

The rules use the named ranges, and the ranges are named dynamically.

Most of the dynamic ranges include a reference to the named range PeriodInPlan and/or PeriodInActual, so I need to understand how those ranges are defined.  Looking at the formula, it's not too complicated, I just can't get my head round what it's actually doing, because I'm being thick today.

And I'm trying to understand this so that I can use this template in some work I'm doing.  I don't want to use a template that I don't understand fully!

The "highlight" function is pretty neat too, IMHO.

do you want to email it to me and i can have a quick look?

TTony

samzadgan said:

do you want to email it to me and i can have a quick look?

Done

[edit]

@samzadgan

I've just sent a simpler version of the original template.  If I can understand how this works, I'll be able to work out the rest.

mart

TTony said:

dafuzz said:

What specifically do you not understand / are you trying to accomplish?

I'm struggling to understand how the rules (that determine how the various conditional formatting is applied) work.  

The rules use the named ranges, and the ranges are named dynamically.

Most of the dynamic ranges include a reference to the named range PeriodInPlan and/or PeriodInActual, so I need to understand how those ranges are defined.  Looking at the formula, it's not too complicated, I just can't get my head round what it's actually doing, because I'm being thick today.

And I'm trying to understand this so that I can use this template in some work I'm doing.  I don't want to use a template that I don't understand fully!

The "highlight" function is pretty neat too, IMHO.

PeriodInPlan is telling you whether the selected period is after the planned start date and before the planned end date. It does this by taking three numbers: the selected period, the start period, and the end period. It takes the median of those, i.e., the middle of the three values, and compares that with the selected period. If they are equal, then the selected period is the median, i.e., is in between the other two. It's a slick way of wrapping two less-than-or-equal checks into one comparison.

PeriodInActual is exactly alike.

Does this help?

TTony

@mart

I think that's the explanation I've been looking for!!

The use of named ranges in this way isn't something that I've encountered before.

If I'm understanding it all correctly ... 

The PeriodInPlan name assesses whether the period number is equal to the median value of (period number, start period, end period) - exactly as you say.  The result/outcome of PeriodInPlan is presumably either true or false.

If true, then the definition of name "Plan" is also true (provided that the activity actually has a start date assigned to it), and the conditional formatting defined as "Plan" applies to that cell.

If I'm understanding it all correctly!

So, rather than using formulae within the cell (which would get incredibly complicated given the range of possible conditions that is being represented by the shading), it's applying logic to determine which condition would apply, and then assigning the result of the logic to each cell via the cell's inclusion in a named-range. 

That put my brain through some exercise.