I have the following table:
| A | B | C | |
|---|---|---|---|
| 1 | Team | ||
| 2 | Team1 | Working hours | 10 |
| 3 | Name | Ronald | |
| 4 | Team2 | Working hours | 20 |
| 5 | Name | Magda | |
| 6 | Team1 | Working hours | 30 |
| 7 | Name | John |
- Column (A2:A7) represents a dropdown list of {Team1, Team2}
- Cell A1 selects the filter for that
I want now select in cell (A1) all data from Team1, to have a table looking like:
| A | B | C | |
|---|---|---|---|
| 1 | Team: Team1 | ||
| 2 | Team1 | Working hours | 10 |
| 3 | Name | Ronald | |
| 6 | Team1 | Working hours | 30 |
| 7 | Name | John |
Does anyone know, if that is possible?
- A solution would be, to fill every cell of column A with the Team value. However, this is not what I like
- Another solution (I guess) is to connect these cells, but this makes it rather complicated if I want to add another attribute per team member (besides Working hours and Name)
CodePudding user response：
I suggest you change your data orientation to be like this (check below picture):
I think it should do the job
CodePudding user response：
How about the following?:
=IF(FILTER(A2:C8,(A1:A7=E1) (A2:A8=E1))="","",FILTER(A2:C8,(A1:A7=E1) (A2:A8=E1)))
This solution requires Office365. The length of both ranges in the filter criteria need to be the same size (row numbers) as the range you want to filter. The range Just shifts (offsets) 1 row to have the second row showing.
Added an IF-argument to show blank cells as blank (they would appear as 0 otherwise).
