Change Named Cell ranges based upon value of another cell?

[Guest]

What I have is a row of numbers that contain 2 named ranges (Front and Back).
Both ranges are 7 columns wide and 1 row deep. What I want to do is change
the location of the ranges based upon another cell.

For instance, here are the numbers (each in it's own column):
5,2,7,4,1,6,3,8,9,5,2,7,4,1,6,3,8,9,5,2,7,4,1,6,3,8,9

The ranges are each 7 columns wide and are offset by the value in another
cell:
For instance if the cell contains 5, the range "Front" would be 1,6,3,8,9,5,2

Can someone point me in the right direction?

 

[Ivan Raiminius]

Hi,

while defining a name, instead of actual range, you can use formula
like this:

=offset(b1,0,a1,1,7)

supposing that your values
(5,2,7,4,1,6,3,8,9,5,2,7,4,1,6,3,8,9,5,2,7,4,1,6,3,8,9 ) are in second
row, starting at b1, and the cell which contains 5 is a1.

Did it help?

Regards,
Ivan

 

[Bob Phillips]

Define Front as

=OFFSET($1:$1,0,$A$10-1,1,7)

where A10 is the cell with the value

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[Guest]

insert => Name => Define

Name: Front
RefersTo: =Offset(Sheet1!$A$1,0,Sheet1!$F$20 - 1,1,7)

Assume F20 contains the value 5 and the data you show starts in A1 and
extends across row 1.

 

[Guest]

Perfect. Thanks to all who answered with the "offset" solution.