Conditional lookup to create a list from a list

Jim B

Hello,

This seems like a very rudimentary ask but I am stumped. Hope someone can help.

Can anyone please suggest a column formula that can bring values over from a sheet based on a conditional lookup to an adjacent cell and list them contiguously on another sheet?

Like so:

Thanks in advance.

JB

Paul Newcome

Is there a reason you are bringing them over to a separate sheet as opposed to using a row report with a filter?

Jim B

Thanks, Paul.

Yes the reason is I don't want to share Sheet 1 with the people who will interact with Sheet 2 as it contains sensitive information.

Any solves here?

Thank you.

Paul Newcome

There is a solution, but it comes with its own issues. Do you have access to Dynamic View or Workapps?

Jim B

Thanks, Paul. I'll try in Dynamic View.

Would still be interested to know what the solve is if you have time to describe - despite the limitations.

Best,

JB

Paul Newcome

You would insert a text/number column that is manual entry and enter the numbers 1 through however many you think you will need. From there you would use an INDEX/COLLECT formula with a cell reference to your number column to pull in each of the entries.

The challenge with this is that the formula generated list will follow what is on the sheet. If a row gets deleted or added in the middle or any kind of sorting occurs, it could change the order of the formula generated list. The challenge with this is that any manually entered data in this second sheet stays on the row it was manually entered on. This means the manually entered data stays on (for example) row 9 even though the original 9th entry on the list is now the 10th entry on the list because of sorting on the source sheet.

Dynamic View allows you to display fields as read only or editable (or hide them from view entirely) without giving anyone access to the sheet that feeds the Dynamic View.

Jim B

Thanks for the explanation, Paul.

Paul Newcome

Happy to help. 👍️