I did
Insert=Name
Name: Style_List
RefersTo: =OFFSET(Data!$A$3,0,0,COUNTA(Data!$A:$A)-2)

Didn't make any difference what sheet was active.

To check the formula I went to the name box to the left of the formula bar
and entered

Style_List

it selected the list. (even changing the activesheet).

Then I went to a sheet other than data and did

Data=Validation

Selected the List option

in the Source Box I put in

=Style_List

and OK'd out

Worked fine for me.

There should be data in At least 3 cells in column A of Data before
starting.

--
Regards,
Tom Ogilvy



"Fid" wrote in message
oups.com...
Okay, so I have a named range on worksheet named "Data" called
"Style_List" that references the dynamic range
=OFFSET(Data!$A$3,0,0,COUNTA(Data!$A:$A)-2).

When I have sheet "Specs" active and I do Data-List-Create List and
put "=Style_List" in the box asking me the reference to the list I get
the error:

The worksheet range for the list data must be on the active worksheet.

I am confused as to what I am doing wrong.

From what you are telling me I sould be able to refer to a named range
not on the active worksheet.

Thanks,

Brent


Jim Jackson wrote:
As always, I only say half what I am thinking. Thanks, Tom for telling
"the
rest of the story."
--
Best wishes,

Jim


"Tom Ogilvy" wrote:

You can use a defined name (Insert=Name=Define) to reference the list
on
another sheet. Then use that name in your data validation

=List1

if list1 were the defined name.

--
Regards,
Tom Ogilvy


"Fid" wrote in message
oups.com...
I cannot use Data-List because that would require the list to be on
the
same worksheet. The list I need to reference is on another
worksheet.

Any other suggestions?

Thanks,

Brent


Jim Jackson wrote:
What if, instead of the Combo-Box, you use a list. "Data/List/..."
for
the
source range of the choices. Then Data/Validation with K3 selected.
--
Best wishes,

Jim


"Fid" wrote:

I have cell K3 that is linked to a combo box. When the user picks
a
value out of the combo box K3 changes to that value.

I need to run code when the value of K3 changes. I have the
following
code:

Private Sub Worksheet_Change(ByVal Target As Range) '

Application.EnableEvents = False
On Error GoTo ws_exit
If Not Intersect(Target, Range("K3")) Is Nothing Then
Hide_RowsPoochPad
End If

ws_exit:
Application.EnableEvents = True
End Sub

The worksheet_change event does not fire when the user selects an
item
from the combo box and the value of K3 changes.

What do I need to do to get the code to run when the value of K3
changes? I tried using the combo box change event but that will
not
suit my needs.

Thanks,

Brent