not include some values in arrays, based on a further test

[burnsy180]

Hey guys, right. I'm reasonably newish at excel, but i kinda know my way around.
I'm currently writing a spreadsheet and now i'm stumped at this point.
It's also going to be a pain to try and explain but here goes:

In sheet1 i have a list of items (and checkboxes next to each stating whether they are "DONE" or "INCOMPLETE", and a number relating to that group of item in column I.
In sheet3 i have a list of tasks that relate to those items (by that group number)
In sheet2 i have a To-Do list, looking for "INCOMPLETE" in sheet1 column M, and returning the number from column I corresponding to that particular item, and displaying in sheet2 column A.
The formulas in columns B through D then return values from sheet3 that match the number from column A.

I'm using an INDEX and ROW function to search for these in sheet2
This may be worded weird, but anyway, that part seems to be working fine.

Problem is:
1. When there is an "incomplete" item in sheet1 and no corresponding tasks found, instead of getting just the group number and a blank row, i don't want to include that group number in the list at all.

2. When there is more than 1 task found available for that group number i want to display them all, not just the first one that is found.

Can anyone help? Or at least give me any ideas?

The code i'm using to look for "INCOMPLETE" in one cell, and display the corresponding value from another cell in that row is:

Please Login or Register  to view this content.

So far i've just been using arrays, and IF functions etc, no VBA. I wouldn't have a clue about VBA, but am willing to try!

Thanks to anyone with any insight
Joel

[burnsy180]

bump no repsonse

[Marcol]

Your attachment has had eight views and no responses, that must tell you something!

Let's see if we can get the ball rolling.

1/. Get rid of the checkboxes.
With over 300 of them Sheet1 is virtually useless, how can you easily add or delete rows to your table?
This worksheet uses "Marlett" font to simulate the checkboxes, enter "a" in columns E:G
Note that I have unmerged the cells in what was column E:F. Don't merge cells in any data tables!

2/. Better to have only one variable per cell in Column B "Variable" (See Task 4)
(What is the purpose of the ampersands "&" in this column?)
We can now use this formula in Column H to get your "Group Number
In H3

Please Login or Register  to view this content.

Drag/Fill down

3/. I have put formulae in columns I:K to replicate your checkbox responses and retained your various formulae in Column L.
I think you'll agree that having to change this formula at each group level is a bit of a pain.

4/. See if this perhaps works better
Use Column M as a helper
In M3

Please Login or Register  to view this content.

Drag/Fill Down
In N3

Please Login or Register  to view this content.

Drag Across to Column P then Down as required.
This will now flag if "R/I", "I" or "T" is "DONE" or "INCOMPLETE"
What is the criteria for a group to be flagged "DONE"?

5/. Columns I:L are not required, I have left them in this sheet to let you compare the results.

Check this sheet out and clear up any points, then we can move on to the first step of your enquiry.

[burnsy180]

Hey Marco, thanks for finally giving it a look!
The sheet you've sent back has definately been cleaned up a lot.
I agree the checkboxes are a pain but i'm making this spreadsheet to distribute at work as a progress tracking guide, and will be used by people with no excel knowledge at all. So I kinda want to keep them, as they will need to enter their own particular data.
Speaking of which, any quick ideas on how to align those checkboxes or make them fill the entire cell?

As for the R/I and I columns, those don't need to be taken into account in any calculations (except for conditional formatting when all 3 in a row are checked). Those are only there as a reference after being printed out. I only need to worry about the "T" column

I should just say now that originally sheet1 was merely used as a printed out reference of items that needed to be done. I am only now expanding it into various sheets with formulae.
The ampersands are there just for visual reference, but they explain how a task is flagged "done".
Remember this is only referring to the "T" column.
If there is no ampersand in column B, then having checked any of the items of that variable in the T column will show that variable "DONE"
If there is one, one of the items above it need to be checked, AS WELL AS one in line or below it to be flagged "DONE". (In other words, to be done on that variable, you need to do one item AND another below)
So the formulae in column M just reflects that.

I really appreciate all the assistance with the first sheet (and i will be changing some stuff around as you've suggested), but i've found a roundabout way to solve my first problem of having blank cells in my lookup.
If you have a look on sheet2 on the new attached sheet at columns F, G and H:
F finds the variables that are incomplete
G finds those variables that have a corresponding task on sheet3
H removes the blanks in between

This way seemed a very drawn out way of doing it, but it works.
My next problem was when i have a variable that has multiple corresponding tasks on sheet3, i want to display them all, not just the first one it finds.

mmmmmm....

[Marcol]

I agree the checkboxes are a pain but i'm making this spreadsheet to distribute at work as a progress tracking guide, and will be used by people with no excel knowledge at all. So I kinda want to keep them, as they will need to enter their own particular data.
Speaking of which, any quick ideas on how to align those checkboxes or make them fill the entire cell?

Get rid of them!!!
Your own words "how to align those checkboxes " should tell you why.
If the sheet is to be used by inexperienced users it wont be long before it is a complete mess.

Try this worksheet
1/. Is that how the amphersand should work?

2/. The data validation should prevent user errors.

3/. The C/F doesn't need the TRUE/FALSE columns.

Keep it simple.
How will inexperienced users change your formulae as they add rows or break a group with an amphersand?
Surely formulae that calculate the group numbers and status automatically is a better option. If not, why not?

[burnsy180]

Awesome.
Well i've done as you suggested and checkboxes are GONE, and i've gone for a slightly different means of "checking" the boxes.
I found a macro that upon clicking changes the cell in selected range to "a", instead of typing it in manually.

ALL TRUE/FALSE columns are gone.
The "DONE" value is gone, as i'm only looking for "INCOMPLETE" values.

1/ Yes that is how the amphersand should work, however in your sheet (for eg. variable 2.1) , there are 2 values for above and below the amphersand, so i would need another AND condition to calculate if the entire variable was complete.

2/ Data validation is good, cheers

3/ Yep, T/F columns are gone!

In regards to asking how users will add rows, change formulae etc, the users will not be changing anything on this sheet except for checking the boxes for R/I, I, T. The items are the same for every user, they just specify what they have or haven't done.
So this list of items will not change, which is why i don't think it's necessary to have formulae to calculate the group numbers.
I probably could just leave your formulae there but i don't entirely follow how they work, so troubleshooting if i needed to would be a problem, and at this stage it's easier to just type it in.

So...
Any ideas with sheet2?
Problem being:
1/ If the values in column G have more than one matching task in sheet3, i want to display them all, not just the first found.
This how it currently works:
TO-DO LIST

Variable	Task
2.2	Task 4
3.1	Task 6
4.2	Task 8

AVAILABLE TASKS

Task No.	Variable
Task 4	2.2
Task 5	2.2
Task 6	3.1
Task 8	4.2

Notice in the TO-DO LIST, it will only display Task 4 for Variable 2.2, not Task 5, as it is the first found.
I want it to work like this:
TO-DO LIST

Variable	Task No.
2.2	Task 4
2.2	Task 5
3.1	Task 6
4.2	Task 8

Or even by grouping the variable that has more than one task and having an indented list of tasks next to it.
Sorta like:

Variable	Task No.
2.2	Task 4
	Task 5
3.1	Task 6
4.2	Task 8

But with the gridline underneath "2.2" removed so as to group the tasks to it.
That is ultimately what i want it to do but have no idea how.
ANY IDEAS????
Thanks so much!

[Marcol]

I'm finding it difficult to follow your logic.

I've changed the formula in Sheet1 Column J to include your "And" conditions. Does this work better?

Have a look at the two tables in Sheet2.

The first returns results for all unchecked cells in Sheet1 Column "T", the second returns all unckecked groups.
Is either close to your needs?

Don't worry about the various formulae at this stage, a fair bit is only for testing the examples.
Let's get the sheet working, then I'll explain the ones that survive.

I've also altered your VBa a little to make it a bit more usable.

Check/Uncheck Sheet! Column G to see the changes and differences in the tables.

[burnsy180]

Yeah, those formulas work correctly, that's fine.
Righto, i think this may have gone a little deeper than what I was after.
Although, the cell selection VBA is Much better, can you isolate the part where after selection it selects column C and how to implement that without all the other funky formulae?
As far as i can see, in my last attached sheet, sheet 1 is sorted. The only information i need off sheet 1 is what variables are "INCOMPLETE".
And I believe i've got that in sheet 2 column F.

Then from that list i have to narrow it down to what variables are ALSO in sheet 3 column F.
And that's in sheet 2 column G.

Then i need to get rid of the blank cells in that list.
And that's in sheet 2 column H.

Up to this point i'm happy with the worksheet as is.
I think with the other 2 tables you added is sorta redundant as i already have the data i need in row H.
I don't really need any information about the items or how many items, the variable number is the only thing i need to cross-reference.
Sorry if that sounds a bit rude, i do appreciate all the work you've put in i'm just getting a bit lost.

So if we can just concentrate on the final steps...

In the list in sheet 2 column H, i need to find how many instances of each of those variables are in sheet 3 column F, and repeat that variable that amount of times

So for example:
If say, variable 2.2 had 3 tasks in sheet 3 and variable 4.1 had 1, then display:

Variable	Task
2.2
2.2
2.2
4.1

So that way i could show the 3 different tasks you could do to complete variable 2.2 (as opposed to just one).

Only thing is i'll need to determine the rows on which each of the duplicates occur so i can do an index lookup to get the different tasks.
But first things first, how can i repeat them?