Count between columns given a cell address

[Ed Harris]

Hi
I have a column of cell addresses in AZ and I want to count the number of columns between those cell addresses and column AD.
I thought I could use Columns in BJ but can not see how to specify the cell contents over the cells own address.
Any help much appreciated.

test data 2021.xlsx
	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI	BJ
1	DATA26	DATA27	DATA28	DATA29	DATA30	TIME	TEMP	wind						ACCELEROMETER			chart no	average	H		R	Temp	wind	sum of acc					TEMP	WIND	H		PEAK		colour
2	74.5746	74.5746	74.57462	74.57472	74.57479	13/03/2021 09:11	13.6	3.6	1	0	0	1	0	0	0	0		74.57463	74.57463		0	13.6	3.6	0	26	Yellow	$AA$6005	31/03/2021 14:59	20.7	2.6	0	1	£0.092	-£0.092	3	1	#NAME?
3	74.57709	74.57709	74.57706	74.57704	74.57693	13/03/2021 09:23	13.7	6.2	1	0	0	1	0	0	0	0	1	74.57702	74.57702		0	13.7	6.2	0	3	Yellow	$AA$951	16/03/2021 06:13	13.5	2.9	0	1	£0.094	£0.094	3	2
4	74.57681	74.5768	74.57681	74.57679	74.57681	13/03/2021 09:27	13.7	7.8	0	1	0	1	0	0	0	0	2	74.57674		74.57674	0	13.7	7.8	0	57	Blue	$AC$4900	28/03/2021 06:45	14.7	6.2	1	0	£0.038	-£0.038	2	3
5	74.57553	74.57552	74.57552	74.57553	74.57554	13/03/2021 09:31	13.7	13.6	1	0	0	1	0	0	0	0	3	74.57556	74.57556		0	13.7	13.6	0	38	Blue	$AD$2388	20/03/2021 15:37	15.9	2.3	1	0	£0.134	-£0.134	2	4
Sheet4

Cell Formulas
Range	Formula
AT2:AT5	AT2	=AJ2
AU2:AV5	AU2	=AF2
AW2:AW5	AW2	=SUM(AM2:AO2)
BJ2	BJ2	=COLUMNS(AZ2:AD)
AR5,AR2:AR3	AR2	=AQ2
AQ2:AQ5	AQ2	=SUM(AD2,U2,K2)/3
BF2:BF5	BF2	=ABS(BG2)

 

[Habtest]

Try this and you may adjust based on definition of "numbers of columns"

Book2
	AZ										BJ
1
2	$AA$6005										4
3	$AA$951										4
4	$AC$4900										2
5	$AD$2388										1
Sheet1

Cell Formulas
Range	Formula
BJ2:BJ5	BJ2	=COLUMNS(INDIRECT("AD:"&LEFT(AZ2,FIND("$",AZ2,2)-1),1))

 

[Ed Harris]

Try this and you may adjust based on definition of "numbers of columns"

Book2
	AZ										BJ
1
2	$AA$6005										4
3	$AA$951										4
4	$AC$4900										2
5	$AD$2388										1
Sheet1

Cell Formulas
Range	Formula
BJ2:BJ5	BJ2	=COLUMNS(INDIRECT("AD:"&LEFT(AZ2,FIND("$",AZ2,2)-1),1))

Yes, Brilliant that works. Many thanks. I adjusted by subtracting outside the formula. I can look up the functions but can you give me a very brief explanation how it works?

 

[Habtest]

Take $AA$6005 as an example
FIND() finds "$" sign positin starting from the second character in cell AZ2, and returns 4, where the row number "$6005" begins.
LEFT() returns the string before "$6005", which is the column number "$AA";
What we need is essentially COLUMNS(AD:$AA), and nesting everything above inside INDIRECT() will do just that.

 

[Ed Harris]

Take $AA$6005 as an example
FIND() finds "$" sign positin starting from the second character in cell AZ2, and returns 4, where the row number "$6005" begins.
LEFT() returns the string before "$6005", which is the column number "$AA";
What we need is essentially COLUMNS(AD:$AA), and nesting everything above inside INDIRECT() will do just that.

Many thanks.