Replace variable Absolute Column Reference in formulas by a static numeric factor

[busjaj]

Hi,

I'm using Excel 2010. I have Sheet1 that contains eleven columns. Let's call this group of eleven columns LocationData.

On a Sheet2, I have one column for each LocationData group that summarizes the data using various formulas, referencing various columns in the group. All my formulas are setup for the first LocationData group using absolute references to Sheet1.

When I need to add additional LocationGroups, I have a macro that inserts the 11 columns into Sheet1.

I'd like to copy my single column in Sheet2 and replace the absolute column reference with an absolute reference to the column 11 columns to the right.

For example,

=SUM(Sheet1!$L$77:$L$79,Sheet1!$L$71:$L$72,Sheet1!$L$73:$L$75)

Would become:

=SUM(Sheet1!$W$77:$W$79,Sheet1!$W$71:$W$72,Sheet1!$W$73:$W$75)

I was hoping I could use something like this:

ActiveCell.Offset(0, 0).Columns("A:A").EntireColumn.Replace What:=absColRef, Replacement:=colRefPlusEleven, LookAt:= _xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

I could probably hard-code the $L, but I would rather do a pattern match of "$" plus 1-2 characters (absColRef) because there are several columns used across all my formulas. Is that possible?

Once I know the column characters, how do I increment them by a factor of 11?

Thanks for any guidance!

 

[Al Chara]

I'm not sure if I understood exactly what you are looking for, but let me know if this works for you. The formula is written in the way that the first column on Sheet2 is A, the second column on Sheet2 is B, etc.

=SUM(OFFSET(Sheet1!$L$77,0,COLUMN()*11-11,3,1),OFFSET(Sheet1!$L$71,0,COLUMN()*11-11,2,1),OFFSET(Sheet1!$L$73,0,COLUMN()*11-11,3,1))

 

[busjaj]

Wow, thank you! That works great and I hadn't considered anything like it. I love this board, I always learn something new.

I am worried that my colleagues won't be able to figure out the functions when they are trying to confirm/troubleshoot that its working as they expect (they aren't likely to just trust it). Any suggestions for something that makes the reference more transparent to the user?

 

[Al Chara]

You could always Define a Name, OffsetColumn, that Refers to =COLUMN()*11-11. Then you could change the formula to the following:

=SUM(OFFSET(Sheet1!$L$77,0,OffsetColumn,3,1),OFFSET(Sheet1!$L$71,0,OffsetColumn,2,1),OFFSET(Sheet1!$L$73,0,OffsetColumn,3,1))

 

[mikerickson]

You could use the one argument syntax of INDEX.

=SUM(INDEX(NamedRange,0,11)) returns the sum of the 11th column of named range.