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

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:

=IF(ISERROR(INDEX(Sheet1!$I$1:$I$112,SMALL(IF(Sheet1!$I$7:$M$112="INCOMPLETE",ROW(Sheet1!$I$7:$I$112)),ROW(1:1)),1)),"",INDEX(Sheet1!$I$1:$I$112,SMALL(IF(Sheet1!$I$7:$M$112="INCOMPLETE",ROW(Sheet1!$I$7:$I$112)),ROW(1:1)),1))

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

bump no repsonse

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....

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!

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?