Format Cells to Adjust Display Using a Formula

[dusty9]

Is it possible to format a cell to have the number input adjusted via formula? What I have for input is numbers 1-40. For 1-20, I want them to show as input, for numbers 21-40, I want them to display as B1, B2, B3, etc.
This is for two rankings merged together, an A group and a B group. I am inputting as 1-40 for conditional formatting and calculation purposes, but would like the display to distinguish the B group.

My thought is "[>20] "B" #-20 ; # " as the custom format, but that is just showing B21-20 where I want B1.

Thank you.

 

[kevin9999]

I'm not aware of any number format that will achieve your aim, however, if you're willing to entertain a VBA solution then try the following code in the worksheet code module of the sheet being used. Save the file as macro-enabled. I've used B2 as the test cell, change to suit.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("B2"), Target) Is Nothing Then
        Application.EnableEvents = False
        If Target > 20 Then Target = "B" & Target - 20
        Application.EnableEvents = True
    End If
End Sub

Example 1 (15 entered)

Book1
	A	B	C
1
2		15
3
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2	Whole number	between 1 and 40

Example 2 (35 entered)

Book1
	A	B	C
1
2		B15
3
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
B2	Whole number	between 1 and 40

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Couldn't you just use another column?

23 08 28.xlsm
	A	B
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5
6	6	6
7	7	7
8	8	8
9	9	9
10	10	10
11	11	11
12	12	12
13	13	13
14	14	14
15	15	15
16	16	16
17	17	17
18	18	18
19	19	19
20	20	20
21	21	B1
22	22	B2
23	23	B3
24	24	B4
25	25	B5
26	26	B6
27	27	B7
28	28	B8
29	29	B9
30	30	B10
31	31	B11
32	32	B12
33	33	B13
34	34	B14
35	35	B15
36	36	B16
37	37	B17
38	38	B18
39	39	B19
40	40	B20
1-40

Cell Formulas
Range	Formula
B1:B40	B1	=IF(A1<21,A1,"B"&A1-20)

 

[dusty9]

Thanks, I have 15 rounds of results, and only the best 6 count for scoring, so I am using a conditional formatting to highlight the best 6 for display purposes so viewers can see what rounds are counting towards each score. If I use helper columns producing an alphanumeric text output, I'll lose that formatting. Otherwise that would have been my choice.
Not a make or break thing by any means, just thought it'd be nice to have via formatting on an output sheet. I'll work with the Macro that Kevin provided and see if others accessing the file are comfortable with it.

 

[Peter_SSs]

I have 15 rounds of results, and only the best 6 count for scoring, so I am using a conditional formatting to highlight the best 6 for display purposes so viewers can see what rounds are counting towards each score.

Could you give us some smallish sample data with your current conditional formatting with XL2BB so that we can see just what you have, how it is laid out and what you are trying to achieve?

 

[dusty9]

This is what I have, the events are in 2 regions so not a ton of overlap, but I have it bring in the results from a different sheet, add a small decimal in to avoid ties for taking the top 6. Event 14 and 15 have a few ranking worse than 20th that I'd like to show as B+[cell value -20]:

2023 Standings.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2				Event 1	Event 2	Event 3	Event 4	Event 5	Event 6	Event 7	Event 8		Event 9	Event 10	Event 11	Event 12	Event 13	Event 14	Event 15	Event 16	Event 17
3			Date	5/13	6/03	6/17	7/15	7/21	7/23	8/20	9/08		6/09	6/10	7/02	7/07	7/08	8/14	8/16	8/25	8/26
4	Rank	Points	Name	Fin	Fin	Fin	Fin	Fin	Fin	Fin	Fin		Fin	Fin	Fin	Fin	Fin	Fin	Fin	Fin	Fin
5	1	640	Competitor 1	11	1	1	1		1	1
6	2	636	Competitor 2	3					NS				19	1		1	1	1	1		1
7	3	629	Competitor 3	2			2		4				34	4				5	3
8	4	611	Competitor 4	1	3	4	4		2	12								15	9
9	5	610	Competitor 5	4	2	2	14		3	2								21	20
10	6	587	Competitor 6	5	11	3	3		6	3								30	13		2
11	7	576	Competitor 7	6	6	5	6		9	4
12	8	546	Competitor 8	7	4	7	5		11	6
13	9	544	Competitor 9	NS	8	6	7		8	8			20	9		15	11	19	30		10
14	10	516	Competitor 10	8	9	8	11		14	7			13	12		17	16	34	NS		16
Action Sprint Tour

Cell Formulas
Range	Formula
A5:A14	A5	=+RANK(B5,$B$5:$B$104,0)
B5:B14	B5	=IFERROR(INDEX('AST Unsorted'!B:B,MATCH(ROW(A5)-4,'AST Unsorted'!A:A,0)),".")
M5:U14,D5:K14	D5	=IFERROR(INDEX(Results!$A:$A,MATCH($AE5&D$2&D$3,Results!$B:$B,0))+COLUMN(D2)/10000,IFERROR(INDEX(Results!$A:$A,MATCH($AE5&D$2&D$3,Results!$B:$B,0)),""))

Named Ranges
Name	Refers To	Cells
Results!_FilterDatabase	=Results!$A$1:$H$601	D5:K10, M5:M7, N5:U10, D12:K14, N12:U14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D14:U14	Cell Value	top 6 bottom values	text	NO
D13:U13	Cell Value	top 6 bottom values	text	NO
D12:U12	Cell Value	top 6 bottom values	text	NO
D11:U11	Cell Value	top 6 bottom values	text	NO
D10:U10	Cell Value	top 6 bottom values	text	NO
D9:U9	Cell Value	top 6 bottom values	text	NO
D8:U8	Cell Value	top 6 bottom values	text	NO
D7:U7	Cell Value	top 6 bottom values	text	NO
D6:U6	Cell Value	top 6 bottom values	text	NO
D5:U5	Cell Value	top 6 bottom values	text	NO

 

[Peter_SSs]

Thanks for the XL2BB sample data. Could you set up a helper area elsewhere (I have done it below but it could even be on another worksheet) like this & use this new conditional formatting?
Note that this new CF is applied to the new area but uses the values in the original area.

23 08 29.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
2				Event 1	Event 2	Event 3	Event 4	Event 5	Event 6	Event 7	Event 8		Event 9	Event 10	Event 11	Event 12	Event 13	Event 14	Event 15	Event 16	Event 17
3			Date	May-13	6-Mar	Jun-17	Jul-15	Jul-21	Jul-23	Aug-20	9-Aug		6-Sep	6-Oct	7-Feb	7-Jul	7-Aug	Aug-14	Aug-16	Aug-25	Aug-26
4	Rank	Points	Name	Fin	Fin	Fin	Fin	Fin	Fin	Fin	Fin		Fin	Fin	Fin	Fin	Fin	Fin	Fin	Fin	Fin
5	1	640	Competitor 1	11	1	1	1		1	1
6	2	636	Competitor 2	3					NS				19	1		1	1	1	1		1
7	3	629	Competitor 3	2			2		4				34	4				5	3
8	4	611	Competitor 4	1	3	4	4		2	12								15	9
9	5	610	Competitor 5	4	2	2	14		3	2								21	20
10	6	587	Competitor 6	5	11	3	3		6	3								30	13		2
11	7	576	Competitor 7	6	6	5	6		9	4
12	8	546	Competitor 8	7	4	7	5		11	6
13	9	544	Competitor 9	NS	8	6	7		8	8			20	9		15	11	19	30		10
14	10	516	Competitor 10	8	9	8	11		14	7			13	12		17	16	34	NS		16
15
21
22				Event 1	Event 2	Event 3	Event 4	Event 5	Event 6	Event 7	Event 8		Event 9	Event 10	Event 11	Event 12	Event 13	Event 14	Event 15	Event 16	Event 17
23				May-13	Mar-23	Jun-17	Jul-15	Jul-21	Jul-23	Aug-20	Aug-23		Sep-23	Oct-23	Feb-23	Jul-23	Aug-23	Aug-14	Aug-16	Aug-25	Aug-26
24	Rank	Points	Name	Fin	Fin	Fin	Fin	Fin	Fin	Fin	Fin		Fin	Fin	Fin	Fin	Fin	Fin	Fin	Fin	Fin
25	1	640	Competitor 1	11	1	1	1		1	1
26	2	636	Competitor 2	3					NS				19	1		1	1	1	1		1
27	3	629	Competitor 3	2			2		4				B14	4				5	3
28	4	611	Competitor 4	1	3	4	4		2	12								15	9
29	5	610	Competitor 5	4	2	2	14		3	2								B1	20
30	6	587	Competitor 6	5	11	3	3		6	3								B10	13		2
31	7	576	Competitor 7	6	6	5	6		9	4
32	8	546	Competitor 8	7	4	7	5		11	6
33	9	544	Competitor 9	NS	8	6	7		8	8			20	9		15	11	19	B10		10
34	10	516	Competitor 10	8	9	8	11		14	7			13	12		17	16	B14	NS		16
dusty9

Cell Formulas
Range	Formula
D22:K23,A24:K24,A25:C34,M22:U24	D22	=D2
M25:U34,D25:K34	M25	=IF(M5="","",IF(ISNUMBER(M5),IF(M5<21,M5,"B"&M5-20),M5))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
M25:U34,D25:K34	Expression	=AND(ISNUMBER(D5),D5<=SMALL($D5:$U5,6))	text	NO

 

[dusty9]

Hmm, the offset formatting looks like it is the way to go. I'm not very good with the formula conditional formats so I probably wouldn't have gotten there on my own. Thank you for this solution!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.