drom
Well-known Member
- Joined
- Mar 20, 2005
- Messages
- 546
- Office Version
- 2021
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
hi and thanks in advance!!
My table contains a slicer To show Name (The Xl2BB, does not show the Slicers, or I do not knot how to show it, anyway does not matter)
Anyway when the slicer shows only 1 value, not multiple options
My job is done brvoudr thr Formual on C10 (green color) shows the Value A
Now when the clear the filters in this slicers and or in my table
I get with the formula on C10:
CHECK C11:C14
I would like to avoid C11:XXXX in my C10 Formula
So when the slicers shows more than one posibiltie in my formula get a message like "Please Choose Only One Name"
I do not want to use PivotTables, VBA
Just a formula
Thanks
| krishnan.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Name | Address | selected | ||||||
| 2 | A | 12 | 1 | ||||||
| 6 | A | 25 | 1 | ||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | Selection: | A | =UNIQUE(FILTER(Table_1[Name];Table_1[selected]=1)) | ||||||
| 11 | |||||||||
| 12 | |||||||||
| 13 | |||||||||
| 14 | |||||||||
| 15 | |||||||||
| 16 | |||||||||
| 17 | |||||||||
| 18 | |||||||||
| 19 | |||||||||
| 20 | |||||||||
| 21 | |||||||||
| 22 | |||||||||
| 23 | |||||||||
| 24 | |||||||||
| 25 | |||||||||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2,E6 | E2 | =AGGREGATE(3,5,[@Name]) |
| C10 | C10 | =UNIQUE(FILTER(Table_1[Name],Table_1[selected]=1)) |
| D10 | D10 | =FORMULATEXT(C10) |
My table contains a slicer To show Name (The Xl2BB, does not show the Slicers, or I do not knot how to show it, anyway does not matter)
Anyway when the slicer shows only 1 value, not multiple options
My job is done brvoudr thr Formual on C10 (green color) shows the Value A
Now when the clear the filters in this slicers and or in my table
I get with the formula on C10:
| krishnan.xlsx | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Name | Address | selected | ||||||
| 2 | A | 12 | 1 | ||||||
| 3 | B | 13 | 1 | ||||||
| 4 | C | 14 | 1 | ||||||
| 5 | D | 15 | 1 | ||||||
| 6 | A | 25 | 1 | ||||||
| 7 | B | 30 | 1 | ||||||
| 8 | |||||||||
| 9 | |||||||||
| 10 | Selection: | A | =UNIQUE(FILTER(Table_1[Name];Table_1[selected]=1)) | ||||||
| 11 | B | ||||||||
| 12 | C | ||||||||
| 13 | D | ||||||||
| 14 | |||||||||
| 15 | |||||||||
| 16 | |||||||||
| 17 | |||||||||
| 18 | |||||||||
| 19 | |||||||||
| 20 | |||||||||
| 21 | |||||||||
| 22 | |||||||||
| 23 | |||||||||
| 24 | |||||||||
Sheet1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E2:E7 | E2 | =AGGREGATE(3,5,[@Name]) |
| C10:C13 | C10 | =UNIQUE(FILTER(Table_1[Name],Table_1[selected]=1)) |
| D10 | D10 | =FORMULATEXT(C10) |
| Dynamic array formulas. | ||
CHECK C11:C14
I would like to avoid C11:XXXX in my C10 Formula
So when the slicers shows more than one posibiltie in my formula get a message like "Please Choose Only One Name"
I do not want to use PivotTables, VBA
Just a formula
Thanks
