Identifying cells that remained unchecked in a checklist

[SueS]

I have created a workbook that allows teachers to identify what their current programs are addressing and therefore what is missing from their programs. I would like to have a separate worksheet that will identify the parts that have not been checked so they don't have to look through the checklist themselves.

I have attached a sample of the workbook, but the original has another two worksheets for different year levels. Teachers list their units of work at the top and mark off what outcomes and content is covered by that unit. What I'd like shown in the additional worksheet is any outcome or content area that has not been clicked across the row at all. Eg. On the first page in the first worksheet if no cell between c4 and t4 has been clicked the outcome/content:

Communicating
COES1.1
Expresses feelings, needs and wants in appropriate ways

would automatically be displayed as not being covered.

There is already a code that allows these cells to be clicked and an x appears. Not sure if this impacts.

I'm no expert obviously but I was thinking if you highlighted the range of cells and then somehow make the worksheet understand that if none of these cells have an 'x' in them, the information in the cell prior to that range would be copied into the last worksheet 'Areas not addressed'

Is this possible?

Thanks in advance.

Sample mapping grid.xlsm

[mehmetcik]

Hi Sue

There are numerous ways of doing this.

I personally would have to use a macro. However some of the guys here are experts in using array formulae and that might be more elegant.

If you do not get a response within 24 hours then I'll do something for you.

[SueS]

Thanks mehmetcik,

Looking forward to suggestions

Sue

[SueS]

In addition to this, if it is at all possible, I would also like teachers to be able to click a button/cell at the bottom of the page to activate the review of the outcomes covered. So once they have completed filling out the checklist, they click the button and the macro (or whatever) is enabled to show what is missing on the 'Areas not covered' worksheet, rather than it being activated the whole time, which is unnecessary. If that makes sense.

[mehmetcik]

Hi Sue

Sorry about the delay.

This is just a first pass

selecting the sheet issues not addressed will run a macro that will populate the sheet with a list of issues not addressed.

The macro can also be called directly by running the macro "NotAddressed"

You can create a button linked to this or better still assign a key to run it. Eg Ctrl n.

I only created it for one of your sheets as the second sheet looked to be corrupted.


I need your instructions on what to do with this next

[SueS]

Thanks mehmetcik, sorry I haven't had a chance to reply earlier.

I can see what you have done, but I'm not sure it works the way I want it to or I just don't know what I'm doing (probable). I clicked in the cells to mark that a particular outcome had been addressed (eg. COES1.1 Expresses feelings, needs and wants in appropriate ways.) But this outcome is still identified as not addressed on the Areas not addressed sheet.

I've had time to think about it and I think a button with the macro assigned would work best. I know how to do this, just not how to create the macro that does the job needed. The other option is, instead of having a separate sheet to show areas not addressed, the actual outcome/content cell could be highlighted. Not sure if this is easier to create, if at all. So for example if COES1.1 .... is not marked with an x anywhere along the row, when the button (with the assigned macro) is clicked, the cell containing COES1.1 is filled with yellow (or something along those lines).

I just need a way of identifying rows that have no 'x' at all in them, after teachers have completed the sheets.

Thanks for your help, I really appreciate it. It's a bit frustrating because I know what I want to do, just no idea how to do it

Sue

[mehmetcik]

Hi Sue

OK Step change.

If that is all you want then you could probably do that with conditional formatting.

BRB

Select each range C2 to T2 that you want formated

click on conditional format

Select format using a formula:

Paste this formula there:

Please Login or Register  to view this content.

Click on format

select Fill and then select yellow.

Now click apply.

Thats it.

now you can edit the range that the format applies to:

presently the format applies to C2:T2

You could change that to C2:C168

or $C$2:$T$2,$C$4:$T$10, $C$12:$T$20 [ Skips two rows ].

But it will include a few areas that you are not interested in.

eg rows 3 an 11

so you will need to select an area at a time and enter the code there.


NB: you can paste the formula in excel or word and use replace to modify the formula.

eg replace 2 with 4

[SueS]

Hi mehmetcik,

Sorry I haven't replied as yet, I have had to work on a separate project and I'm only just getting a chance to get back into this one. I really like what you've done, and I'll have a play around with it at some point today. The only thing that would make it even better would be if it was the actual outcome/content that was filled yellow (or whatever colour). Eg. click in C4 and A4 changes to no fill, if unclicked across c4 - t4 then a4 is filled with colour. Make sense? I'll still have a look at it today and see how I go applying that formula, although there are a lot of areas to apply to so it will take me some time.

Thank you so much for your help on this, it might always take me a while to get back to you with all my other projects going on but I really appreciate the time and effort you are giving to help me.

[SueS]

Hi mehmetcik,

I've been trying to work this conditional formatting out for a couple of weeks and I'm just not getting it when I try to apply it to different cells. I'm missing something important. But I have figured out I can copy one of the cells you have applied the conditional format to and paste the formula into another cell or group of cells and it works. Do you know if there will be a problem somewhere down the track with this or is it as simple as it seems? I have copied a cell in the sample you sent and applied it to another worksheet I have and its working as it does in your sample, is this enough?

Thanks
Sue