Bring info in every 6th from another workbook

[ozbeachbum]

Hi All,
I am endeavouring to bring in the value from the cell in every 6th row in another workbook, I have tried the formula below without any success.

=OFFSET('[02 REG CHR securities.xlsx]Securities Info'!$BX$75,(ROW('[02 REG CHR securities.xlsx]Securities Info'!BX1)-1)*6,0)

Any assistance would be greatly appreciated.
Cheers,
Dave.

 

[ozbeachbum]

Thanks,
I am just posting a query due to that exact situation.

 

[ozbeachbum]

Firstly to Fluff & All, apologies, I have been told I broke the rules when I did the new thread.
I thought by marking this thread solved that it would be closed and now that my query related to a different issue it would require a new thread.

I would like to thank Flashbond for their reply on the other thread;
=INDEX('[02 REG CHR securities.xlsx]Securities Info'!$BX$75:$BX$135,((ROWS($A$1:$A1)-1)*6)+1)
The formula however returns a Zero when the result should 535.
Not sure if this will help, I performed an evaluate formula which showed; 1-1=0 *6=0 +1=1 $BX$75:$BX$135=0

Below is the situation.
I started with;
=OFFSET('[02 REG CHR securities.xlsx]Securities Info'!$BX$75,(ROW('[02 REG CHR securities.xlsx]Securities Info'!BX1)-1)*6,0)

which worked, but requires the linked workbook '[02 REG CHR securities.xlsx] to be open, to recalculate.
I did some research and apparently this is the case when using OFFSET, therefore to solve the recalculating Issue I tried INDEX as suggested in the article;

=INDEX('[02 REG CHR securities.xlsx]Securities Info'!$BX$75,(ROW('[02 REG CHR securities.xlsx]Securities Info'!BX1)-1)*6,0)

I though this solved the issue as it worked, but only in the first cell, when I copied it down, I got an invalid reference or error.
I am sure Flashbond is correct when they say INDEX requires a range.
Any more ideas would be greatly appreciated.
Dave.

 

[Legacy 143009]

1-1=0 *6=0 +1=1 which means first index.
First index of BX75:BX135 must be BX75.
Then 2-1=1 *6=6 +1=7. 7th index must be BX81.. And so on...
I don't know what doesn't work for you

 

[Fluff]

Both formulae work for me

Fluff.xlsm
	B	C	D	E
75	1		1	1
76	2		7	7
77	3		13	13
78	4		19	19
79	5		25	25
80	6		31	31
81	7		37	37
82	8		43	43
83	9		49	49
84	10
85	11
86	12
87	13
88	14
89	15
90	16
91	17
92	18
93	19
94	20
95	21
96	22
97	23
98	24
99	25
100	26
101	27
102	28
103	29
104	30
105	31
106	32
107	33
108	34
109	35
110	36
111	37
112	38
113	39
114	40
115	41
116	42
117	43
118	44
119	45
120	46
121	47
122	48
123	49
Main

Cell Formulas
Range	Formula
D75:D83	D75	=OFFSET($B$75,(ROW(B1)-1)*6,0)
E75:E83	E75	=INDEX(B$75:$B$135,((ROWS($A$1:$A1)-1)*6)+1)

 

[Legacy 143009]

Oh.. @Fluff is right as always. Just ROW would be enough.

=INDEX(B$75:$B$135,((ROW($A1)-1)*6)+1)

 

[jdellasala]

This would be much easier in Power Query. Bring the data into PQ, add an Index Column from 1, then add a second column that takes the MOD 6 of the Index Column, filter the Custom Column selecting rows that equal 0, then load that into a table.

let
    Source = Excel.CurrentWorkbook(){[Name="GDP"]}[Content],
    ChangedType = Table.TransformColumnTypes(Source,{{"Column1", type date}, {"Column2", type number}}),
    AddedIndex = Table.AddIndexColumn(ChangedType, "Index", 1, 1, Int64.Type),
    InsertedModulo = Table.AddColumn(AddedIndex, "Modulo", each Number.Mod([Index], 6), type number),
    FilteredRows = Table.SelectRows(InsertedModulo, each ([Modulo] = 0)),
    RenamedColumns = Table.RenameColumns(FilteredRows,{{"Column1", "Date"}, {"Column2", "Value"}}),
    RemovedOtherColumns = Table.SelectColumns(RenamedColumns,{"Date", "Value"})
in
    RemovedOtherColumns

This is some of the original data with every 6th row highlighted:

Book1
	E	F
3	Row Count:	303
4	Row Count / 6:	50
5
6	Date	Value
7	01/01/1947	2,034.450
8	04/01/1947	2,029.024
9	07/01/1947	2,024.834
10	10/01/1947	2,056.508
11	01/01/1948	2,087.442
12	04/01/1948	2,121.899
13	07/01/1948	2,134.056
14	10/01/1948	2,136.440
15	01/01/1949	2,107.001
16	04/01/1949	2,099.814
17	07/01/1949	2,121.493
18	10/01/1949	2,103.688
19	01/01/1950	2,186.365
20	04/01/1950	2,253.045
21	07/01/1950	2,340.112
22	10/01/1950	2,384.920
23	01/01/1951	2,417.311
24	04/01/1951	2,459.196
Sheet1

Cell Formulas
Range	Formula
E3	E3	="Row Count: "
F3	F3	=COUNT(E7:E309)
E4	E4	="Row Count / 6: "
F4	F4	=INT(COUNT(E7:E309)/6)
E6:F6	E6	=PROPER(A7:B7)
E7:F309	E7	=_FredRange(A8)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
GDP	=Sheet1!$E$7#	F3:F4

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E7:F309	Expression	=$G7=0	text	NO

This is the result of the Power Query transformation:

Book1
	I	J
4	Row Count:	50
5
6	Date	Value
7	04/01/1948	2121.899
8	10/01/1949	2103.688
9	04/01/1951	2459.196
10	10/01/1952	2650.431
11	04/01/1954	2654.456
12	10/01/1955	2916.985
13	04/01/1957	2987.699
14	10/01/1958	3065.141
15	04/01/1960	3260.177
16	10/01/1961	3440.924
17	04/01/1963	3669.02
18	10/01/1964	3968.878
19	04/01/1966	4424.581
20	10/01/1967	4618.812
21	04/01/1969	4938.728
22	10/01/1970	4938.857
23	04/01/1972	5368.485
24	10/01/1973	5731.632
25	04/01/1975	5591.382
Sheet1

Cell Formulas
Range	Formula
J4	J4	=COUNT(GDP_2[Date])

Ignore _FredRange - it's a LAMBDA function. GDP is the named range of the raw data.

Hope that helps.

 

[jdellasala]

There is also a single cell spilled array answer:

Book1
	G
75	6
76	12
77	18
78	24
79	30
80	36
81	42
82	48
Sheet2

Cell Formulas
Range	Formula
G75:G82	G75	=LET( Rng, B75:B123, Rslt, IFERROR(IF(MOD(Rng,6)=0, Rng, ""), ""), FILTER(Rslt, ISNUMBER(Rslt)) )
Dynamic array formulas.

The variable Rng is the column of numbers from above, Rslt is the array from the IFERROR section, and the final display is from FILTER.

 

[Fluff]

Another option for a spilled range is simply

Fluff.xlsm
	A	B	C	D
75		1		1
76		2		7
77		3		13
78		4		19
79		5		25
80		6		31
81		7		37
82		8		43
83		9		49
84		10
85		11
86		12
87		13
88		14
89		15
90		16
91		17
92		18
93		19
94		20
95		21
96		22
97		23
98		24
99		25
100		26
101		27
102		28
103		29
104		30
105		31
106		32
107		33
108		34
109		35
110		36
111		37
112		38
113		39
114		40
115		41
116		42
117		43
118		44
119		45
120		46
121		47
122		48
123		49
Main

Cell Formulas
Range	Formula
D75:D83	D75	=INDEX(B$75:$B$135,SEQUENCE(9,,,6))
Dynamic array formulas.

 

[ozbeachbum]

Hi All,
My original INDEX formula that Fluff & Flashbond said should work, now works.
I rebooted and redid them and they work, don't know what is going, but it is not the only issue I have had since upgrading to W11 & Office 20-21.
To Fluff,
You must be happy with England at the moment, which is more than I can say for the Wallabies.
Dave.

 

[Fluff]

Glad we could help & thanks for the feedback.