Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
building formulas that change frequently using named cell ranges | Excel Worksheet Functions | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
formula for named cell/range based on cell values | Excel Programming | |||
Named Cell Ranges | Excel Discussion (Misc queries) | |||
Named Cell Ranges | Excel Discussion (Misc queries) |