Forum Discussion
Excel Lists
I am looking to take a worksheet of various names and copy/sort those names to 5 other sheets, i.e. all of the names Bill appear on the same worksheet, all the names Mike, appear on the same worksheet, etc. How can I do this?
Attached is a sample workbook. This method uses the FILTER function.
I am not sure I understand "I cannot do an = formula to have what is in one cell show up in another, because project locations move within the lines of the worksheet, closed projects go away, new projects are added". Possibly, a statement that more than a simple relative reference is required.
If the initial "ProjectList" were an Excel Table then it grows and reduces with the data it contains and reference to entire fields can be made as structured reference. I also did the filtering by referring to the tab Name.
What was cute was the way
= LET( name, TEXTAFTER(CELL("filename",A1),"]"), filteredByName, FILTER(ProjectList,ProjectList[Project Manager]=name), DROP(filteredByName,,1) )works for each name, including newly defined ones. TEXTAFTER and DROP are going to be an issue for a while though.
TEXTAFTER and DROP is temporary issue, but CELL("filename") is more serious one since it doesn't work in Excel for web. More and more often, especially in corporate environment, people use both. Possible scenario is to open in browser to check something or do small changes and from it, if necessary, open in desktop app for more heavy tasks.
- Thank you Patrick. I will have to figure out how to open this. My work firewall filtered it. I can't open it.
Here are two good sources for understanding the FILTER function. So if you can't open Patrick2788 's example, perhaps you could create your own.
https://exceljet.net/excel-functions/excel-filter-function
https://www.youtube.com/watch?v=9I9DtFOVPIg
The "this" that you are wanting help to accomplish is not at all clear. Why don't you start with a far more clear description of the raw data and the desired outcome.
What's the purpose of the sort to the 5 different sheets in the first place? It's not clear, for example, whether you WANT all of the names of "Bill" to appear on the sam worksheet or if that's what you're hoping to overcome.
I have a construction company. I have a worksheet that lists my 53 projects. Each project/listing on that worksheet has a Project Manager name associated with it. Any one Project Manager may have five or six projects to handle. So as I look through that list of 53 projects, I see the names randomly. I would like to take each of those names/entries and have them also show on another worksheet/"summary" worksheet, thus a worksheet with all Bill's jobs, a worksheet with all Dan's jobs, a worksheet with all of Mike's jobs, etc. I cannot do an = formula to have what is in one cell show up in another, because project locations move within the lines of the worksheet, closed projects go away, new projects are added. Thinking of an IF formula; if a range of cells has the name Mike in it, then the info will show in the cell within the "summary" worksheet. Sorry, does this explain it better?
