Forum Discussion
excel how to display table with list of members
There is no need to manually set every filter function. Since there are relative references to table number, you could easily complete every table sheet by just copy and paste it.
And you don't have to reference one column in every filter function. Below should be better.
=FILTER(List[[Guest Name]:[Catoegry]],List[Table '#]=I9)
I have a question, may I know how display name that are "Award" from a range table range
to show which table and name that are = Awards in a table to display
the names and it's table number
thanks
- thank you. this works.
There is no need to manually set every filter function. Since there are relative references to table number, you could easily complete every table sheet by just copy and paste it.
And you don't have to reference one column in every filter function. Below should be better.
=FILTER(List[[Guest Name]:[Catoegry]],List[Table '#]=I9)I user the fitler and the table display automatically
is there any way not to show the SPILL and
the arrangement was auto pick, they empty cells are showing follow by names in the guest column, how can i not sure the empty cell in the display table with the formula? Thank yoyu
Thank you for your help Ezio-de-Pazzi
can I also check I have another list of names with team member in rows
with Data and I like to display in the list tab
The team members and it's department
Can advise how to use formula to pull from the data as attached file? Thank you
Thank you for your help.
can I also check I have another list of names with team member in rows
with Data and I like to display in the list tab
The team members and it's department
Can advise how to use formula to pull from the data as attached file? Thank you
- LET function is way better than mine!
please check this if I got your point. Cause like L z. said it really confused me for a long time.
It's pretty easy with the FILTER function.
=INDEX(FILTER($A$3:$C$26,$C$3:$C$26=F3),SEQUENCE(ROWS(FILTER($A$3:$C$26, $C$3:$C$26=F3))),{3,1})I strongly recommend you to use Excel Table to structurize data, this way it could automatically resize the data range if you add. Plus, the function would be more understandable.
=INDEX(FILTER(List[[Table '#]:[Guest Name]],List[Guest Name]=F3),SEQUENCE(ROWS(FILTER(List[[Table '#]:[Guest Name]],List[Guest Name]=F3))),{3,1})Hi bbsin
Your sample was confusing as some Gest Names had "Awardx". Highlighted in orange in the attached file is what I modified with the hope this reflects your actuals
in F5:
=LET( flt, FILTER(A3:C26, D3:D26=F3), INDEX(flt, SEQUENCE(ROWS(flt)), {3,1}) )(If it was me I would format your List as a https://www.bing.com/ck/a?!&&p=6301349f8ba69e6dJmltdHM9MTY2NDkyODAwMCZpZ3VpZD0wNjNhNjE4ZS00NDU3LTZhZDMtMTIyNC03M2ExNDU3YTZiN2EmaW5zaWQ9NTE2OQ&ptn=3&hsh=3&fclid=063a618e-4457-6ad3-1224-73a1457a6b7a&psq=excel+format+as+table&u=a1aHR0cHM6Ly9zdXBwb3J0Lm1pY3Jvc29mdC5jb20vZW4tdXMvb2ZmaWNlL2Zvcm1hdC1hbi1leGNlbC10YWJsZS02Nzg5NjE5Zi1jODg5LTQ5NWMtOTljMi0yZjk3MWMwZTIzNzA&ntb=1 - see some benefits https://answers.microsoft.com/en-us/msoffice/forum/all/benefits-of-using-tables-instead-of-ranges/992a6e69-9c06-4b01-9230-1e12a87b81b2 )