Dynamic Range of Cell Values in a single Row
 

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?

Dynamic Range of Cell Values in a single Row
 

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

--
HTH

Bob Phillips

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

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

Dynamic Range of Cell Values in a single Row
 

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.

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


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

Dynamic Range of Cell Values in a single Row
 

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.



"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

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

Dynamic Range of Cell Values in a single Row
 

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.

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

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

Dynamic Range of Cell Values in a single Row
 

Karen,

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

Bernie

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

"Bob Phillips" wrote:

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

--
HTH

Bob Phillips

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

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