Dynamic Named Range - Moving

**jamesryan** · 05-01-2008, 03:24 AM

Hi

I have a worksheet (as attached) which has eight lists of products.

I want to create a dynamic named range which changes depending on which list of products is selected.

eg.

List1 List2 List3
aaa bbb ddd
ggg ttt ppp
rrr xxx bbb
ooo

If the user selects "List1" from a separate dropdown box then the named range will become A1:A4, if "List2" it will become B1:B5, etc.

Please take a look at the attached sheet as the formula i have used in the define name box does not work, i have even split the formula up into parts on the worksheet and each part calculates the desired result correctly, but not when put together for some reason.

Thanks

James

**mikerickson** · 05-01-2008, 03:37 AM

Using two named ranges would be one approach

Name: myColumn
RefersTo: =OFFSET(xxx!$A$1,0,MATCH(xxx!I28,xxx!$A$1:$H$1,0)-1,ROWS(xxx!$A:$A),1)

Name: myDynamicRange
RefersTo: =OFFSET(myColumn,0,0,COUNTA(myColumn),1)

**jamesryan** · 05-01-2008, 04:34 AM

Hi,

Thanks for repyling, however i can't get this to work, can you try it for me and attach it back?

Thanks

**mikerickson** · 05-01-2008, 08:53 AM

When using relative refereneces in Names, it is imported to select the proper cell before defining the Name. In the attached, J28 was active when myColumn was defined, so myColumn (and myDynamicRange) is the column whose header matches the cell to the left of the formula.

Dynamic Named Range - Moving

LinkBack

Thread Tools

Rate This Thread

Display

Dynamic Named Range - Moving

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions