Using an array to create a list of selected items

Hi,

I've created a range B3:B12 in which each cell returns TRUE of FALSE. There corresponding values are listed in C3:C12. I have the same thing again in D3:D12 and E3:E12.
Checkboxes link to the TRUE/FALSE cells. Attached is a stripped down file to show this.

I want to create a formula that lists each item in the one cell (no spaces required inbetween) for use with a search. I know a long winded way to do this involving many IFs but any array will be much more tidier, thing is I hardly use arrays and have struggled with this one.

I'm thinking it will be along the lines of =IF(B3:B12="TRUE",C3:C12,"")&IF(D3:D12="TRUE",E3:E12,"") but exactly what it should be is beyond me.

As always your help is much appriciated.

EDIT: The original file attached was corrupt for some reason, the new attachedment is the same except instead of TRUE/FALSE cells I've set them to 'x' for quickness (lost my work...!).

ItemsList.xlsx

Using the VBA function at this link:

http://www.excelforum.com/showthread...=1#post3096647

You can then enter this array formula** in G3:

=concatall(IF(B3:B12="x",C3:C12,""))&concatall(IF(D3:D12="x",E3:E12,""))

Ah ha!

Good stuff, thanks for the help! I really do need to get into VBA.



I would add to your rep but the site won't let me (telling me to spread rep around to others first....)

Originally Posted by Harribone

Ah ha!

Good stuff, thanks for the help! I really do need to get into VBA.

You're welcome!

I would add to your rep but the site won't let me (telling me to spread rep around to others first....)

That happens when you try to rep someone twice in a row. I must have been the last person you repped!