Formulas and relative pointing

[atrainwreck]

First off, I want to thank Mr. Excel for creating a forum that has been helpful over the years and a great information of wealth. Ive been a member since 2012 and still kicking lol. Thank you all in advance.

So the issue....I'm stumped on what technique to use regarding relative location of data when copying and pasting formulas/data referenced from a sheet that has a range of say A1:B100 without spaces between the rows. The problem I'm having with data being collected onto a 5 row range sheet where it needs to be copied down in increments of 5 yet start with the next row A2 from that reference A1:B100 sheet. Offset I thought was the solution, but unfortunately, that didn't work for me. VBA is more powerful, but I'm stuck on what to consider.

To be clear, the example shown below. The 5 row data table, needs to be copied down in 5 row increments. Row 1, B1 starts with a relative reference from ="BOG:"&Inners!A2. What I need to show when this 5 row range is copied to row 6, the next reference in A6 is ="BOG:"&Inners!A3.

When copying the rows, I'll get a relative reference of ="BOG:"&Inners!A7...not what I want. Same issue with the other relative ranges. Obliviously, the text cells and absolute values aren't an issue. Any help or guidance what to consider would be appreciated. The hack I know would work would be to change the A1:B100 sheet to add blank rows between the data. Then all information aligns to the table/referenced formulas. That makes the referenced sheet messy and hard to read....especially since it would turn that page into ~500 rows.

Thanks for the help!

Text	="BOG:"&Inners!A2
Text	Text
Text	Text
Text	="SM:"&XLOOKUP(Inners!S2,Inners!$AE$2:$AE$14,XLOOKUP(--MID(Inners!A2,10,2),Inners!$AH$1:$AL$1,Inners!$AH$2:$AL$14))
Text	Text

 

[number kruncher]

will

="BOG:"&Inners!A$2

work for you ?

nk

 

[FormR]

Hi, do you mean like this.

Book1
	A	B
1		A
2	A
3	B
4	C
5	D
6	E	B
7
8
9
10
11
12		C
13
14
15
16
17
18		D
19
20
21
22
23		E
Sheet1

Cell Formulas
Range	Formula
B1,B23,B18,B12,B6	B1	=INDEX($A$2:$A$6,ROUNDUP(ROWS(B$1:B1)/5,0))

 

[atrainwreck]

will

="BOG:"&Inners!A$2

work for you ?

nk

Unfortunately, that won't work since it locks the referenced cell formula. It need to reference the data from the next referenced row.

will

="BOG:"&Inners!A$2

work for you ?

nk

 

[number kruncher]

Ok.. can you tell us what

="BOG:"&Inners!A2

Should become please?

nk

 

[FormR]

Like this:

="Bog:"&INDEX(Inners!$A$2:$A$6,ROUNDUP(ROWS(B$1:B1)/5,0))

Change the cell reference in the ROWS() function to the cell in which you place the first formula before copying to the other rows.

 

[atrainwreck]

Hi, do you mean like this.

Book1
	A	B
1		A
2	A
3	B
4	C
5	D
6	E	B
7
8
9
10
11
12		C
13
14
15
16
17
18		D
19
20
21
22
23		E
Sheet1

Cell Formulas
Range	Formula
B1,B23,B18,B12,B6	B1	=INDEX($A$2:$A$6,ROUNDUP(ROWS(B$1:B1)/5,0))

This copies the data from the above rows, but does not use the formulas and array which pulls the data in. The formula "="BOG:"&Inners!A2", if I only had to copy it down each row, would be consecutively ="BOG:"&Inners!A3, ="BOG:"&Inners!A4 etc....which is the data I need to display pulled from that sheet. The problem is my cell reference gets out of order since I'm referencing consecutive rows, and on the table, the row is part of the 5 row table.

 

[atrainwreck]

Ok.. can you tell us what

="BOG:"&Inners!A2

Should become please?

nk

data pulled from sheet inner in column A

'references="BOG:"&Inners!A2	I1-4-020-65D-18
'references'="BOG:"&Inners!A3	I1-4-035-65D-20
'references'="BOG:"&Inners!A4	I1-4-040-65D-21
etc	I1-4-040-95D-21
	I1-4-045-65D-21
	I1-4-050-65D-22
	I1-4-050-65F-22
	I1-4-055-65D-22
	I1-4-065-65D-23
	I1-5-012-95D-18
	I1-5-012-95F-18
	I1-5-017-26F-18

 

[FormR]

but does not use the formulas and array which pulls the data in

Sorry, I don't know what you mean by this - what formulas? what arrays?

Book1
	A	B
1		Bog:A
2	A
3	B
4	C
5	D
6	E	Bog:B
7
8
9
10
11
12		Bog:C
13
14
15
16
17
18		Bog:D
19
20
21
22
23		Bog:E
Inners

Cell Formulas
Range	Formula
B1,B23,B18,B12,B6	B1	="Bog:"&INDEX(Inners!$A$2:$A$6,ROUNDUP(ROWS(B$1:B1)/5,0))

 

[atrainwreck]

Like this:

="Bog:"&INDEX(Inners!$A$2:$A$6,ROUNDUP(ROWS(B$1:B1)/5,0))

Change the cell reference in the ROWS() function to the cell in which you place the first formula before copying to the other rows.

oooh...this may work. I'll test it tomorrow and report back with results.

Offhand $A$2:$A$6, I would think needs adjusted to the #of rows on that sheet(~300) i.e. $A$2:$A$300 otherwise it would stop calculations after the 6th row if I'm not mistaken. It's super late and need to get some sleep. Thank you so much all of you for taking the time to help with responses so quickly.