Creating a 'reference sheet' of postage prices from different suppliers with different weight bands and sizing options.

[Rhothgar]

Hi all,

I have started out today with good intentions and only got so far.

I am trying to create a worksheet which I will then copy into another spreadsheet.

The worksheet I am trying to create contains the name of the service, the maximum size of letter, large letter or parcel allowed by each supplier within a range.

I do not know if I am even using the correct technical terms when it comes to Excel ranges, references, etc.

So in the other spreadsheet, it is hoped that I will be able to simply select maybe 1, 2 or 3 dropdowns to select weight, size and supplier in that order and come up with an answer as to what is the cheapest method of posting.
Business is so, so tough these days and margins have been squeezed possibly beyond what is actually sustainable to be honest.

I've installed the fab XL2BB tool and this is my first post using it.

So I'll give an example to try and narrow the issues down:-

Generally, if an item is up to 2kg, it is no longer viable to post with Royal Mail. However, if its size is less than 2.5 cm depth and generally less than 100g, it is better to send with Royal Mail as it saves driving to the Hermes shop.

Also, if the item being posted requires insurance, Hermes (now Evri) only include £20 cover whereas Royal Mail provide £50 cover.

If the value is higher than £60 then sometimes we would send with Special Delivery which includes insurance up to £500 as standard.

There isn't any rhyme or reason to how we choose to send other than what we feel will provide the best service to the customer but also keeps the very limited margins protected.

At the moment, we have one particular item which sells for £18.99 and the profit is only £0.94 (and that excludes packaging and labelling costs). Pretty desperate times to be fair. It was a bad judgement call on my behalf to
buy this stock in. At present, we are paying Ebay 4% to promote the items which effectively wipes out the profit completely. Talk about busy fools!

Any help would be very much gratefully appreciated.

For instance, in the below sheet, I am imagining that A13:H25 is a reference block, B28:H40 is another reference block, A43:H53 another and finally A56:H58 is the final reference.

B5:B9 is the range of A13:53 reference charts
C5:C9 will be the range of A56:H58 and some additional blocks beyond.

Am I at least going in the right direction with this?

Postage Reference Sheet.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1
2
3
4												Large Letter
5		Royal Mail	Hermes
6		Letter
7		Large Letter
8		Small Parcel
9		Medium Parcel
10
11
12
13		Signed For 1st Class
14	Length	24		35.3		45		61
15	Width	16.5		45		35		46
16	Depth	0.5		2.5		16		46
17		Letter		Large Letter		Small Parcel		Medium Parcel
18	100g	2.35		2.85
19	101-250g			3.45
20	251-500g			4.05
21	501-750g			4.7
22	0-2000g					5.55
23	0-2000g							8.05
24	2000-10000g							9.05
25	10000-20000g							14.05
26
27
28		Signed For 2nd Class
29	Length	24		35.3		45		61
30	Width	16.5		45		35		46
31	Depth	0.5		2.5		16		46
32		Letter		Large Letter		Small Parcel		Medium Parcel
33	100g	2.08		2.45
34	101-250g			3.05
35	251-500g			3.55
36	501-750g			4.1
37	0-2000g					4.45
38	0-2000g							6.45
39	2000-10000g							8.05
40	10000-20000g							11.55
41
42
43		Special Delivery Guaranteed by 1pm
44	Length	61		61		61		61
45	Width	46		46		46		46
46	Depth	46		46		46		46
47		Letter		Large Letter		Small Parcel		Medium Parcel
48	1-100g	6.85		6.85		6.85		6.85
49	101-500g	7.65		7.65		7.65		7.65
50	501-1000g	8.95		8.95		8.95		8.95
51	1001-2000g	11.15		11.15		11.15		11.15
52	2001-10000g	15.45		15.45		15.45		15.45
53	10001-20000g	19.45		19.45		19.45		19.45
54
55
56		Hermes Drop-off
57	Length	24		35.3		45		61
58	Width	16.5		45		35		46
59	Depth	0.5		2.5		16		46
60		Letter		Large Letter		Small Parcel		Medium Parcel
61	100g					2.47		2.47
62	101-250g					2.47		2.47
63	251-500g					2.47		2.47
64	501-750g					2.47		2.47
65	751-1000g					2.47		2.47
66	1001-2000g					3.61		3.61
67	2001-5000g					4.86		4.86
68	5001-10000g					4.94		4.94
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
L4	List	=$B$6:$B$9