Dynamic Range of Cell Values in a single Row

K

KarenB

I have defined a NAME with the following formula:
=OFFSET(CORPORATE!$A$1,0,0,1,COUNTA(CORPORATE!$1:$1))
which I think should return all values in row 1 of the "Corporate" sheet.

I've referenced that name in a dropdown box; however, only the very first
value appears when the list is dropped down.

When I click on the formula in the Define Name dialog box, all values in
that row are highlighted.

What am I missing?
 
B

Bob Phillips

It works fine for me. Did you use the List type?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
B

Bernie Deitrick

Karen,

If by this:
I've referenced that name in a dropdown box; however, only the very first
value appears when the list is dropped down.
Click to expand...

you mean that you are using Data Validation, then the list needs to reference the name using

=Name

When I do that, everything works well for me.

HTH,
Bernie
MS Excel MVP
 
K

KarenB

I'm not sure what you mean "did you use the List type"??? I have a row with
values in several columns. I need to dynamically count the number of columns
and return the list in a drop down list box.

I have defined the Name as "BusUnits"
The formula: =OFFSET(CORPORATE!$A$1,0,0,1,COUNTA(CORPORATE!$1:$1))
I created a ComboBox and, in the Format Control Input Range, I entered
"BusUnits"

When I drop down the list, there is only one value; however there are 18
values in the range.
 
K

KarenB

I have narrowed the problem down to the columns. If I use the same formula,
but exchange the height/width coordinates, it returns the right number of
values... unfortunately it returns 18 rows in a single column instead of what
I need: 18 columns in a single row.
 
B

Bernie Deitrick

Karen,

Sounds like you need to use Application.Transpose on the array when
assigning it to the control.

Bernie
 
Top