Formula to Create a Partial List from a Complete List

[slindfors]

I tried to use the Lookup(Max but that apparently works only for dates. My example is set up based on this attempt and may not be the best way to do what I am trying to do.

Column B is a complete list of personnel qualified to do a job. Checking the associated blocks means they are not available. Based on that information, I want to populate a list of personnel available in column F with no gaps so I can reference the "Available" list in another formula to auto-populate a calendar for scheduling.

[Speshul]

Try this...it ain't perfect, but might put you on the right track!

Select F3:F17 (the Available box) and enter this array formula (while all of those cells are selected)

=INDEX(B3:B17,LARGE(--(NOT(A3:A17))*ROW(1:15),ROW(1:15)))

Hold down Control + Shift and press Enter. The formula will get {}'s around it and look like this in the cell

{=INDEX(B3:B17,LARGE(--(NOT(A3:A17))*ROW(1:15),ROW(1:15)))}

The problem with this formula is it repeats A on the bottom when you start checking boxes. it does remove the letter from the list and shift the rest up, but it adds an A on the bottom for no value...

idk

[Mazzaropi]

slindfors, Good afternoon.

I did this example for you.
18-07-2014_ExcelForum_List_of_Available_Workers-OK.xlsx

Is it what you want?
I hope it helps.

[slindfors]

Thank you both very much. I was able to use the example to get it done!!

[Tony Valko]

Try this array formula** entered in F3:

=IFERROR(INDEX(B:B,SMALL(IF(A$3:A$17=TRUE,ROW(A$3:A$17)),ROWS(F$3:F3))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

[slindfors]

I will check that out as well, thank you!

[Tony Valko]

Tip...

A checkbox "floats" on top of the sheet and does not occupy a cell. Although, when we use checkboxes we typically draw them to the dimensions of a cell(s).

So, instead of using another column for the linked cells just use the cells that the checkboxes cover.

In your case the linked cells would be C3:C17.

Set the font color to be the same as the fill color then the cells will appear to be empty.