Any XL Xperts in?

digitalscream

Yep, that's it. I presume you've understood the idea behind the OFFSET function in dynamic named ranges? That's the only tricksy bit as far as I can tell - the rest is just stacking logic on top of more logic, so it's more a "find the starting point" problem than anything.

mart

@TTony

I've never seen anything like this before, but I like a nice puzzle! 

I was thinking of these as logical states attached to a given cell. Implicitly that defines a named range of cells, but I was thinking of it in a cell-centric way. From that point of view, yes, for a given cell PeriodInPlan is either True or False according to whether or not the time period of that column is within the planned time for the activity for that row. Similarly, Plan is then either True or False according to whether the period is within the planned time, and yes, there's a basic check that the start time is positive.

What bothered me in PercentCompleteBeyond was that logical states of True and False were being multiplied with numbers, and also that the results were being added. So I think there's some slightly dirty programming going on in the midst of what is basically very elegant. But it is Excel after all.

holnrew

I wear XXL I'm afraid.

TTony

digitalscream said:

I presume you've understood the idea behind the OFFSET function in dynamic named ranges?

I have indeed - I've used OFFSET plenty of times to do all sorts of weird spreadsheety things!  If they'd used that approach here, I might have understood it more easily ....

I've never even thought of assigning a cell to a named range, and then conditionally-formatting it, based on "invisible" logic (invisible in that it's not represented by in-cell formulae).  Damn clever.  And confusing.

It's a template from the MS site, I think they have clever people there ...

TTony

mart said:

What bothered me in PercentCompleteBeyond was that logical states of True and False were being multiplied with numbers, and also that the results were being added. So I think there's some slightly dirty programming going on in the midst of what is basically very elegant. But it is Excel after all.

I've not looked at that part of it yet - and might not as I'm not sure that it's directly relevant to what I need to do with the template!

I'm going to re-build it from the basic version that I've now created, and extend it as I need to add more functionality into it.

Feel like I've learned something today ...

mart

TTony said:

I've not looked at that part of it yet - and might not as I'm not sure that it's directly relevant to what I need to do with the template!

I'm going to re-build it from the basic version that I've now created, and extend it as I need to add more functionality into it.

Feel like I've learned something today ...

It's really just more of the same. Once you've grasped the tricks in the PeriodInPlan and Plan definitions, there's not much more going on. 

But yeah, building your own version of that template is the way to make sure you fully understand what's going on. I haven't dared try that yet ....

TTony

mart said:

But yeah, building your own version of that template is the way to make sure you fully understand what's going on. I haven't dared try that yet ....

I've just used the same technique to add an indicator to the gantt chart that shows milestone dates onto the plan.

Cracked it.

Thanks again @mart and also @samzadgan who was resolving it via email at about the same time ... theFB does Excel support!!

mart

tFB excels itself again.

musicegbdf

TTony;410151" said:

musicegbdf said:

or is this too basic..












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

Sorry was a bit beyond me anyway, but has made me curious.
I tend to live in the pivot table world, which I find really clever and helpful.

TTony

musicegbdf said:

I tend to live in the pivot table world

I've spent time there too

If you get bored sometime, download the (Microsoft) template that I linked to in the original post and see if you can figure out what it's doing - and how it's doing it!  Clever ...

Emp_Fab

See... this kind of shit is exactly why we should all become Amish.

stickyfiddle

I don't like pivot tables, but I do use them. 

I'm a financial modeller for about 50% of my job, so use excel a huge amount. I spent a good 5 months of this year on one model... I actually quite enjoy it.

crunchman

Depends how it's set up and what you need to do but sometimes I find VBA to be quicker to easier and quicker than conditional formatting.

I did something for my boss the other day that would have required about lots of different conditions being entered into conditional formats.  It was quicker in VBA, and you can also see everything in one place in your code.  With conditional formatting you have to edit each rule individually.

Having said that, I'll probably download that and have a look at it.  It sounds quite cool. 

TTony

My trouble with VBA is that I use it so infrequently, that I have to re-teach myself everything each time I do.

crunchman

I've been using it on and off for 15 years.  There are some things I do that I'm sure could be done in the spreadsheet on the more modern versions of Excel but by the time I've figured out how to do it it's quicker for me to do in VBA.

It's also easier sometimes when you have to go back 6 months later and understand what you've done if you write half decent code and use sensible variable names etc.  If all you have is a bunch of formulae referring to cells and ranges they can be a pig to go back to and work out what's going on.