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.