Dynamic reference to table headers

[ShieBoon]

Hi all, I have a combobox in a userform, and i want to fill that combobox with table headers.

For e.g.
Fruits Vegetables Nuts
Orange Celery Almonds
Apple Cabbage Cashew
Pear Tomato Peanuts

I want to fill the combobox with "Fruits", "Vegetables" and "Nuts". I tried with:

=OFFSET(Sheet1$A$1,0,0,1,COUNTA(Sheet1!$1:$1))

The reference works but, the values do not appear in the combobox. Any ideas?

excel 2003

Thanks
Shie Boon

 

[syntaxed]

Could it be the missing exclamation mark?
=OFFSET(Sheet1!$A$1,0,0,1,COUNTA(Sheet1!$1:$1))

 

[RoryA]

You need to transpose the data somewhere, or use code.

 

[ShieBoon]

Hi syntaxed and rorya,

Many thanks for the replies

To syntaxed. I don't think so, left that out in the post. Sorry!

To rorya. I tried to transpose but the formula did not work. I'll try again. Thanks!