Increase the number of custom formats

[GuusKoppes]

Hi,

I am working on an excel tool for the calculation of ship weights using weight ratiocination.
I am trying to show text in a pivot table using the custom number format by applying custom formats like below to a cell in my pivot table.
[=100]"Hull structure, general"
The creation and application of these formats works fine using a macro but in my worksheet I am limited to adding 206 custom formats while i would like to add about 700.
If I try to add an additional format I get the error message "No more custom number formats can be added." I tried it on two different laptops one using Office 365 ProPlus and one using Office Professional Plus 2016

Is there a way to increase the number of custom formats that you can create?

PS. I know that it is possible to add the data to a data model and create a measure with the DAX formula concatenatex to show text in pivot table. But, it is sort of an interactive sheet, as a result it needs to be refreshed frequently and using the data model turned out to increase the refresh time very much.

 

[GraH]

As it is represented in the pivot picture, I'm wondering why you do not simply "lookup" the value and add description?
I thought when reading your thread the first time around, those "numbers" were sitting in the value area, now I'm not so sure any more.

Question is rather why this is needed like jkpieterse already suggested.

 

[GuusKoppes]

As it is represented in the pivot picture, I'm wondering why you do not simply "lookup" the value and add description?
I thought when reading your thread the first time around, those "numbers" were sitting in the value area, now I'm not so sure any more.

Question is rather why this is needed like jkpieterse already suggested.

There is more data in the pivot table, primarely weight data of different ships in different columns. This is a snapshot of the first two columns to show What i am trying to achieve

 

[mole999]

@GuusKoppes you asked about memory limits Excel 32 bits is 3GB,, Excel 64 bit is far greater, you also need to be on a 64 bit operating system

64-bit environment imposes no hard limits on file size. Workbook size is limited only by available memory and system resources.


Beginning with Excel 2016, Large Address Aware functionality lets 32-bit Excel consume twice the memory when users work on a 64-bit Windows operating system. For more information, see Large Address Aware capability change for Excel.

 

[GraH]

There is more data in the pivot table, primarely weight data of different ships in different columns. This is a snapshot of the first two columns to show What i am trying to achieve

Hi, I got that, but question remains why you need those custom formats to start with. Sounds like a label through a lookup could do, and if the lookup table is sorted (which it seems to be) a double binary lookup will be lightning fast and perhaps save you from the headache you're developing.

Putting it differently (because I can't see your file obviously), do you have all these "columns" generated with values only, or are the fields sitting in the row section as well? That label you need, can it be a field in the row section or not?

 

[GuusKoppes]

Hi, I got that, but question remains why you need those custom formats to start with. Sounds like a label through a lookup could do, and if the lookup table is sorted (which it seems to be) a double binary lookup will be lightning fast and perhaps save you from the headache you're developing.

Putting it differently (because I can't see your file obviously), do you have all these "columns" generated with values only, or are the fields sitting in the row section as well? That label you need, can it be a field in the row section or not?

I think a lookup is not possible because I can't change the values in the pivottable. One solution I found that is partly working for me is using the number formats, by using these I do not change the values in the pivottable but only their appearance.
The first column is generated using rows, the other columns are all generated using values. I don't think it can be a row, or at least not in the way I am building the pivot table now.
I created an example with random numbers of a sheet in my workbook, in total i have 7 of these for the different main SWBS(ship work breakdown structures) groups but the sheets are identical in format and formulas etc.

I hope this example will make more clear what I am trying to achieve. The goal is to estimate the weight of a new vessel based on main parameters such as length etc. Each SWBS number, 111, 112 etc has a ratio that will be multiplied with the weight data of an existing ship. For each SWBS number the user can choose a reference ship that compares best to the new ship. This way a weight estimation can be created using existing weight data and different reference ships. In the reference column the user can choose a reference vessels for that SWBS number and add some delta if it is known that the weight data under or overestimates that part.

So the top part of the sheet with the table allows the user to make choices, the bottom part is the pivot table that is used to create an overview and a pivot chart will also be added to give more insights in the weight data etc. The data in the top table is used as input for the pivottable below.

Finally the tool is "dynamic" I made different macros so that different reference ships can be chosen and in the future new weight data and ships can be easily added.

A pretty long story but i hope this will give some more insight in the problem, please ask questions if something is unclear! And again thanks everyone for thinking with me in this.

Test_sheet_weights_estimation_screenshot_version.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA
2										Parent ship weights					Ratios					New ship weights
3	SWBS1	SWBS2	SWBS3	SWBS	Title	Ratio	Ref	Delta	New ship	ship1	ship2	ship3	ship4	ship5	ship1	ship2	ship3	ship4	ship5	ship1	ship2	ship3	ship4	ship5	1DigitSWBS	2DigitSWBS	3DigitSWBS
4	100			100	HULL STRUCTURE, GENERAL		ship1		8540.00	1708	451	4222	2119	4565	5.00	3.00	1.00	2.00	5.00	8540.00	1353.00	4222.00	4238.00	22825.00	100	100	100
5		110		110	SHELL AND SUPPORTING STRUCTURE		ship1	50.00	16815.00	3353	2176	4377	2395	1992	5.00	3.00	0.00	4.00	3.00	16765.00	6528.00	0.00	9580.00	5976.00	100	110	110
6			111	111	SHELL PLATING, SURFACE SHIP AND SUBMARINE PRESSURE HULL	(B+2*D)*L	ship2		0.00	4328	770	4578	1958	1036	3.00	0.00	1.00	4.00	5.00	12984.00	0.00	4578.00	7832.00	5180.00	100	110	111
7			114	114	SHELL APPENDAGES	L*B*D/100	ship2	350.00	1086.00	2768	736	3101	4169	2097	0.00	1.00	2.00	4.00	1.00	0.00	736.00	6202.00	16676.00	2097.00	100	110	114
8		120		120	HULL STRUCTURAL BULKHEADS		ship1		11740.00	2348	3890	3852	4720	1553	5.00	1.00	2.00	3.00	1.00	11740.00	3890.00	7704.00	14160.00	1553.00	100	120	120
9			121	121	LONGITUDINAL STRUCTURAL BULKHEADS	L*D^2	ship1	25.00	4949.00	4924	3600	4424	3170	2970	1.00	5.00	3.00	3.00	2.00	4924.00	18000.00	13272.00	9510.00	5940.00	100	120	121
10		150		150	DECK HOUSE STRUCTURE		ship1		6560.00	1640	3006	1322	2885	1840	4.00	4.00	1.00	4.00	0.00	6560.00	12024.00	1322.00	11540.00	0.00	100	150	150
11		160		160	SPECIAL STRUCTURES		ship1	75.00	75.00	4123	3450	1746	1168	3836	0.00	0.00	2.00	4.00	4.00	0.00	0.00	3492.00	4672.00	15344.00	100	160	160
12			161	161	STRUCTURAL CASTINGS, FORGINGS, AND EQUIV. WELDMENTS	L*B*D/100	ship1		0.00	2345	4183	3847	3485	4703	0.00	1.00	4.00	1.00	5.00	0.00	4183.00	15388.00	3485.00	23515.00	100	160	161
13			163	163	SEA CHESTS	(0,5+0,5*SHP)	ship1	120.00	7832.00	3856	1104	2916	4261	842	2.00	4.00	5.00	1.00	4.00	7712.00	4416.00	14580.00	4261.00	3368.00	100	160	163
14			167	167	HULL STRUCTURAL CLOSURES	L*D	ship1		0.00	2131	61	1018	2329	1043	0.00	2.00	1.00	5.00	4.00	0.00	122.00	1018.00	11645.00	4172.00	100	160	167
15			168	168	DECKHOUSE STRUCTURAL CLOSURES	L*D	ship1		24190.00	4838	3959	82	1221	1052	5.00	5.00	2.00	1.00	2.00	24190.00	19795.00	164.00	1221.00	2104.00	100	160	168
16			169	169	SPECIAL PURPOSE CLOSURES AND STRUCTURES	L*B*D	ship3		7743.00	3944	260	2581	1155	4354	5.00	0.00	3.00	5.00	0.00	19720.00	0.00	7743.00	5775.00	0.00	100	160	169
17		170		170	MASTS, KINGPOSTS, AND SERVICE PLATFORMS		ship1	30.00	9357.00	3109	418	1397	2779	2215	3.00	1.00	3.00	1.00	1.00	9327.00	418.00	4191.00	2779.00	2215.00	100	170	170
18			171	171	MASTS, TOWERS, TETRAPODS	L*B*D	ship1		5595.00	1119	356	3659	729	3828	5.00	5.00	1.00	1.00	1.00	5595.00	1780.00	3659.00	729.00	3828.00	100	170	171
19		180		180	FOUNDATIONS		ship5		10332.00	898	1266	2903	3941	3444	1.00	1.00	1.00	3.00	3.00	898.00	1266.00	2903.00	11823.00	10332.00	100	180	180
20			181	181	HULL STRUCTURE FOUNDATIONS	Group_1_new/Group_1_parent	ship1		424.00	424	426	4950	3281	4928	1.00	4.00	4.00	2.00	2.00	424.00	1704.00	19800.00	6562.00	9856.00	100	180	181
21			182	182	PROPULSION PLANT FOUNDATIONS	Group_2_new/Group_2_parent	ship1		11262.00	3754	1551	4697	2924	2605	3.00	5.00	4.00	2.00	5.00	11262.00	7755.00	18788.00	5848.00	13025.00	100	180	182
22			183	183	ELECTRIC PLANT FOUNDATIONS	Group_3_new/Group_3_parent	ship1	45.00	1619.00	787	2770	1810	591	4207	2.00	1.00	2.00	5.00	0.00	1574.00	2770.00	3620.00	2955.00	0.00	100	180	183
23			184	184	COMMAND AND SURVEILLANCE FOUNDATIONS	Group_4_new/Group_4_parent	ship1		9490.00	4745	1604	4091	4031	3528	2.00	0.00	5.00	5.00	2.00	9490.00	0.00	20455.00	20155.00	7056.00	100	180	184
24			185	185	AUXILIARY SYSTEMS FOUNDATIONS	Group_5_new/Group_5_parent	ship4		6978.00	212	1649	2911	2326	730	4.00	0.00	3.00	3.00	3.00	848.00	0.00	8733.00	6978.00	2190.00	100	180	185
25			186	186	OUTFIT AND FURNISHINGS FOUNDATIONS	Group_6_new/Group_6_parent	ship1		0.00	819	4890	2134	4624	1776	0.00	1.00	2.00	4.00	1.00	0.00	4890.00	4268.00	18496.00	1776.00	100	180	186
26			187	187	ARMAMENT FOUNDATIONS	Group_7_new/Group_7_parent	ship1		1874.00	937	4383	3488	2371	2729	2.00	5.00	1.00	5.00	3.00	1874.00	21915.00	3488.00	11855.00	8187.00	100	180	187
27		190		190	SPECIAL PURPOSE SYSTEMS		ship4		15355.00	4201	4635	3591	3071	2196	0.00	1.00	4.00	5.00	3.00	0.00	4635.00	14364.00	15355.00	6588.00	100	190	190
28			196	196	WELD TOLERANCE		ship1		3442.00	3442	3456	4578	2099	4296	1.00	5.00	4.00	5.00	3.00	3442.00	17280.00	18312.00	10495.00	12888.00	100	190	196
29			197	197	MATERIAL TOLERANCE	2,5%_Group_1	ship1		6114.00	2038	3543	1335	2255	4005	3.00	4.00	1.00	3.00	0.00	6114.00	14172.00	1335.00	6765.00	0.00	100	190	197
30
31				Rijlabels	SWBS Description	Refs	Ratios	Deltas	Ship weights of New ship	Som van ship1	Som van ship2	Som van ship3	Som van ship4	Som van ship5
32				100	HULL STRUCTURE, GENERAL	1	1		8540	1708	451	4222	2119	4565
33				100	HULL STRUCTURE, GENERAL	1	1		8540	1708	451	4222	2119	4565
34				110	110	3	3	2	17901	10449	3682	12056	8522	5125
35				110	110	1	1	1	16815	3353	2176	4377	2395	1992
36				111	SHELL PLATING, SURFACE SHIP AND SUBMARINE PRESSURE HULL	1	1		0	4328	770	4578	1958	1036
37				114	SHELL APPENDAGES	1	1	1	1086	2768	736	3101	4169	2097
38				120	120	2	2	1	16689	7272	7490	8276	7890	4523
39				120	120	1	1		11740	2348	3890	3852	4720	1553
40				121	LONGITUDINAL STRUCTURAL BULKHEADS	1	1	1	4949	4924	3600	4424	3170	2970
41				150	DECK HOUSE STRUCTURE	1	1		6560	1640	3006	1322	2885	1840
42				150	DECK HOUSE STRUCTURE	1	1		6560	1640	3006	1322	2885	1840
43				160	SPECIAL STRUCTURES	6	6	2	39840	21237	13017	12190	13619	15830
44				160	SPECIAL STRUCTURES	1	1	1	75	4123	3450	1746	1168	3836
45				161	STRUCTURAL CASTINGS, FORGINGS, AND EQUIV. WELDMENTS	1	1		0	2345	4183	3847	3485	4703
46				163	SEA CHESTS	1	1	1	7832	3856	1104	2916	4261	842
47				167	HULL STRUCTURAL CLOSURES	1	1		0	2131	61	1018	2329	1043
48				168	DECKHOUSE STRUCTURAL CLOSURES	1	1		24190	4838	3959	82	1221	1052
49				169	SPECIAL PURPOSE CLOSURES AND STRUCTURES	1	1		7743	3944	260	2581	1155	4354
50				170	MASTS, KINGPOSTS, AND SERVICE PLATFORMS	2	2	1	14952	4228	774	5056	3508	6043
51				170	MASTS, KINGPOSTS, AND SERVICE PLATFORMS	1	1	1	9357	3109	418	1397	2779	2215
52				171	MASTS, TOWERS, TETRAPODS	1	1		5595	1119	356	3659	729	3828
53				180	FOUNDATIONS	8	8	1	41979	12576	18539	26984	24089	23947
54				180	FOUNDATIONS	1	1		10332	898	1266	2903	3941	3444
55				181	HULL STRUCTURE FOUNDATIONS	1	1		424	424	426	4950	3281	4928
56				182	PROPULSION PLANT FOUNDATIONS	1	1		11262	3754	1551	4697	2924	2605
57				183	ELECTRIC PLANT FOUNDATIONS	1	1	1	1619	787	2770	1810	591	4207
58				184	COMMAND AND SURVEILLANCE FOUNDATIONS	1	1		9490	4745	1604	4091	4031	3528
59				185	AUXILIARY SYSTEMS FOUNDATIONS	1	1		6978	212	1649	2911	2326	730
60				186	OUTFIT AND FURNISHINGS FOUNDATIONS	1	1		0	819	4890	2134	4624	1776
61				187	ARMAMENT FOUNDATIONS	1	1		1874	937	4383	3488	2371	2729
62				190	SPECIAL PURPOSE SYSTEMS	3	3		24911	9681	11634	9504	7425	10497
63				190	SPECIAL PURPOSE SYSTEMS	1	1		15355	4201	4635	3591	3071	2196
64				196	WELD TOLERANCE	1	1		3442	3442	3456	4578	2099	4296
65				197	MATERIAL TOLERANCE	1	1		6114	2038	3543	1335	2255	4005
66				Eindtotaal	100	26	26	7	171372	68791	58593	79610	70057	72370
SWBS100

Cell Formulas
Range	Formula
A4:A29	A4	=IFERROR(IF(VALUE(RIGHT(D4,2))<>0," ",D4)," ")
B4:B29	B4	=IFERROR(IF(VALUE(RIGHT(D4,2))<>0,IF(VALUE(RIGHT(D4,1))<>0,"",D4),""),"")
C4:C29	C4	=IFERROR(IF(VALUE(RIGHT(D4,1))<>0,D4,""),"")
E4:E29	E4	=INDEX(New_ship_weights!$F:$F,MATCH(D4,New_ship_weights!$A:$A,0),1)
F4:F29	F4	=INDEX(New_ship_weights!$G:$G,MATCH(D4,New_ship_weights!$A:$A,0),1)
I4:I29	I4	=IFERROR(INDEX($T4:$X4,1,MATCH($G4,$T$3:$X$3,0)),0)+$H4
J4:N29	J4	=RANDBETWEEN(50,5000)
O4:S29	O4	=RANDBETWEEN(0,5)
T4:X29	T4	=O4*J4
Y4:Y29	Y4	=LEFT($D4,1)&"00"
Z4:Z29	Z4	=LEFT($D4,2)&"0"
AA4:AA29	AA4	=$D4

Named Ranges
Name	Refers To	Cells
New_ship_weights!_FilterDatabase	=New_ship_weights!$A$3:$EX$660	E4:F29

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O4:S29	Other Type	DataBar		NO

Cells with Data Validation
Cell	Allow	Criteria
G3	List	=Start_sheet!$F$3:$F$5

 

[GraH]

"Rijlables", then we can continue in Dutch, GuusKoppes .

What strikes me is that you have SWBS and Title in your data, and Title seems to be the label you want in your pivot. Or am I really that wrong? So why don't you add that Title in your row section. If you make the pivot lay-out tabular, both "row labels" show up nicely in 2 columns. And btw, Title is a lookup through an INDEX+MATCH combo.
Also for having those groups and groups of groups (the bold lines), that is achievable with grouping in Pivot as well (a bit tedious because of your 700+labels, but a one-time set-up and a macro genious can probably make such a code in a blink of eye based on your definition table).

 

[GuusKoppes]

"Rijlables", then we can continue in Dutch, GuusKoppes .

What strikes me is that you have SWBS and Title in your data, and Title seems to be the label you want in your pivot. Or am I really that wrong? So why don't you add that Title in your row section. If you make the pivot lay-out tabular, both "row labels" show up nicely in 2 columns. And btw, Title is a lookup through an INDEX+MATCH combo.
Also for having those groups and groups of groups (the bold lines), that is achievable with grouping in Pivot as well (a bit tedious because of your 700+labels, but a one-time set-up and a macro genious can probably make such a code in a blink of eye based on your definition table).

Haha, normally still work in the english excel but I am dutch indeed

Wow, feeling a bit stupid now, never realised text would just show up in rows. But glad that you pointed that out! Works perfectly for me.
Added a screenshot of how it looks now. The grouping is working fine as it is now i think but good to keep that in mind!

Still wondering why i couldn't add more than 206 custom formats though but will leave that for myself to figure if I have some spare time.
Thanks everyone for your fast replies and helping me to reach a solution!