if a=1 than add b to c

Hi Ronald,

The biggest complication here is in constructing a 'horizontal' list from items selected from a vertical list.

Here's an alternative that uses Ian's solution as a jumping off point, but constructs the list as it goes along, and needs only simple cell reference for the transfer of that list to Tabel 5.

To start here's the 'above the waterline' view:

Of course, the formula below the smaller table gives a clue to what's going on 'below the waterline," especially with that reference to C13.

Here's the 'behind the scenes' view:

The formula below Tabel 6 is entered in C2, then filled down to the end of column C.

The formula gets the value from the cell above itself, then appends the result of the IF statement.

If checks in column B for a 1. IF there is a 1 in column B, it copies the value from 'this row' of column A, then appends a space. If there is not a 1 in column B, IF returns a null string, which adds no length to the string copied from the previous row.

Results can be seen in column C.

NOTE: as each copy of the formula starts by getting the contents of the cell in the previous row of 'this column', it is necessary to insert a null string into Tabel 6::C1 to prevent a zero at the beginning of the final list. The easiest way is to enter the formula ="" into this cell. (The = will open the formula Editor. Just type the two double quotation marks into the editor, then click the green chackmark to confirm the formula and close the editor.

On Tabel 5, the formula shown above simply picks up the final result from the last cell in column C—C13 in the example.

As a space was aded after each name transferred to the growing list, the end result will include a space after the last word in the list. The easiest place to remove this space is during the transfer to Tabel 5. If that's important, use this variation for the formula in Tabel 5::A2

TRIM(Tabel 6::C13)

Regards,

Barry

Hi Ronald,

here is one way that is "clunky".

Formula in Tabel 6 C2 (and Fill Down) =IF(B2=1,A2&" ","")

The &" " adds a space after each word.

"" inserts NULL (blank) if column B does not equal 1.

Formula in C12 =C2&C3&C4&C5&C6&C7&C8&C9&C10&C11

Formula in Tabel 5 A4 =Tabel 6::C$12

I am sure that there is a more elegant (less clunky) way!

What is your main aim?

Regards,

Ian.

This is easier with a short script than it is with formulas:

Copy-paste script below into Script Editor (in Applications > Utilities)

Make sure Script Editor and Numbers are listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.

Click the triangle <run> button.

Done in a few seconds.

SG

tell application "Numbers"
	tell front document to tell active sheet
		tell table "Tabel 6"
			set theWords to value of cell 1 of (rows whose value of cell 2 is 1)
		end tell
		set my text item delimiters to " "
		tell table "Tabel 5"
			set the value of (column 1's cell 4) to theWords as text
		end tell
	end tell
end tell

Hi Ronald,

"…and i wanted to push the 'helpful' button but for some reason that one is not working anymore"

As the OP (original poster) in a discussion, you get two 'helpful" and one 'solved' to assign.

Often, it's a good idea to hang onto all three until you have a full solution to the issue, then spend them as you see fit. "Solved" brings that post to the top of the discussion, where someone with a similar question can file an answer quickly.

"Helpful" brings a post to just below the post that "Solved" the issue, where that future user will still find it with little effort.

A "helpful" (only one) can be upgraded to a "Solved", but a "solved" is locked in, and can't be changed.

Regards,

Barry