Need Help with Code Logic (2003/SP1)

[kmonaco]

I need help from the Excel gurus again. I know exactly what it is I need Excel to do for me, but I can't seem to get the program to understand! The VBA interpreter doesn't seem to understand the syntax of "PLEASE.just.do(ThisOneLittleThing).ForMe" for some reason. Basically, my client wants Excel to work like MS Project; when you indent (in Project; add a child in my Excel sheet), the dates adjust according to the child entries.

I've attached a workbook that hopefully shows the problem in enough detail for you to understand. Basically, I need to keep an eye on columns 1-4, and if the user creates or deletes child items, I need to add or remove formulas to the parent items in Col. 6, 7, and 9. It's a little more complex than that, but not much. What I need to figure out is a function or routine to use to determine child (or parent) status and a function or routine to use to set the right hand side of the range expression. And, I guess, how it all works together.

I can sort of get the first one: If you're in column 1, you're a parent. If you're in column 4, you're not. If you're in column 2 or 3, you MAY be a parent, if there is an entry one row down and one column over. It would take me awhile, but I could eventually get that working. Maybe. But, so far, I haven't figured out how to UNDO it if the user deletes child rows.

The second function, figuring out the range value, is more troublesome for me. The range has to extend from the row directly beneath the parent entry to one entry above the first non-blank entry in the same column as the parent identifier, but I'm not sure how to capture that. I could probably figure out a way to do it if I had the option of entering hard-coded values from the VBA code into the formula, but I was hoping for a generic formula that would dynamically adjust to allow for additional child entries. And maybe that's the wrong approach; maybe I just need to revise the formula each time a child is added. But I'm still stuck regarding how to approach the program flow.

Hopefully, I've deleted all the superfluous stuff from this sample, but still left enough to show you what I'm trying to do. I didn't stop to remove or change the data validation set up in some of the cells. Just remove it if it gets in the way.

Many thanks!

[HansV]

Can you try to explain very slowly, in great detail, for people who don't have the slightest clue what your spreadsheet means, where you want formulas and what they should do, and when they should be changed?

[kmonaco]

I guess I left out the part about what I was actually trying to do, huh? Forgive me. Let's see if I can do a bit better, and if this doesn't clear things up, I'll try again in the morning when I

[HansV]

The obvious question is, if the client wants it to look exactly like MS Project, and to act exactly like MS Project, why doesn't he use MS Project?
You're going to spend an enormous amount of time and effort, and therefore money, trying to make Excel work in a way it wasn't really designed for, and that MS Project can do right out of the box, without any programming.

[kmonaco]

Well, there is a VERY long story behind that. My client would LOVE to just tell everyone they have to use Project. But there are many reasons why that won't work right now, one of which is the HUGE cost of purchasing Project for all the engineers who don't already have it . A few days of my time figuring this out is a drop in the bucket compared to that expense. The other is the fact that many (and I mean A LOT) of the engineers don't like Project, don't understand Project, don't have time to learn Project, don't want to learn Project, don't need more than a minuscule part of the feature set offered by Project, and wouldn't use it even if they had it. And since our company culture supports the "do it however you want as long as you get your job done" philosophy, there is no one to tell them they HAVE to use Project. So, we're trying to woo them there by offering them pieces of what Project can do, in an environment that is familiar and comfortable and non-threatening (Excel). Once we have them building schedules AT ALL, since many of them do not currently do it at all, the hope is that they will start saying, "You know, this Excel tool is really nice, but it sure would be cool if it could do such-and-such." And we will respond by saying, "You know, Excel can't do that, but let me show you how this works in Project." And, one by one, they'll be hooked. And the more my tool acts like Project, the easier the transition will be: "See, Project isn't so hard. It works just like that Excel tool you've been using."

Explaining it all last night helped generate some ideas I'm going to try this morning. Any suggestions would still be welcome. Many thanks!

--Karyl

[kmonaco]

If anyone is interested, I think I got this mostly figured out this afternoon. I haven't written the code, yet, but I have the formulas done and most of the logic figured out. You can see it on the attached document (still under review). The hardest part was figuring out how to dynamically pick up the dates from the children without knowing the bounds of the range. I'm sure there is a better way to do it, or at least perhaps a less verbose one, but this is what I came up with:

{=IF(COUNTA(RC5:R999C5)=1,StartDate,IF(MATCH(TRUE, NOT(ISBLANK(R[1]C1:R999C1)),0)=1,StartDate,MIN(INDIRECT("R[1]C:R["&MATCH(TRUE,NOT(ISBLANK(R[1]C1:R999C1)),0)-1&"]C",FALSE))))}

Translation:
If the count of entries in Col. 5 from this row down = 1 (which means the user has entered a description, but there are no other items below), enter the Project StartDate (a named cell).
And, if the first non-blank cell below this item is directly below (=1, so there are no children), then enter the Project StartDate.
Otherwise, use the earliest date (MIN) from the range of the cell directly below (R[1]C) through the first non-blank cell - 1 (the Row # of the last blank cell).

The formula had to be modified to check for different branches, but it all basically worked the same with different column references. And the End Date formula used MAX instead of MIN. I had a terrible headache by the time I was done, but I was pretty impressed with myself that I figured it out at all! And in only (!) three hours! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Tomorrow I'll add the code that keeps an eye on the sheet and that should be it. (Of course, it never is "it," but I'm hoping to get it running well enough tomorrow to give out to the beta testers. Thanks for the forum in which to talk this all through. It was writing the requests for help, and trying to explain what I was doing, that helped me determine an approach.

--Karyl