Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Can't highlight a single cell. Creates a range when I move curso | Excel Worksheet Functions | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Return Range of Numerical Values in Single Column based on Frequency Percentage | Excel Worksheet Functions | |||
copying cell names | Excel Discussion (Misc queries) |