Dynamic link list

[goofy78270]

Is it possible to create a dynamic link list?

I am looking to create a list that varies in size by day and i do not want to have the extra blanks in the list by accounting for the max number of entries. I would like the list to only pull in values within a range and not account for the blanks.

 

[Dan Waterloo]

Which version of Excel are you using?
If you are on Excel 2003 or later you can create a dynamic list by using /Data/List.

 

[Dan Waterloo]

Also, what are you aiming to use this list for?

 

[goofy78270]

I am aiming to use this for a drop down list.

 

[FlyingKiwi]

This code does it:

=OFFSET(startcell,0,0,COUNTA(column),1)

( ie =OFFSET('sheetname'!$D$6,0,0,COUNTA('sheetname'!$D:$D),1)
this will start counting the list from D6 and go down for whatever the count is for the column.

 

[goofy78270]

I am not sure if this works because when i put it in the input range of the list box, it clears itself out when I say OK. Is there a technique or step I am missing?

 

[FlyingKiwi]

Sorry I didn't think it through, I missed a step.
First use the code above to create a 'dynamic named range' then link this to your input range in the list box

 

[goofy78270]

I added the following code to a sheet and it only returns the first value. Should this code be within a macro?

=OFFSET(D2,0,0,COUNTA(D:D),1)

 

[FlyingKiwi]

The code isn't need to be in a macro. This doesn't use VBA only excel functions.
Try using absolute cell references instead of relative cell references
(ie $D$2 instead of D2 and $D:$D instead of D:D)