Excel dynamically add fields to row range

Question

Grettings People of the Stack!

I have a question… Is it possible to dynamically populate a column of cells on one sheet… based on a list on another worksheet by placing an “X” or something in the Cell next to the desired data?

I have a list of approx. 100 or so Items on Sheet2 where a user can select what Items they would like to appear in a list on Sheet1. I’d like it if I could place a check or an X next to the item on Sheet2 and have that item dynamically populate in a range of rows on Sheet1

Example:

**Sheet1**
|Apple|
|Pear |

**Sheet2**
|X|Apple
| |Banana
|X|Pear

Answer 1

Try,

=INDEX(Sheet2!G:G, AGGREGATE(15, 7, ROW($2:$99)/(Sheet2!F$2:F$99="x"), ROW(1:1)))
'alternate dynamic X range
=INDEX(Sheet2!G:G, AGGREGATE(15, 7, ROW($2:$99999)/(Sheet2!F$2:INDEX(Sheet2!F:F, MATCH("zzz", Sheet2!F:F))="x"), ROW(1:1)))

Caveat(s):

1. You need to reference enough cells in sheet2 to cover all of the possibilities.
2. You need to fill enough of the destination column to cover all possible matches. Excel does not have an ARRAYFORMULA function like google-sheets.
3. If you don't want to end up with #NUM! errors when you run out of matches, use an IFERROR wrapper.