Using Check box, Check populates previous cell with date.

[ladyck3]

I've created a great tool (thanks Skip) with help that will automatically place a date in a cell when 3 separate sets of progress scores reach 100% each. The cell is conditioned to turn red if past the desired date and green if on time.
---------------------------
I posted this in the wrong group so here it goes
-------------------------------------------------


Well the thing is, the sections where the user enters the dates which create those 3 sets of scores to 100% each is user defined. Now the end result does place the completed date as the entire project is finished and I think that is sufficient but I've been asked to add a check box and when the step is completed, the user checkes the box.

I see two things needed.Once checked the date is entered in a cell (and then conditionally formatted for on time or past due). However, I'm sure this requires vba right?That's where I say..HELP!

So if I have a check in cell F12 I want the NOW() function (if NOW()is a VBA function) to place the date (hard coded) into E12. I can lock the cell and conditionally format it but is this possible?I do have a concern, won't check boxes make this thing grow HUGE in size and add increased potential for corruption?

I know its squirrelly at best anyway (backup is your friend).If so please help.Thank you in advance..... Oh and I'm sharing the workbook with multiple users.... if that matters.I'm excited to hear more

-------------------------------------------------

Now before I moved this, Skip asked a question... why a check box?

The purpose of this whole workbook is to show accountability and to find the weak spots or kinds in the armor.

As is, the user can enter any date they want, but if we make it a check box to dignify when a task is done, then the date is automatically entered and cannot be 'fudged'.

I think that showing the completion of the project is good but that does keep the individual task dates left up to interpretation or manipulation.

I'll check back later tonight maybe but for sure in the morning.



LadyCK3
aka: Laurie

 

[SkipVought]

Laurie,

Is not THE DATE that is posted, from the process I helped you with, entered by the program? We can make that date 'tamper proof', if it is not already. I just cannot understand how another checkbox will add value, UNLESS there is a way to indicate, "Yes, THE DATE is there, but WAIT! Something's not correct." If that is the intent, then you need a set of Option Buttons indicating "Yea or Nea"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Here is what I have currently working.

Target date which feeds all 'required due date' fields is in cell B8

In column D cells D1237 show required due date for each task (some are 45 days prior to B3 some are 1 day, some are day of)


There are 3 groups (1, 2, 3)

As each group's tasks are completed by entering a date manually, the percentage of work completed increased to 100%

Group 1:
Cells e12:e19 and E21:E23 - when dates are maually entered and all fields are complete in that group Cell E7's percentage increased gradually to 100% When the group completes its tasks and 100% is relalized, the date is automatically added to G3 (date of completion/100%)

Cell D3 changes to 33% when Group 1 is at 100%

(The sheet I am looking at, Group 1 is the only one with entries and its just 1 entry so Group 1 is at 9% but the overall (E7) is at 3%


Group 2:
Cells E28:E31 currently empty but same scenario to 100%

This changes D3 to 66%


Group 3:
Cells E34:E37 Same as above.

When Cell D3 changes to 100%, the date is automatically entered into E3

=============================
Here's what I'm being asked to do.....

Rather than have the individual responsible party manually enter the date, I'm being asked to put a check box in the corresponding cells in Column F. When the check box is ticked... the date it is ticked is automatically entered into the corresponding cell in Column 3 (where they are currently entering any date).

Its all about accountability and its a new process. Its part of trying to find out where the kinks are... but my point is this... no matter what date you enter in these cells, the ultimate dates are when the group reaches 100% (already automatically entered) and when the project is completed (the biggy and therefore shows ultimate final date) so why the dates in between ... they can fudge all they want, right? The tell tale sign is group actual completion and project actual completion.

Each group has its own responsible party depending on the project so what's the big deal. And correct me if I'm wrong but won't this bloat the spreadsheet too? There are already approx 40 worksheets in this workbook and it will grow as new products come along so ...

I think this is just extra stuff we may not need... what are your thoughts?

How monumentous is this? I'm thinking its something I should get a handle on just in case I can't talk them down. If its REALLY a LOT it may be WAY WAY over my head anyway, right Skip?

I really think the fail safes are already in place and any date fudging really doesn't matter it will show when the group score completion date is generated, right? I mean if all tasks are due to be completed by 2/2 and you enter 2/1 into all of the cells but the completion date shows 2/3... SORRY... you falsified, somewhere, right?

Please advise....


LadyCK3
aka: Laurie

 

[SkipVought]

Please post the current logic for where THE DATE is entered by the program.

As each group's tasks are completed by entering a date manually

These dates can be made to be 'tamper proof' once the date is manually entered.

However, if you want to 'lock down' this application, so that on one can change dates once they are entered, you have to 1) make a 'secret' back door and 2) make it impossible for anyone to open the workbook without enabling macros, cuz, if macros are disabled, ANYONE can change ANYTHING.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

Well trust me, the group working with this has no clue about macros so to disable them is probably not something they'd do. The users are not one of them excel saavy... they can open a spreadsheet but that's about it. The more I think about it... I seriously do not think this is warranted... if nothing else, with the current code I might be able to figure out how to have them just enter an "x" in the F column and the date auto populate (with the code you've already given me? Maybe?

Let me chew on this a bit... I know without a shadow of a doubt that you can do this... optimum word YOU ... but I don't think we need to go to that nth degree.

I'll get back to you one way or the other later today. Thanks for being a sounding board


LadyCK3
aka: Laurie

 

[SkipVought]

Post back anytime.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ladyck3]

WOO HOO, they listened to "my" voice of reason... we're scrapping the checkbox option. And one of these particular "gotchya" moments happened just this morning... a supervisor did not enter his 1/22 due stuff til this morning 2/10 and it shows Gotta love it

Thanks again....
I'm sure you'll 'see' me again.

LadyCK3
aka: Laurie

 

[SkipVought]

Good! Always make your case thoughtfully, with solid logic, and you'll see some victories!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}