Formula to create a list from another sheet column

Question

Good morning all. I've been trying, unsuccessfully, to create a formula to pull (populate) all names listed in a column from another sheet. The closest I've been able to get is utilizing the formulas below but neither are what I need. Cell linking does not appear to be a good option due to the potential to add additional employees to the list(s). =JOIN(COLLECT({MBC Employee List - Field-Super / FM Name Helper}, {MBC Employee List - Field-Super / FM Name Helper}, <>"")) This formula collects all names but unfortunately joins all the names/text into one cell. =INDEX(COLLECT({MBC Employee List - Office Employee Name}, {MBC Employee List - Office Employee Name}, <>""), 1) This formula collects only the one name in the one specific cell. I would need to manually adjust the formula across 150+ rows. I have master sheets for "field" and "office" employees which both have columns listing employee names. I want the new sheet to pull all listed names in those columns (which will be filtered by a checkbox). I assume I will need an individual column foir each master sheet and then I would create a helper column to aggregate the data from each into one column…which would be used to populate a drop down on another sheet. Can anyone help me with what I am missing?

Chris Walsh · Answer

Thank you all for the comments. I tried the suggestions, but this is still not working as I intended. Maybe my initial explanation wasn't the best. I have two sheets. "MBC Employee List - Field" and "MBC Employee List - Office" which I would like to pull data from to populate a single (combined field and office) drop down list on another/new sheet. Names (and their email address) would populate a drop down to ID responsibility of a task. I have a partial solution using the formulas below but it is not perfect at all. =INDEX(COLLECT({MBC Employee List - Field-Super / FM Name Helper}, {MBC Employee List - Field-Super / FM Name Helper}, <>" "), [Super / FM Auto #]@row) =INDEX(COLLECT({MBC Employee List - Office Employee Name}, {MBC Employee List - Office Employee Name}, <>""), [Office Auto #]@row) Currently, the above formulas each populate a column which is then joined into a combined list. Combining both of these lists of names into a single column of distinct/individual names is where I am stuck. Utilizing the above formulas wants to list the matching employee information in cell row #1. As a hack to temporarily make this work, I've held a few spaces between office and field and numbered the first 20 rows of office as "0" which allows the "combined name" to populate AFTER I delete the formula for the other respective column. Monitoring for new employees is thus a maintenance issue. It seems I will have the same issue with pulling email information to "one" specific column for automation/notification purposes. Any further thoughts/insight would be appreciated.

James Harris · Answer

When requiring a numbered list, as suggested above by Dave, you can make it slightly more robust by doing the following:

Have a helper column called (for example) ONE that has a column formula: "=1". This will put a 1 in every cell of the column.

In row 1 of your IDX column, put the following cell formula (i.e. not a column formula): "=count(ONE$1:ONE1). Drag this cell formula down to the bottom of the sheet. You'll find that the IDX column now has a numbered list from 1 to n.

The advantage of doing it this way is that, if you have new rows inserted into the middle of your sheet, the numbering system will adjust accordingly. And as long as new rows added to the top or bottom of the sheet are done so without a gap in the row structure, the cell formula in IDX will automatically be in place for those new rows.

Thanks,

James

DKazatsky2 · Answer

@Chris Walsh,

The only way I know of to do this requires a little bit of setup, which could be tedious for he larger lists. In the sheet where you are collecting the information, add a column to be used as an index, which you will need to populate manually. This would be numbers 1 to n - make sure to number higher than what you expect the total to be. In my example, this column will be titled "IDX".

In the column collecting the names, use the following formula.

=IFERROR(INDEX(DISTINCT({MBC Employee List - Field-Super / FM Name Helper}), IDX@row), "")

I would suggest trying this on the smaller list first to see if it yields what you are looking for.

Hope this helps,

Dave