Grow column reference infinitely?
 

In both the formulas below how can I expand column "CP" infinitely as the
number of columns grows over time?
For 1 column move in sheet "Dashboard" (the current sheet), sheet "Satchmo
Daily" (the reference sheet), expands 7 columns. So the column expansion is
not able to keep up as the formula moves only one cell for every 7 reference
cells.

=LOOKUP('Dashboard (2)'!S$16,'Satchmo Daily'!$B$6:CP$6,'Satchmo
Daily'!$B25:CP25)

=SUM(OFFSET(INDEX('Satchmo Daily'!$B31:$CP31,MATCH('Dashboard
(2)'!C$16,'Satchmo Daily'!$B$6:$CP$6,0)),0,-6,1,7))

TIA,
Earl

Grow column reference infinitely?
 

Not sure that I follow you entirely but take a look at this link on Dynamic
Named Ranges... It might send you in th eright direction...

http://www.cpearson.com/excel/excelF.htm#DynamicRanges
--
HTH...

Jim Thomlinson


"earls" wrote:

In both the formulas below how can I expand column "CP" infinitely as the
number of columns grows over time?
For 1 column move in sheet "Dashboard" (the current sheet), sheet "Satchmo
Daily" (the reference sheet), expands 7 columns. So the column expansion is
not able to keep up as the formula moves only one cell for every 7 reference
cells.

=LOOKUP('Dashboard (2)'!S$16,'Satchmo Daily'!$B$6:CP$6,'Satchmo
Daily'!$B25:CP25)

=SUM(OFFSET(INDEX('Satchmo Daily'!$B31:$CP31,MATCH('Dashboard
(2)'!C$16,'Satchmo Daily'!$B$6:$CP$6,0)),0,-6,1,7))

TIA,
Earl