Forum Discussion
Excel Lists
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.
Good point. I accept that. There was a time when most of my workbooks used VBA event handlers (MouseMove etc) to animate diagrams built using 3D shapes. I came to think of the online version of Excel as having all the utility of a chocolate teapot, so tended to neglect it. Much has changed since then.
