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

 

[FormR]

Offhand $A$2:$A$6, I would think needs adjusted to the #of rows on that sheet(~300) i.e. $A$2:$A$300

Yes, that's right

 

[atrainwreck]

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

Its located in B4 from the first post I mentioned. ...just more data pulled from the sheet "inners". This formula also has relative ranges i.e. S2 and A2 that I'll assume I can use your suggestion. I'll try it all tomorrow and report back. It's 230am now . This old fart needs sleep so I'm not grumpy.

="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))

 

[atrainwreck]

The results: ="BOG:"&Inners!A2 using @FormR revision suggestion Formula A: ="BOG:"&INDEX(Inners!$A$3:$A$350,ROUNDUP(ROWS(B$4:B4)/5,0)) works perfect. My original plan of offset wasn't a good one. I learned something new with roundup. I can follow the cell reference easily when I copy this formula 5 rows away, it pulls the data from sheet inner 5 rows up and 0 columns.

Where I'm having difficult grasping the other formula which uses an array and cell references, it's not so easy. I'll call that Formula B.

Formula B: ="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))

S2 an A2 are the only relative cells, but it's within the array. I can't figure out how to add index & roundup into "Formula B" without getting spill errors. A work around could be to place "Formula B" into a new column on sheet Inners then copy down to 350, then reference the cell location as in Formula A. i.e.: =INDEX(Inners!$AO$3:$AO$350,ROUNDUP(ROWS(B$4:B4)/5,0))

Is there a way to add index and roundup into "Formula B" without creating a new column on the inner sheet?

 

[FormR]

Not tested, but you you could do it something like this.

=LET(RW,ROUNDUP(ROWS(B$2:B2)/5,0),"SM:"&XLOOKUP(INDEX(Inners!$S$2:$S$999,RW),Inners!$AE$2:$AE$14,XLOOKUP(--MID(INDEX(Inners!$A$2:$A$999,RW),10,2),Inners!$AH$1:$AL$1,Inners!$AH$2:$AL$14)))

You should change the B2 references in the ROWS() function to be the reference of the cell in which you place the first formula.

BTW, what sheet are you using this formula in? If it's in the "Inners" sheet itself then you should remove all instances of "Inners!" from the formula.

 

[atrainwreck]

Not tested, but you you could do it something like this.

=LET(RW,ROUNDUP(ROWS(B$2:B2)/5,0),"SM:"&XLOOKUP(INDEX(Inners!$S$2:$S$999,RW),Inners!$AE$2:$AE$14,XLOOKUP(--MID(INDEX(Inners!$A$2:$A$999,RW),10,2),Inners!$AH$1:$AL$1,Inners!$AH$2:$AL$14)))

You should change the B2 references in the ROWS() function to be the reference of the cell in which you place the first formula.

BTW, what sheet are you using this formula in? If it's in the "Inners" sheet itself then you should remove all instances of "Inners!" from the formula.

I'll try this today and change the B2 references. This formula doesn't sit within the sheet inners, so it will be correct how you wrote it. You're a genius if this works haha even if it doesn't. Most importantly it helps guide me to a hopefully working solution. I for the life of me couldn't wrap my head around getting index and roundup into that array. Ideas danced around my head but I was a like a one legged man in a butt kicking contest. . I'll report back later after I test it. Hopefully, this helps others too as I didn't see this type of a question come up anywhere on Mr.Excel YT videos or on the forum. I know there's a webinar coming up shortly. I wish I could attend, I've got a doctor appointment...figures .

 

[atrainwreck]

@FormR Confirmed working. I had to tweak a few things but you set me on the right path! Hopefully, I can be of assistance to board questions.