Many many Q's - conditional formatting plus IF, VLOOKUP queries

[lemmo80]

Hi there,

Thank you in advance to anyone that can help, I have a lot of big ideas for my spreadsheet but limited knowledge of application. Firstly, some background, the data input into the sheet is a straight cut & transpose from a word table. Columns A:L are the cut & transposed data, the remaining columns are the working tools. I cannot add or remove any of columns A:L and any formulas written into them will be deleted when I transpose new data into the sheet, but I think conditional formatting might work? And yes, I feel like I'm over-asking, so please tell me if that's the case and I'll keep fumbling away with googling solutions.

So... to the list of queries: by column;

Column A, this is meant to be a mobile number, however the "0" drops off when transposed. Is there a conditional formatting formula that turns it into 0XXX XXX XXX?
Columns D:G are a duplication of H:K, this spilt occurs due to the response given in column C. I would like data from D:G to be replicated into H:K, I can then hide D:G so the data is more consolidated visually. Is this possible? I've tried doing basic "= & if blank, return blank" formulas, but again it gets deleted when I transpose new data.
Column M, this column is not effected by the transpose so a normal formula will stay. This column counts the days in transit. The formula works prefect when the transit is both ways, however I would like it to return "0" when its one-way. Currently the formula is written to work on H:K as I'm assuming the D:G replication is possible.
Column N, this works fine.
Column O, well.... this is a dozy and gold medal challenge...! the column calculates the charge rate depending on the length of the vehicle given in column B. The charge structure is E18:F26. Travel is charged per run, but if you travel both ways on the same day and on the same run you only get charged one-way. For example the booking on Row 2, will only get charged for one run ($465) as they travelled both ways on same day (28/7) and same run (blue, run 2). Whereas the booking on Row 3, needs to be charged twice ($255x2=$510), they travelled both ways on same day (3/8) but different runs (run 1, then run 2). All others both way bookings travelled different days so need to be charged twice. I've written a nested "IF(AND" that works great for one run charging BUT, now need it to "lookup" H:K (assuming D:G are replicated there) and say something like "IF H&J are MATCH (AND(IF I&K are MATCH, *1" .... "IF H&J are no MATCH, *2. Please note I&K will not match exactly as the loading time for each run is different. If this formula needs to be broken down over a few columns they can be added from R onwards or make more helper boxes below. so so sorry, I wish I could solve this on my own but I'm so out of my depth.
Column P, works fine.

That's it! What do you think... challenge accepted??

TEST VERSION - Jan - Jun 2021 - Truck Bookings Track ^0 Trace.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Contact Number	Length*	Are you requesting to travel*	Depart Sydney DATE OF TRAVEL	Sydney Truck Run	Depart Hazelbrook DATE OF TRAVEL	Hazelbrook Truck Run	Depart Sydney DATE OF TRAVEL	Sydney Truck Run	Depart Hazelbrook DATE OF TRAVEL	Hazelbrook Truck Run	Travel Category	**NEW ** Day Counter	**NEW MANUAL** TC Charge	**NEW MANUAL** Truck Charge	**NEW MANUAL** Total Amount $*
2	442691010	6.5	both ways (return)					28/07/2021	Blue Truck - Run 2, loading 10.30am	28/07/2021	Blue Truck - Run 2, loading 1.00pm	Other Type	1	1	465	466
3	430352594	6	both ways (return)					3/08/2021	Blue Truck - Run 1, loading 6.30am	3/08/2021	Blue Truck - Run 2, loading 1.00pm	Other Type	1	1	255	256
4	458231430	5.3	both ways (return)					5/08/2021	Blue Truck - Run 1, loading 6.30am	6/08/2021	Blue Truck - Run 2, loading 1.00pm	Regular (GVM < 4.5 Tonne)	2	10	255	265
5	445893014	5.6	both ways (return)					28/07/2021	Blue Truck - Run 1, loading 6.30am	30/07/2021	Blue Truck - Run 1, loading 9.00am	Other Type	3	3	255	258
6	473771850	9.3	both ways (return)					28/07/2021	Blue Truck - Run 1, loading 6.30am	30/07/2021	Blue Truck - Run 1, loading 9.00am	Heavy (GVM > 4.5 Tonne)	3	36	465	501
7	461433434	9	both ways (return)					4/08/2021	Blue Truck - Run 2, loading 10.30am	6/08/2021	Blue Truck - Run 1, loading 9.00am	Regular (GVM < 4.5 Tonne)	3	15	465	480
8	489312270	9.9	both ways (return)					3/08/2021	Blue Truck - Run 2, loading 10.30am	6/08/2021	Blue Truck - Run 2, loading 1.00pm	Heavy (GVM > 4.5 Tonne)	4	48	780	828
9	428877579	7.5	both ways (return)					16/08/2021	Blue Truck - Run 1, loading 6.30am	19/08/2021	Blue Truck - Run 2, loading 1.00pm	Regular (GVM < 4.5 Tonne)	4	20	465	485
10	456756415	11.2	to Hazelbrook	26/07/2021	Red Truck - Run 1, Loading 8:00am								1	#N/A	780	780
11	444417999	9	to Hazelbrook	26/07/2021	Red Truck - Run 1, Loading 8:00am								1	#N/A	465	465
12	472296835	8	to Hazelbrook	23/07/2021	Blue Truck - Run 1, loading 6.30am								1	#N/A	465	465
13	459958419	9	to Sydney			28/07/2021	Blue Truck - Run 1, loading 9.00am						1	#N/A	465	465
14	487837255	5.15	to Sydney			30/07/2021	Blue Truck - Run 2, loading 1.00pm						1	#N/A	255	255
15
16
17					HELPER CELLS BELOW				HELPER CELLS BELOW
18					Meterage	Price			CHARGES MATRIX	DAILY RATE
19					Pallet	110			Regular (GVM < 4.5 Tonne)	5
20					1.5 to 3.0m	150			Heavy (GVM > 4.5 Tonne)	12
21					3.01 to 6.0m	255			Other Type	1
22					6.01 to 9.5m	465
23					9.51 to 12.5m	780
24					12.51 to 18m	840
25					18.1 to 23m	890
26					> 23m	1005
Sheet1

Cell Formulas
Range	Formula
M2:M14	M2	=SUM(J2-H2)+1
N2:N14	N2	=VLOOKUP(L2,$I$18:$J$21,2,FALSE)*M2
O2:O14	O2	=IF(AND(B2<1.5),110,IF(AND(B2>1.49,B2<3.01),150,IF(AND(B2>3,B2<6.01),255,IF(AND(B2>6,B2<9.51),465,IF(AND(B2>9.5,B2<12.51),780,IF(AND(B2>12.5,B2<18.01),840,IF(AND(B2>23),1005,0)))))))
P2:P14	P2	=SUMIF(N2:O2,"<>#N/A")

 

[Alex Blakenburg]

The best approach depends on your skill level.
Ideally you want address how you are getting the data.
You say you are getting it from word but how is it getting into word, can you cut out that step and get a cleaner data source ?
Do you want to use VBA or Power Query ?

The simplest solution is to do it all using formulas.
You are already talking about hiding Columns D:G how about hiding all your current columns:
You would use the Data > Outline > Group function to hide all the columns on the left.
I would add an additional heading that says Input Section (do not use merge cells to do this or anywhere)
On the right side I would use a heading called Output Section.

You will need to convert the table that appears on the bottom left hand side to an Excel table and give a Table Name of tbl_Meterage
for the formulas to work.

If you decide this is the way to go you can in theory delete:
U:X and
M:P

20210811 Word To Excel Transpose Formulas etc.xlsx
	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	AB	AC	AD	AE	AF	AG	AH	AI
1	Contact Number	Length*	Are you requesting to travel*	Depart Sydney DATE OF TRAVEL	Sydney Truck Run	Depart Hazelbrook DATE OF TRAVEL	Hazelbrook Truck Run	Depart Sydney DATE OF TRAVEL	Sydney Truck Run	Depart Hazelbrook DATE OF TRAVEL	Hazelbrook Truck Run	Travel Category	**NEW ** Day Counter	**NEW MANUAL** TC Charge	**NEW MANUAL** Truck Charge	**NEW MANUAL** Total Amount $*		Contact Number	Length*	Are you requesting to travel*	Depart Sydney DATE OF TRAVEL	Sydney Truck Run	Depart Hazelbrook DATE OF TRAVEL	Hazelbrook Truck Run	Depart Sydney DATE OF TRAVEL	Sydney Truck Run	Depart Hazelbrook DATE OF TRAVEL	Hazelbrook Truck Run	Travel Category	**NEW ** Day Counter	**NEW MANUAL** TC Charge	**NEW MANUAL** Truck Charge	**NEW MANUAL** Total Amount $*	**NEW MANUAL** Truck Charge MULTIPLIER
2	442691010	6.5	both ways (return)					28/07/2021	Blue Truck - Run 2, loading 10.30am	28/07/2021	Blue Truck - Run 2, loading 1.00pm	Other Type	1	1	465	466		442691010	6.5	both ways (return)					28/07/2021	Blue Truck - Run 2, loading 10.30am	28/07/2021	Blue Truck - Run 2, loading 1.00pm	Other Type	1	1	465	466	1
3	430352594	6	both ways (return)					3/08/2021	Blue Truck - Run 1, loading 6.30am	3/08/2021	Blue Truck - Run 2, loading 1.00pm	Other Type	1	1	255	256		430352594	6	both ways (return)					3/08/2021	Blue Truck - Run 1, loading 6.30am	3/08/2021	Blue Truck - Run 2, loading 1.00pm	Other Type	1	1	510	511	2
4	458231430	5.3	both ways (return)					5/08/2021	Blue Truck - Run 1, loading 6.30am	6/08/2021	Blue Truck - Run 2, loading 1.00pm	Regular (GVM < 4.5 Tonne)	2	10	255	265		458231430	5.3	both ways (return)					5/08/2021	Blue Truck - Run 1, loading 6.30am	6/08/2021	Blue Truck - Run 2, loading 1.00pm	Regular (GVM < 4.5 Tonne)	2	10	510	520	2
5	445893014	5.6	both ways (return)					28/07/2021	Blue Truck - Run 1, loading 6.30am	30/07/2021	Blue Truck - Run 1, loading 9.00am	Other Type	3	3	255	258		445893014	5.6	both ways (return)					28/07/2021	Blue Truck - Run 1, loading 6.30am	30/07/2021	Blue Truck - Run 1, loading 9.00am	Other Type	3	3	510	513	2
6	473771850	9.3	both ways (return)					28/07/2021	Blue Truck - Run 1, loading 6.30am	30/07/2021	Blue Truck - Run 1, loading 9.00am	Heavy (GVM > 4.5 Tonne)	3	36	465	501		473771850	9.3	both ways (return)					28/07/2021	Blue Truck - Run 1, loading 6.30am	30/07/2021	Blue Truck - Run 1, loading 9.00am	Heavy (GVM > 4.5 Tonne)	3	36	930	966	2
7	461433434	9	both ways (return)					4/08/2021	Blue Truck - Run 2, loading 10.30am	6/08/2021	Blue Truck - Run 1, loading 9.00am	Regular (GVM < 4.5 Tonne)	3	15	465	480		461433434	9	both ways (return)					4/08/2021	Blue Truck - Run 2, loading 10.30am	6/08/2021	Blue Truck - Run 1, loading 9.00am	Regular (GVM < 4.5 Tonne)	3	15	930	945	2
8	489312270	9.9	both ways (return)					3/08/2021	Blue Truck - Run 2, loading 10.30am	6/08/2021	Blue Truck - Run 2, loading 1.00pm	Heavy (GVM > 4.5 Tonne)	4	48	780	828		489312270	9.9	both ways (return)					3/08/2021	Blue Truck - Run 2, loading 10.30am	6/08/2021	Blue Truck - Run 2, loading 1.00pm	Heavy (GVM > 4.5 Tonne)	4	48	1560	1608	2
9	428877579	7.5	both ways (return)					16/08/2021	Blue Truck - Run 1, loading 6.30am	19/08/2021	Blue Truck - Run 2, loading 1.00pm	Regular (GVM < 4.5 Tonne)	4	20	465	485		428877579	7.5	both ways (return)					16/08/2021	Blue Truck - Run 1, loading 6.30am	19/08/2021	Blue Truck - Run 2, loading 1.00pm	Regular (GVM < 4.5 Tonne)	4	20	930	950	2
10	456756415	11.2	to Hazelbrook	26/07/2021	Red Truck - Run 1, Loading 8:00am								1	#N/A	780	780		456756415	11.2	to Hazelbrook					26/07/2021	Red Truck - Run 1, Loading 8:00am			0	0	#N/A	780	780	1
11	444417999	9	to Hazelbrook	26/07/2021	Red Truck - Run 1, Loading 8:00am								1	#N/A	465	465		444417999	9	to Hazelbrook					26/07/2021	Red Truck - Run 1, Loading 8:00am			0	0	#N/A	465	465	1
12	472296835	8	to Hazelbrook	23/07/2021	Blue Truck - Run 1, loading 6.30am								1	#N/A	465	465		472296835	8	to Hazelbrook					23/07/2021	Blue Truck - Run 1, loading 6.30am			0	0	#N/A	465	465	1
13	459958419	9	to Sydney			28/07/2021	Blue Truck - Run 1, loading 9.00am						1	#N/A	465	465		459958419	9	to Sydney							28/07/2021	Blue Truck - Run 1, loading 9.00am	0	0	#N/A	465	465	1
14	487837255	5.15	to Sydney			30/07/2021	Blue Truck - Run 2, loading 1.00pm						1	#N/A	255	255		487837255	5.15	to Sydney							30/07/2021	Blue Truck - Run 2, loading 1.00pm	0	0	#N/A	255	255	1
15
16
17	Meterage From	Meterage To	Price						HELPER CELLS BELOW
18	1.5	3	150						CHARGES MATRIX	DAILY RATE
19	3.01	6	255						Regular (GVM < 4.5 Tonne)	5
20	6.01	9.5	465						Heavy (GVM > 4.5 Tonne)	12
21	9.51	12.5	780						Other Type	1
22	12.51	18	840
23	18.1	23	890
24	23.1		1005
25	Pallet
26
Data

Cell Formulas
Range	Formula
M2:M14	M2	=SUM(J2-H2)+1
N2:N14,AE2:AE14	N2	=VLOOKUP(L2,$I$18:$J$21,2,FALSE)*M2
O2:O14	O2	=IF(AND(B2<1.5),110,IF(AND(B2>1.49,B2<3.01),150,IF(AND(B2>3,B2<6.01),255,IF(AND(B2>6,B2<9.51),465,IF(AND(B2>9.5,B2<12.51),780,IF(AND(B2>12.5,B2<18.01),840,IF(AND(B2>23),1005,0)))))))
P2:P14,AG2:AG14	P2	=SUMIF(N2:O2,"<>#N/A")
R2:T14,AC2:AC14	R2	=A2
Y2:Z14	Y2	=IF($H2<>"",H2,IF($D2<>"",D2,""))
AA2:AB14	AA2	=IF($H2<>"",J2,IF($F2<>"",F2,""))
AD2:AD14	AD2	=IF(C2="both ways (return)",SUM(AA2-Y2)+1,0)
AF2:AF14	AF2	=VLOOKUP(S2,tbl_Meterage,3,TRUE)*AH2
AH2:AH14	AH2	=IF(AND(Y2<>"",AA2<>""), IF(Y2<>AA2, 2, IF(LEFT(Z2,FIND(",",Z2))<>LEFT(AB2,FIND(",",AB2)), 2, 1)), 1)

 

[lemmo80]

The best approach depends on your skill level.
Ideally you want address how you are getting the data.
You say you are getting it from word but how is it getting into word, can you cut out that step and get a cleaner data source ?
Do you want to use VBA or Power Query ?

The simplest solution is to do it all using formulas.
You are already talking about hiding Columns D:G how about hiding all your current columns:
You would use the Data > Outline > Group function to hide all the columns on the left.
I would add an additional heading that says Input Section (do not use merge cells to do this or anywhere)
On the right side I would use a heading called Output Section.

You will need to convert the table that appears on the bottom left hand side to an Excel table and give a Table Name of tbl_Meterage
for the formulas to work.

If you decide this is the way to go you can in theory delete:
U:X and
M:P

20210811 Word To Excel Transpose Formulas etc.xlsx
	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	AB	AC	AD	AE	AF	AG	AH	AI
1	Contact Number	Length*	Are you requesting to travel*	Depart Sydney DATE OF TRAVEL	Sydney Truck Run	Depart Hazelbrook DATE OF TRAVEL	Hazelbrook Truck Run	Depart Sydney DATE OF TRAVEL	Sydney Truck Run	Depart Hazelbrook DATE OF TRAVEL	Hazelbrook Truck Run	Travel Category	**NEW ** Day Counter	**NEW MANUAL** TC Charge	**NEW MANUAL** Truck Charge	**NEW MANUAL** Total Amount $*		Contact Number	Length*	Are you requesting to travel*	Depart Sydney DATE OF TRAVEL	Sydney Truck Run	Depart Hazelbrook DATE OF TRAVEL	Hazelbrook Truck Run	Depart Sydney DATE OF TRAVEL	Sydney Truck Run	Depart Hazelbrook DATE OF TRAVEL	Hazelbrook Truck Run	Travel Category	**NEW ** Day Counter	**NEW MANUAL** TC Charge	**NEW MANUAL** Truck Charge	**NEW MANUAL** Total Amount $*	**NEW MANUAL** Truck Charge MULTIPLIER
2	442691010	6.5	both ways (return)					28/07/2021	Blue Truck - Run 2, loading 10.30am	28/07/2021	Blue Truck - Run 2, loading 1.00pm	Other Type	1	1	465	466		442691010	6.5	both ways (return)					28/07/2021	Blue Truck - Run 2, loading 10.30am	28/07/2021	Blue Truck - Run 2, loading 1.00pm	Other Type	1	1	465	466	1
3	430352594	6	both ways (return)					3/08/2021	Blue Truck - Run 1, loading 6.30am	3/08/2021	Blue Truck - Run 2, loading 1.00pm	Other Type	1	1	255	256		430352594	6	both ways (return)					3/08/2021	Blue Truck - Run 1, loading 6.30am	3/08/2021	Blue Truck - Run 2, loading 1.00pm	Other Type	1	1	510	511	2
4	458231430	5.3	both ways (return)					5/08/2021	Blue Truck - Run 1, loading 6.30am	6/08/2021	Blue Truck - Run 2, loading 1.00pm	Regular (GVM < 4.5 Tonne)	2	10	255	265		458231430	5.3	both ways (return)					5/08/2021	Blue Truck - Run 1, loading 6.30am	6/08/2021	Blue Truck - Run 2, loading 1.00pm	Regular (GVM < 4.5 Tonne)	2	10	510	520	2
5	445893014	5.6	both ways (return)					28/07/2021	Blue Truck - Run 1, loading 6.30am	30/07/2021	Blue Truck - Run 1, loading 9.00am	Other Type	3	3	255	258		445893014	5.6	both ways (return)					28/07/2021	Blue Truck - Run 1, loading 6.30am	30/07/2021	Blue Truck - Run 1, loading 9.00am	Other Type	3	3	510	513	2
6	473771850	9.3	both ways (return)					28/07/2021	Blue Truck - Run 1, loading 6.30am	30/07/2021	Blue Truck - Run 1, loading 9.00am	Heavy (GVM > 4.5 Tonne)	3	36	465	501		473771850	9.3	both ways (return)					28/07/2021	Blue Truck - Run 1, loading 6.30am	30/07/2021	Blue Truck - Run 1, loading 9.00am	Heavy (GVM > 4.5 Tonne)	3	36	930	966	2
7	461433434	9	both ways (return)					4/08/2021	Blue Truck - Run 2, loading 10.30am	6/08/2021	Blue Truck - Run 1, loading 9.00am	Regular (GVM < 4.5 Tonne)	3	15	465	480		461433434	9	both ways (return)					4/08/2021	Blue Truck - Run 2, loading 10.30am	6/08/2021	Blue Truck - Run 1, loading 9.00am	Regular (GVM < 4.5 Tonne)	3	15	930	945	2
8	489312270	9.9	both ways (return)					3/08/2021	Blue Truck - Run 2, loading 10.30am	6/08/2021	Blue Truck - Run 2, loading 1.00pm	Heavy (GVM > 4.5 Tonne)	4	48	780	828		489312270	9.9	both ways (return)					3/08/2021	Blue Truck - Run 2, loading 10.30am	6/08/2021	Blue Truck - Run 2, loading 1.00pm	Heavy (GVM > 4.5 Tonne)	4	48	1560	1608	2
9	428877579	7.5	both ways (return)					16/08/2021	Blue Truck - Run 1, loading 6.30am	19/08/2021	Blue Truck - Run 2, loading 1.00pm	Regular (GVM < 4.5 Tonne)	4	20	465	485		428877579	7.5	both ways (return)					16/08/2021	Blue Truck - Run 1, loading 6.30am	19/08/2021	Blue Truck - Run 2, loading 1.00pm	Regular (GVM < 4.5 Tonne)	4	20	930	950	2
10	456756415	11.2	to Hazelbrook	26/07/2021	Red Truck - Run 1, Loading 8:00am								1	#N/A	780	780		456756415	11.2	to Hazelbrook					26/07/2021	Red Truck - Run 1, Loading 8:00am			0	0	#N/A	780	780	1
11	444417999	9	to Hazelbrook	26/07/2021	Red Truck - Run 1, Loading 8:00am								1	#N/A	465	465		444417999	9	to Hazelbrook					26/07/2021	Red Truck - Run 1, Loading 8:00am			0	0	#N/A	465	465	1
12	472296835	8	to Hazelbrook	23/07/2021	Blue Truck - Run 1, loading 6.30am								1	#N/A	465	465		472296835	8	to Hazelbrook					23/07/2021	Blue Truck - Run 1, loading 6.30am			0	0	#N/A	465	465	1
13	459958419	9	to Sydney			28/07/2021	Blue Truck - Run 1, loading 9.00am						1	#N/A	465	465		459958419	9	to Sydney							28/07/2021	Blue Truck - Run 1, loading 9.00am	0	0	#N/A	465	465	1
14	487837255	5.15	to Sydney			30/07/2021	Blue Truck - Run 2, loading 1.00pm						1	#N/A	255	255		487837255	5.15	to Sydney							30/07/2021	Blue Truck - Run 2, loading 1.00pm	0	0	#N/A	255	255	1
15
16
17	Meterage From	Meterage To	Price						HELPER CELLS BELOW
18	1.5	3	150						CHARGES MATRIX	DAILY RATE
19	3.01	6	255						Regular (GVM < 4.5 Tonne)	5
20	6.01	9.5	465						Heavy (GVM > 4.5 Tonne)	12
21	9.51	12.5	780						Other Type	1
22	12.51	18	840
23	18.1	23	890
24	23.1		1005
25	Pallet
26
Data

Cell Formulas
Range	Formula
M2:M14	M2	=SUM(J2-H2)+1
N2:N14,AE2:AE14	N2	=VLOOKUP(L2,$I$18:$J$21,2,FALSE)*M2
O2:O14	O2	=IF(AND(B2<1.5),110,IF(AND(B2>1.49,B2<3.01),150,IF(AND(B2>3,B2<6.01),255,IF(AND(B2>6,B2<9.51),465,IF(AND(B2>9.5,B2<12.51),780,IF(AND(B2>12.5,B2<18.01),840,IF(AND(B2>23),1005,0)))))))
P2:P14,AG2:AG14	P2	=SUMIF(N2:O2,"<>#N/A")
R2:T14,AC2:AC14	R2	=A2
Y2:Z14	Y2	=IF($H2<>"",H2,IF($D2<>"",D2,""))
AA2:AB14	AA2	=IF($H2<>"",J2,IF($F2<>"",F2,""))
AD2:AD14	AD2	=IF(C2="both ways (return)",SUM(AA2-Y2)+1,0)
AF2:AF14	AF2	=VLOOKUP(S2,tbl_Meterage,3,TRUE)*AH2
AH2:AH14	AH2	=IF(AND(Y2<>"",AA2<>""), IF(Y2<>AA2, 2, IF(LEFT(Z2,FIND(",",Z2))<>LEFT(AB2,FIND(",",AB2)), 2, 1)), 1)

thank you, much appreciated, I'll suss it out and get back to you shortly.

 

[Alex Blakenburg]

In case you haven't used Excel Tables before,
To convert the metreage data into a table:

• Select any cell in the data range of the table
• Either Insert > Table (3rd button from the left ) OR Ctrl + T
• Give the Table the name tbl_Meterage
  Under Table Design > Table Name = White box on the far left

 

[lemmo80]

In case you haven't used Excel Tables before,
To convert the metreage data into a table:

• Select any cell in the data range of the table
• Either Insert > Table (3rd button from the left ) OR Ctrl + T
• Give the Table the name tbl_Meterage
  Under Table Design > Table Name = White box on the far left

Big big thanks Alex, sorry about the delayed reply. Great idea on the full hide, with input/output section, it all worked superb. Heaps of new stuff in there for me to learn/practice. Love the excel table, that really cleaned up that nested formula. thank you so much. This forum is so helpful. thanks again Alex.

 

[Alex Blakenburg]

Really appreciate your feedback, glad we were able to help.