Change Named Cell ranges based upon value of another cell?
 

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?

Change Named Cell ranges based upon value of another cell?
 

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

Change Named Cell ranges based upon value of another cell?
 

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)

"fedude" wrote in message
...
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?

Change Named Cell ranges based upon value of another cell?
 

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.

--
Regards,
Tom Ogilvy


"fedude" wrote:

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?

Change Named Cell ranges based upon value of another cell?
 

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

"Bob Phillips" wrote:

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)

"fedude" wrote in message
...
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?