I would like to extend the formula I currently have so that the user is able to select the information required, eg instead of being just Name and Age as in the output range, it could be Name and Dept or Name and Salary or Name, Age and Dept. The only constant required in the output range is the Name field. I have included a Data Validation cell to gather that information but it only allows for one cell entry so perhaps getting Name, Age and Dept may be asking too much, if that is the case I'm happy for it to be restricted to just 2 bits of information eg Name and Salary or Name and Age etc.
It would also be desirable if it doesn't cause too much complication that the field headings are automatically produced so that if Name and Age are selected then the output range contains the field headings Name and Age.
It would also be desirable if it doesn't cause too much complication that the field headings are automatically produced so that if Name and Age are selected then the output range contains the field headings Name and Age.
| Book1 | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | |||
| 1 | Dept | HR | |||||||||
| 2 | Name | Salary | Age | Dept | Info required | Dept | |||||
| 3 | Tom | 450 | 65 | HR | Output | ||||||
| 4 | Jane | 700 | 80 | Sales | Name | ||||||
| 5 | Pete | 950 | 50 | Admin | Tom | 65 | |||||
| 6 | Harry | 1200 | 40 | HR | Harry | 40 | |||||
| 7 | Fred | 1450 | 25 | Admin | |||||||
Sheet1 | |||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| H5:I6 | H5 | =FILTER(CHOOSE({1,2},$A$2:$A$7,$C$2:$C$7),$D$2:$D$7=$G$1) |
| Dynamic array formulas. | ||
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| G1 | List | =$D$3:$D$7 |
| G2 | List | =$B$2:$D$2 |
