etaf
Well-known Member
- Joined
- Oct 24, 2012
- Messages
- 8,693
- Office Version
- 365
- Platform
- MacOS
i have been playing with the FILTER function and help here earlier today
but now i have a new question
i'm using this formula - thanks to @Fluff
=SORT(CHOOSECOLS(FILTER(Active!$A$1:$Q$500,(Active!$P$1:$P$500<=F1/100)*(Active!$P$1:$P$500<>"")),XMATCH(E2:K2,Active!A1:Q1,0)),(1))
which is working great
BUT now
rather than show all of the contents of column A
I would like to use some thing like a textbefore ( a2, char(10) )
i have tried this in a helper column and it works - really great with the column header options now , as i can just call the new column Client Name and change in summary - and extend the ranges and it works - PERFECT
i used
IFERROR ( TEXTBEFORE ( A2, char(10) ) ) and copied down - and hid column - so that works - BUT i would like to keep in filter
Can i only show part of 1 column in the filter
in the data sheet i have in cell A2
Flinstone, Fred
123456789
abced ef g
but i only want to pull into the summary sheet , using FILTER - the first line from A2
Flinstone, Fred
using choosecols() instead of using an array of {1,0,0,1} type within a filter
i have just seen here https://www.mrexcel.com/board/threads/allowing-an-array-1-or-zero-to-read-from-a-cell-address.1236174/#post-6055201 a way to instead of using an array of {1,0,1,0,0,0,0,1,1,1} in a filter to show columns based on a FILTER that a CHOOSECOLS() is used , and just provide the...
www.mrexcel.com
but now i have a new question
i'm using this formula - thanks to @Fluff
=SORT(CHOOSECOLS(FILTER(Active!$A$1:$Q$500,(Active!$P$1:$P$500<=F1/100)*(Active!$P$1:$P$500<>"")),XMATCH(E2:K2,Active!A1:Q1,0)),(1))
which is working great
BUT now
rather than show all of the contents of column A
I would like to use some thing like a textbefore ( a2, char(10) )
i have tried this in a helper column and it works - really great with the column header options now , as i can just call the new column Client Name and change in summary - and extend the ranges and it works - PERFECT
i used
IFERROR ( TEXTBEFORE ( A2, char(10) ) ) and copied down - and hid column - so that works - BUT i would like to keep in filter
Can i only show part of 1 column in the filter
in the data sheet i have in cell A2
Flinstone, Fred
123456789
abced ef g
but i only want to pull into the summary sheet , using FILTER - the first line from A2
Flinstone, Fred
Wayne FORUM Help.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
E | F | G | H | I | J | K | |||
1 | Clients under | 20% | <- enter the Number - NOT a Percent - EG for 35%, just type in 35 - Trust Balance to Minimum Retainer % required to extract the summary below | ||||||
2 | headerA | HeaderK | headerL | Header M | Header N | Header O | Header P | ||
3 | Flinstone, Fred 123456789 abced ef g | $ 2,000.00 | $ - | $ 20.00 | $ 200.00 | $ 2,220.00 | -11% | ||
4 | name-12 | $ 10,000.00 | $ - | $ - | $ 12.00 | $ 10,012.00 | 0% | ||
5 | name-13 | $ 10,000.00 | $ 1,000.00 | $ 130.50 | $ - | $ 9,130.50 | 9% | ||
6 | name-15 | $ 10,000.00 | $ 6,000.00 | $ 7,000.00 | $ - | $ 11,000.00 | -10% | ||
7 | name-3 | $ 10,000.00 | $ 9,000.00 | $ 9,000.00 | $ - | $ 10,000.00 | 0% | ||
8 | name-7 | $ 10,000.00 | $ - | $ - | $ 12.00 | $ 10,012.00 | 0% | ||
Summary |
Cell Formulas | ||
---|---|---|
Range | Formula | |
E3:K8 | E3 | =SORT(CHOOSECOLS(FILTER(Active!$A$1:$Q$500,(Active!$P$1:$P$500<=F1/100)*(Active!$P$1:$P$500<>"")),XMATCH(E2:K2,Active!A1:Q1,0)),(1)) |
Dynamic array formulas. |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
Active!_FilterDatabase | =Active!$A$1:$Q$6 | E3 |
Active!Print_Titles | =Active!$1:$1 | E3 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
E3:K201 | Expression | =$E3<>"" | text | NO |
CM test Sheet - bug correction - VERSION 2.1.xlsx | |||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | |||
1 | HeaderA | Header1 | Header2 | Header3 | Header4 | Header5 | Header6 | Header7 | Header8 | Header9 | HeaderK | headerL | Header M | Header N | Header O | Header P | Header16 | ||
2 | Flinstone, Fred 123456789 abced ef g | $2,000.00 | $0.00 | $20.00 | $200.00 | $2,220.00 | -11.000% | 1/3/23 | |||||||||||
3 | name-1 | $10,000.00 | $9,000.00 | $10.00 | $0.00 | $1,010.00 | 89.900% | 2/21/23 | |||||||||||
4 | name-10 | $10,000.00 | $10,000.00 | $0.00 | $0.00 | $0.00 | 100.000% | 1/31/23 | |||||||||||
5 | name-11 | $10,000.00 | $2,300.00 | $0.00 | $0.00 | $7,700.00 | 23.000% | 2/21/23 | |||||||||||
Active |
Cell Formulas | ||
---|---|---|
Range | Formula | |
O2:O5 | O2 | =IF(A2="","",SUM(K2)-(L2-M2)+(N2)) |
P2:P5 | P2 | =IFERROR(IF(K2="Closed","Closed",((L2-M2-N2)/K2)),"") |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
L2:O19,L21:O500 | Expression | =$K2="closed" | text | NO |
A2:Q19,A21:Q500,A20:J20,Q20 | Expression | =$P2="closed" | text | YES |
A2:Q19,A20:J20,Q20,A21:Q500 | Expression | =$N2>0 | text | YES |
A2:Q19,A20:J20,Q20,A21:Q500 | Expression | =AND($L2<>"",($L2-$M2)<=0) | text | NO |
A2:Q19,A20:J20,Q20,A21:Q500 | Expression | =AND($P2<>"",$P2<=0.25) | text | NO |
A2:Q19,A20:J20,Q20,A21:Q500 | Expression | =AND($P2<>"",$P2<=0.5) | text | NO |
A2:Q19,A20:J20,Q20,A21:Q500 | Expression | =AND($P2<>"",$P2<=0.75) | text | NO |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
C2:C5 | List | =Reference!$A$2:$A$13 |