Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson


--

Dave Peterson

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson


--

Dave Peterson

Make sure you make the name a workbook level name.

Just in case you need some notes:
http://www.contextures.com/xlDataVal01.html#Name
(from Debra Dalgleish's site)

KarenF wrote:

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Thanks Dave.

I have noticed that I can't use a 2 column dynamic range as a lookup - or at
least, not the way I'm doing it! Perhaps I should try and put the "offset"
formula in instead of the dynamic range name. Anyway, I'll give this a good
go tomorrow. Thanks for the advice - you've been great.

Have a good weekend.

Karen.

"Dave Peterson" wrote:

Make sure you make the name a workbook level name.

Just in case you need some notes:
http://www.contextures.com/xlDataVal01.html#Name
(from Debra Dalgleish's site)

KarenF wrote:

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I think you may have made a mistake when you defined the range.

This kind of formula worked ok for me when I defined the name.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

And I could use it like:
=vlookup(a1,mynamehere,2,false)

As long as the range had 2 columns and at least one row <bg.

Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic

KarenF wrote:

Thanks Dave.

I have noticed that I can't use a 2 column dynamic range as a lookup - or at
least, not the way I'm doing it! Perhaps I should try and put the "offset"
formula in instead of the dynamic range name. Anyway, I'll give this a good
go tomorrow. Thanks for the advice - you've been great.

Have a good weekend.

Karen.

"Dave Peterson" wrote:

Make sure you make the name a workbook level name.

Just in case you need some notes:
http://www.contextures.com/xlDataVal01.html#Name
(from Debra Dalgleish's site)

KarenF wrote:

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,

Since the list I wish to use as a vlookup table is in columns C and D of a
sheet called Lists, should the formula be:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),COUNTA( Lists!$1:$1))

(the COUNTA(Lists!$1:$1) - does that look to define the region -i.e. look
for a completely blank column and end the range there? )

or would I need to define both columns as in:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$D),COUNTA( Lists!$1:$1))


I think I'm getting there - I would certainly still be stuck without your
help.

This is what I was using.

=OFFSET(Lists!$C$1:$D$1,0,0,COUNTA(Lists!$C:$D),1)

- you could say I missed a bit!

Thanks again.

Take care,

Karen


"Dave Peterson" wrote:

I think you may have made a mistake when you defined the range.

This kind of formula worked ok for me when I defined the name.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

And I could use it like:
=vlookup(a1,mynamehere,2,false)

As long as the range had 2 columns and at least one row <bg.

Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic

KarenF wrote:

Thanks Dave.

I have noticed that I can't use a 2 column dynamic range as a lookup - or at
least, not the way I'm doing it! Perhaps I should try and put the "offset"
formula in instead of the dynamic range name. Anyway, I'll give this a good
go tomorrow. Thanks for the advice - you've been great.

Have a good weekend.

Karen.

"Dave Peterson" wrote:

Make sure you make the name a workbook level name.

Just in case you need some notes:
http://www.contextures.com/xlDataVal01.html#Name
(from Debra Dalgleish's site)

KarenF wrote:

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Since you know that it's exactly 2 columns wide, I think I'd just bake that into
the formula:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),2)

=counta(lists!$c:$c)
will count the non-empty cells in that column (C). That's why it's important
not to have gaps between headers or within the data.

Once you define the name, you can test it by:
edit|goto
type in that name and see what's selected.

And yep, =counta(lists!$1:$1) will count the number of non-empty cells in row
1. But that doesn't sound like what you really want in this case.



KarenF wrote:

Hi Dave,

Since the list I wish to use as a vlookup table is in columns C and D of a
sheet called Lists, should the formula be:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),COUNTA( Lists!$1:$1))

(the COUNTA(Lists!$1:$1) - does that look to define the region -i.e. look
for a completely blank column and end the range there? )

or would I need to define both columns as in:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$D),COUNTA( Lists!$1:$1))

I think I'm getting there - I would certainly still be stuck without your
help.

This is what I was using.

=OFFSET(Lists!$C$1:$D$1,0,0,COUNTA(Lists!$C:$D),1)

- you could say I missed a bit!

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

I think you may have made a mistake when you defined the range.

This kind of formula worked ok for me when I defined the name.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

And I could use it like:
=vlookup(a1,mynamehere,2,false)

As long as the range had 2 columns and at least one row <bg.

Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic

KarenF wrote:

Thanks Dave.

I have noticed that I can't use a 2 column dynamic range as a lookup - or at
least, not the way I'm doing it! Perhaps I should try and put the "offset"
formula in instead of the dynamic range name. Anyway, I'll give this a good
go tomorrow. Thanks for the advice - you've been great.

Have a good weekend.

Karen.

"Dave Peterson" wrote:

Make sure you make the name a workbook level name.

Just in case you need some notes:
http://www.contextures.com/xlDataVal01.html#Name
(from Debra Dalgleish's site)

KarenF wrote:

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Brilliant!

Thanks again Dave.

I'll let you know when I've tried this and the macros etc.

Take care and enjoy your weekend.

Karen.

"Dave Peterson" wrote:

Since you know that it's exactly 2 columns wide, I think I'd just bake that into
the formula:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),2)

=counta(lists!$c:$c)
will count the non-empty cells in that column (C). That's why it's important
not to have gaps between headers or within the data.

Once you define the name, you can test it by:
edit|goto
type in that name and see what's selected.

And yep, =counta(lists!$1:$1) will count the number of non-empty cells in row
1. But that doesn't sound like what you really want in this case.



KarenF wrote:

Hi Dave,

Since the list I wish to use as a vlookup table is in columns C and D of a
sheet called Lists, should the formula be:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),COUNTA( Lists!$1:$1))

(the COUNTA(Lists!$1:$1) - does that look to define the region -i.e. look
for a completely blank column and end the range there? )

or would I need to define both columns as in:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$D),COUNTA( Lists!$1:$1))

I think I'm getting there - I would certainly still be stuck without your
help.

This is what I was using.

=OFFSET(Lists!$C$1:$D$1,0,0,COUNTA(Lists!$C:$D),1)

- you could say I missed a bit!

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

I think you may have made a mistake when you defined the range.

This kind of formula worked ok for me when I defined the name.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

And I could use it like:
=vlookup(a1,mynamehere,2,false)

As long as the range had 2 columns and at least one row <bg.

Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic

KarenF wrote:

Thanks Dave.

I have noticed that I can't use a 2 column dynamic range as a lookup - or at
least, not the way I'm doing it! Perhaps I should try and put the "offset"
formula in instead of the dynamic range name. Anyway, I'll give this a good
go tomorrow. Thanks for the advice - you've been great.

Have a good weekend.

Karen.

"Dave Peterson" wrote:

Make sure you make the name a workbook level name.

Just in case you need some notes:
http://www.contextures.com/xlDataVal01.html#Name
(from Debra Dalgleish's site)

KarenF wrote:

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,

Hope you had a good weekend. Thought I'd let you know that the vlookup
using the range as you described works perfectly.

Now I'm trying to use my dynamic ranges in dependent data validation (i.e.
using the range "sector" as a drop down in validation to choose a sector of
work, and then I have different ranges for each sector. I then want to base
the choice of job role on the sector that is chosen by making my validation
look to =indirect(A2), where the sector chosen looks at the range name for
the job roles in that sector. At the moment, the dependent list is blank for
some reason). When I have that sorted out, then I'm going to try your macros.

Thanks for your help Dave.

Take care

Karen.

"Dave Peterson" wrote:

Since you know that it's exactly 2 columns wide, I think I'd just bake that into
the formula:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),2)

=counta(lists!$c:$c)
will count the non-empty cells in that column (C). That's why it's important
not to have gaps between headers or within the data.

Once you define the name, you can test it by:
edit|goto
type in that name and see what's selected.

And yep, =counta(lists!$1:$1) will count the number of non-empty cells in row
1. But that doesn't sound like what you really want in this case.



KarenF wrote:

Hi Dave,

Since the list I wish to use as a vlookup table is in columns C and D of a
sheet called Lists, should the formula be:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),COUNTA( Lists!$1:$1))

(the COUNTA(Lists!$1:$1) - does that look to define the region -i.e. look
for a completely blank column and end the range there? )

or would I need to define both columns as in:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$D),COUNTA( Lists!$1:$1))

I think I'm getting there - I would certainly still be stuck without your
help.

This is what I was using.

=OFFSET(Lists!$C$1:$D$1,0,0,COUNTA(Lists!$C:$D),1)

- you could say I missed a bit!

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

I think you may have made a mistake when you defined the range.

This kind of formula worked ok for me when I defined the name.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

And I could use it like:
=vlookup(a1,mynamehere,2,false)

As long as the range had 2 columns and at least one row <bg.

Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic

KarenF wrote:

Thanks Dave.

I have noticed that I can't use a 2 column dynamic range as a lookup - or at
least, not the way I'm doing it! Perhaps I should try and put the "offset"
formula in instead of the dynamic range name. Anyway, I'll give this a good
go tomorrow. Thanks for the advice - you've been great.

Have a good weekend.

Karen.

"Dave Peterson" wrote:

Make sure you make the name a workbook level name.

Just in case you need some notes:
http://www.contextures.com/xlDataVal01.html#Name
(from Debra Dalgleish's site)

KarenF wrote:

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Debra has more notes...
http://contextures.com/xlDataVal02.html

In fact, Debra's site is a nice spot for lots of things!


KarenF wrote:

Hi Dave,

Hope you had a good weekend. Thought I'd let you know that the vlookup
using the range as you described works perfectly.

Now I'm trying to use my dynamic ranges in dependent data validation (i.e.
using the range "sector" as a drop down in validation to choose a sector of
work, and then I have different ranges for each sector. I then want to base
the choice of job role on the sector that is chosen by making my validation
look to =indirect(A2), where the sector chosen looks at the range name for
the job roles in that sector. At the moment, the dependent list is blank for
some reason). When I have that sorted out, then I'm going to try your macros.

Thanks for your help Dave.

Take care

Karen.

"Dave Peterson" wrote:

Since you know that it's exactly 2 columns wide, I think I'd just bake that into
the formula:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),2)

=counta(lists!$c:$c)
will count the non-empty cells in that column (C). That's why it's important
not to have gaps between headers or within the data.

Once you define the name, you can test it by:
edit|goto
type in that name and see what's selected.

And yep, =counta(lists!$1:$1) will count the number of non-empty cells in row
1. But that doesn't sound like what you really want in this case.



KarenF wrote:

Hi Dave,

Since the list I wish to use as a vlookup table is in columns C and D of a
sheet called Lists, should the formula be:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),COUNTA( Lists!$1:$1))

(the COUNTA(Lists!$1:$1) - does that look to define the region -i.e. look
for a completely blank column and end the range there? )

or would I need to define both columns as in:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$D),COUNTA( Lists!$1:$1))

I think I'm getting there - I would certainly still be stuck without your
help.

This is what I was using.

=OFFSET(Lists!$C$1:$D$1,0,0,COUNTA(Lists!$C:$D),1)

- you could say I missed a bit!

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

I think you may have made a mistake when you defined the range.

This kind of formula worked ok for me when I defined the name.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

And I could use it like:
=vlookup(a1,mynamehere,2,false)

As long as the range had 2 columns and at least one row <bg.

Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic

KarenF wrote:

Thanks Dave.

I have noticed that I can't use a 2 column dynamic range as a lookup - or at
least, not the way I'm doing it! Perhaps I should try and put the "offset"
formula in instead of the dynamic range name. Anyway, I'll give this a good
go tomorrow. Thanks for the advice - you've been great.

Have a good weekend.

Karen.

"Dave Peterson" wrote:

Make sure you make the name a workbook level name.

Just in case you need some notes:
http://www.contextures.com/xlDataVal01.html#Name
(from Debra Dalgleish's site)

KarenF wrote:

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,

yes, I have read these (I've bookmarked the home page for ease). I'm trying
to do as it suggests but failing for some reason. It has worked in past
spreadsheets for me, but not this time. I'm wondering (as Debra's example
does not include dynamic ranges) whether or not this will work if the
dependent range is a dyanmic one? If in a cell I type =rangename and
autofill, then I do see the content of the cells in the dependent names, but
not in the data validation list. I don't suppose you've any idea what I
could be doing wrong, or if there is another site out there I can read up on.

Thanks again Dave. I'm sure I'll finish this sometime soon. Once I've got
my lists up and running I have to consolidate them into another file and
create pivot tables and charts from the data (not anticipating too many
problems there - fingers crossed!).

Take care,

Thanks again.

Karen.

"Dave Peterson" wrote:

Debra has more notes...
http://contextures.com/xlDataVal02.html

In fact, Debra's site is a nice spot for lots of things!


KarenF wrote:

Hi Dave,

Hope you had a good weekend. Thought I'd let you know that the vlookup
using the range as you described works perfectly.

Now I'm trying to use my dynamic ranges in dependent data validation (i.e.
using the range "sector" as a drop down in validation to choose a sector of
work, and then I have different ranges for each sector. I then want to base
the choice of job role on the sector that is chosen by making my validation
look to =indirect(A2), where the sector chosen looks at the range name for
the job roles in that sector. At the moment, the dependent list is blank for
some reason). When I have that sorted out, then I'm going to try your macros.

Thanks for your help Dave.

Take care

Karen.

"Dave Peterson" wrote:

Since you know that it's exactly 2 columns wide, I think I'd just bake that into
the formula:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),2)

=counta(lists!$c:$c)
will count the non-empty cells in that column (C). That's why it's important
not to have gaps between headers or within the data.

Once you define the name, you can test it by:
edit|goto
type in that name and see what's selected.

And yep, =counta(lists!$1:$1) will count the number of non-empty cells in row
1. But that doesn't sound like what you really want in this case.



KarenF wrote:

Hi Dave,

Since the list I wish to use as a vlookup table is in columns C and D of a
sheet called Lists, should the formula be:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),COUNTA( Lists!$1:$1))

(the COUNTA(Lists!$1:$1) - does that look to define the region -i.e. look
for a completely blank column and end the range there? )

or would I need to define both columns as in:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$D),COUNTA( Lists!$1:$1))

I think I'm getting there - I would certainly still be stuck without your
help.

This is what I was using.

=OFFSET(Lists!$C$1:$D$1,0,0,COUNTA(Lists!$C:$D),1)

- you could say I missed a bit!

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

I think you may have made a mistake when you defined the range.

This kind of formula worked ok for me when I defined the name.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

And I could use it like:
=vlookup(a1,mynamehere,2,false)

As long as the range had 2 columns and at least one row <bg.

Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic

KarenF wrote:

Thanks Dave.

I have noticed that I can't use a 2 column dynamic range as a lookup - or at
least, not the way I'm doing it! Perhaps I should try and put the "offset"
formula in instead of the dynamic range name. Anyway, I'll give this a good
go tomorrow. Thanks for the advice - you've been great.

Have a good weekend.

Karen.

"Dave Peterson" wrote:

Make sure you make the name a workbook level name.

Just in case you need some notes:
http://www.contextures.com/xlDataVal01.html#Name
(from Debra Dalgleish's site)

KarenF wrote:

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Oooh.

You're right about the =indirect() with the dynamic names. I had forgotten that
(it's been awhile!).

If the lists only change when you're doing the import--the user or you don't
change them, maybe you could apply the names to the ranges after you paste the
cells into that existing worksheet.

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False

'now apply the names

with thisworkbook.worksheets(mstrnameswksname)
.range("A1",.cells(.rows.count,"A").end(xlup)).nam e = "List1"
.range("b1:c" & .cells(.rows.count,"B").end(xlup).row).name = "lookup1"
'etc
end with

End Sub

(watch for typos)

If the lists are much more dynamic, you could use some type of worksheet_change
event to reset the names.


KarenF wrote:

Hi Dave,

yes, I have read these (I've bookmarked the home page for ease). I'm trying
to do as it suggests but failing for some reason. It has worked in past
spreadsheets for me, but not this time. I'm wondering (as Debra's example
does not include dynamic ranges) whether or not this will work if the
dependent range is a dyanmic one? If in a cell I type =rangename and
autofill, then I do see the content of the cells in the dependent names, but
not in the data validation list. I don't suppose you've any idea what I
could be doing wrong, or if there is another site out there I can read up on.

Thanks again Dave. I'm sure I'll finish this sometime soon. Once I've got
my lists up and running I have to consolidate them into another file and
create pivot tables and charts from the data (not anticipating too many
problems there - fingers crossed!).

Take care,

Thanks again.

Karen.

"Dave Peterson" wrote:

Debra has more notes...
http://contextures.com/xlDataVal02.html

In fact, Debra's site is a nice spot for lots of things!


KarenF wrote:

Hi Dave,

Hope you had a good weekend. Thought I'd let you know that the vlookup
using the range as you described works perfectly.

Now I'm trying to use my dynamic ranges in dependent data validation (i.e.
using the range "sector" as a drop down in validation to choose a sector of
work, and then I have different ranges for each sector. I then want to base
the choice of job role on the sector that is chosen by making my validation
look to =indirect(A2), where the sector chosen looks at the range name for
the job roles in that sector. At the moment, the dependent list is blank for
some reason). When I have that sorted out, then I'm going to try your macros.

Thanks for your help Dave.

Take care

Karen.

"Dave Peterson" wrote:

Since you know that it's exactly 2 columns wide, I think I'd just bake that into
the formula:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),2)

=counta(lists!$c:$c)
will count the non-empty cells in that column (C). That's why it's important
not to have gaps between headers or within the data.

Once you define the name, you can test it by:
edit|goto
type in that name and see what's selected.

And yep, =counta(lists!$1:$1) will count the number of non-empty cells in row
1. But that doesn't sound like what you really want in this case.



KarenF wrote:

Hi Dave,

Since the list I wish to use as a vlookup table is in columns C and D of a
sheet called Lists, should the formula be:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),COUNTA( Lists!$1:$1))

(the COUNTA(Lists!$1:$1) - does that look to define the region -i.e. look
for a completely blank column and end the range there? )

or would I need to define both columns as in:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$D),COUNTA( Lists!$1:$1))

I think I'm getting there - I would certainly still be stuck without your
help.

This is what I was using.

=OFFSET(Lists!$C$1:$D$1,0,0,COUNTA(Lists!$C:$D),1)

- you could say I missed a bit!

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

I think you may have made a mistake when you defined the range.

This kind of formula worked ok for me when I defined the name.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

And I could use it like:
=vlookup(a1,mynamehere,2,false)

As long as the range had 2 columns and at least one row <bg.

Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic

KarenF wrote:

Thanks Dave.

I have noticed that I can't use a 2 column dynamic range as a lookup - or at
least, not the way I'm doing it! Perhaps I should try and put the "offset"
formula in instead of the dynamic range name. Anyway, I'll give this a good
go tomorrow. Thanks for the advice - you've been great.

Have a good weekend.

Karen.

"Dave Peterson" wrote:

Make sure you make the name a workbook level name.

Just in case you need some notes:
http://www.contextures.com/xlDataVal01.html#Name
(from Debra Dalgleish's site)

KarenF wrote:

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing
the master file to inform the team). The team require that anyone can update
the master list file, and their individual files be based on the list. Any
ideas would be greatly appreciated.

Thanks very much.

Karen

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Now there's a challenge! Now this will be my first venture into VB. I've
always wanted to get to know it a bit better - so here goes!

At least I now know why I was getting so frustrated with the dependent
validation! I'll give this a go. I'll let you know how I get on. I'm
hoping to set the worksheets that use these lists as datalists so they expand
and copy the formulae down, and hopefully make it easier to consolidate in
the long run. Failing this, it'll be back to ranges (which I know can expand
as long as the relevant option is ticked in Tools, Options (which it is)).
As long as the data can be successfully interrogated at the end, it'll be
great.

Thanks again for your expertise. I've ordered myself a book on VBA - it's
arriving around 9 August. That'll be a good read!

Take care Dave

Karen.

"Dave Peterson" wrote:

Oooh.

You're right about the =indirect() with the dynamic names. I had forgotten that
(it's been awhile!).

If the lists only change when you're doing the import--the user or you don't
change them, maybe you could apply the names to the ranges after you paste the
cells into that existing worksheet.

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False

'now apply the names

with thisworkbook.worksheets(mstrnameswksname)
.range("A1",.cells(.rows.count,"A").end(xlup)).nam e = "List1"
.range("b1:c" & .cells(.rows.count,"B").end(xlup).row).name = "lookup1"
'etc
end with

End Sub

(watch for typos)

If the lists are much more dynamic, you could use some type of worksheet_change
event to reset the names.


KarenF wrote:

Hi Dave,

yes, I have read these (I've bookmarked the home page for ease). I'm trying
to do as it suggests but failing for some reason. It has worked in past
spreadsheets for me, but not this time. I'm wondering (as Debra's example
does not include dynamic ranges) whether or not this will work if the
dependent range is a dyanmic one? If in a cell I type =rangename and
autofill, then I do see the content of the cells in the dependent names, but
not in the data validation list. I don't suppose you've any idea what I
could be doing wrong, or if there is another site out there I can read up on.

Thanks again Dave. I'm sure I'll finish this sometime soon. Once I've got
my lists up and running I have to consolidate them into another file and
create pivot tables and charts from the data (not anticipating too many
problems there - fingers crossed!).

Take care,

Thanks again.

Karen.

"Dave Peterson" wrote:

Debra has more notes...
http://contextures.com/xlDataVal02.html

In fact, Debra's site is a nice spot for lots of things!


KarenF wrote:

Hi Dave,

Hope you had a good weekend. Thought I'd let you know that the vlookup
using the range as you described works perfectly.

Now I'm trying to use my dynamic ranges in dependent data validation (i.e.
using the range "sector" as a drop down in validation to choose a sector of
work, and then I have different ranges for each sector. I then want to base
the choice of job role on the sector that is chosen by making my validation
look to =indirect(A2), where the sector chosen looks at the range name for
the job roles in that sector. At the moment, the dependent list is blank for
some reason). When I have that sorted out, then I'm going to try your macros.

Thanks for your help Dave.

Take care

Karen.

"Dave Peterson" wrote:

Since you know that it's exactly 2 columns wide, I think I'd just bake that into
the formula:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),2)

=counta(lists!$c:$c)
will count the non-empty cells in that column (C). That's why it's important
not to have gaps between headers or within the data.

Once you define the name, you can test it by:
edit|goto
type in that name and see what's selected.

And yep, =counta(lists!$1:$1) will count the number of non-empty cells in row
1. But that doesn't sound like what you really want in this case.



KarenF wrote:

Hi Dave,

Since the list I wish to use as a vlookup table is in columns C and D of a
sheet called Lists, should the formula be:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),COUNTA( Lists!$1:$1))

(the COUNTA(Lists!$1:$1) - does that look to define the region -i.e. look
for a completely blank column and end the range there? )

or would I need to define both columns as in:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$D),COUNTA( Lists!$1:$1))

I think I'm getting there - I would certainly still be stuck without your
help.

This is what I was using.

=OFFSET(Lists!$C$1:$D$1,0,0,COUNTA(Lists!$C:$D),1)

- you could say I missed a bit!

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

I think you may have made a mistake when you defined the range.

This kind of formula worked ok for me when I defined the name.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

And I could use it like:
=vlookup(a1,mynamehere,2,false)

As long as the range had 2 columns and at least one row <bg.

Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic

KarenF wrote:

Thanks Dave.

I have noticed that I can't use a 2 column dynamic range as a lookup - or at
least, not the way I'm doing it! Perhaps I should try and put the "offset"
formula in instead of the dynamic range name. Anyway, I'll give this a good
go tomorrow. Thanks for the advice - you've been great.

Have a good weekend.

Karen.

"Dave Peterson" wrote:

Make sure you make the name a workbook level name.

Just in case you need some notes:
http://www.contextures.com/xlDataVal01.html#Name
(from Debra Dalgleish's site)

KarenF wrote:

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing

Good luck and post back (in a new thread) when/if you get stuck.

There are lots of people hanging around that can help.

KarenF wrote:

Now there's a challenge! Now this will be my first venture into VB. I've
always wanted to get to know it a bit better - so here goes!

At least I now know why I was getting so frustrated with the dependent
validation! I'll give this a go. I'll let you know how I get on. I'm
hoping to set the worksheets that use these lists as datalists so they expand
and copy the formulae down, and hopefully make it easier to consolidate in
the long run. Failing this, it'll be back to ranges (which I know can expand
as long as the relevant option is ticked in Tools, Options (which it is)).
As long as the data can be successfully interrogated at the end, it'll be
great.

Thanks again for your expertise. I've ordered myself a book on VBA - it's
arriving around 9 August. That'll be a good read!

Take care Dave

Karen.

"Dave Peterson" wrote:

Oooh.

You're right about the =indirect() with the dynamic names. I had forgotten that
(it's been awhile!).

If the lists only change when you're doing the import--the user or you don't
change them, maybe you could apply the names to the ranges after you paste the
cells into that existing worksheet.

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False

'now apply the names

with thisworkbook.worksheets(mstrnameswksname)
.range("A1",.cells(.rows.count,"A").end(xlup)).nam e = "List1"
.range("b1:c" & .cells(.rows.count,"B").end(xlup).row).name = "lookup1"
'etc
end with

End Sub

(watch for typos)

If the lists are much more dynamic, you could use some type of worksheet_change
event to reset the names.


KarenF wrote:

Hi Dave,

yes, I have read these (I've bookmarked the home page for ease). I'm trying
to do as it suggests but failing for some reason. It has worked in past
spreadsheets for me, but not this time. I'm wondering (as Debra's example
does not include dynamic ranges) whether or not this will work if the
dependent range is a dyanmic one? If in a cell I type =rangename and
autofill, then I do see the content of the cells in the dependent names, but
not in the data validation list. I don't suppose you've any idea what I
could be doing wrong, or if there is another site out there I can read up on.

Thanks again Dave. I'm sure I'll finish this sometime soon. Once I've got
my lists up and running I have to consolidate them into another file and
create pivot tables and charts from the data (not anticipating too many
problems there - fingers crossed!).

Take care,

Thanks again.

Karen.

"Dave Peterson" wrote:

Debra has more notes...
http://contextures.com/xlDataVal02.html

In fact, Debra's site is a nice spot for lots of things!


KarenF wrote:

Hi Dave,

Hope you had a good weekend. Thought I'd let you know that the vlookup
using the range as you described works perfectly.

Now I'm trying to use my dynamic ranges in dependent data validation (i.e.
using the range "sector" as a drop down in validation to choose a sector of
work, and then I have different ranges for each sector. I then want to base
the choice of job role on the sector that is chosen by making my validation
look to =indirect(A2), where the sector chosen looks at the range name for
the job roles in that sector. At the moment, the dependent list is blank for
some reason). When I have that sorted out, then I'm going to try your macros.

Thanks for your help Dave.

Take care

Karen.

"Dave Peterson" wrote:

Since you know that it's exactly 2 columns wide, I think I'd just bake that into
the formula:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),2)

=counta(lists!$c:$c)
will count the non-empty cells in that column (C). That's why it's important
not to have gaps between headers or within the data.

Once you define the name, you can test it by:
edit|goto
type in that name and see what's selected.

And yep, =counta(lists!$1:$1) will count the number of non-empty cells in row
1. But that doesn't sound like what you really want in this case.



KarenF wrote:

Hi Dave,

Since the list I wish to use as a vlookup table is in columns C and D of a
sheet called Lists, should the formula be:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$C),COUNTA( Lists!$1:$1))

(the COUNTA(Lists!$1:$1) - does that look to define the region -i.e. look
for a completely blank column and end the range there? )

or would I need to define both columns as in:

=OFFSET(Lists!$C$1,0,0,COUNTA(Lists!$C:$D),COUNTA( Lists!$1:$1))

I think I'm getting there - I would certainly still be stuck without your
help.

This is what I was using.

=OFFSET(Lists!$C$1:$D$1,0,0,COUNTA(Lists!$C:$D),1)

- you could say I missed a bit!

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

I think you may have made a mistake when you defined the range.

This kind of formula worked ok for me when I defined the name.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

And I could use it like:
=vlookup(a1,mynamehere,2,false)

As long as the range had 2 columns and at least one row <bg.

Debra Dalgleish has some more notes:
http://contextures.com/xlNames01.html#Dynamic

KarenF wrote:

Thanks Dave.

I have noticed that I can't use a 2 column dynamic range as a lookup - or at
least, not the way I'm doing it! Perhaps I should try and put the "offset"
formula in instead of the dynamic range name. Anyway, I'll give this a good
go tomorrow. Thanks for the advice - you've been great.

Have a good weekend.

Karen.

"Dave Peterson" wrote:

Make sure you make the name a workbook level name.

Just in case you need some notes:
http://www.contextures.com/xlDataVal01.html#Name
(from Debra Dalgleish's site)

KarenF wrote:

Hi Dave,

Thanks for your reply. I'm going to give it a try. I've been having some
difficulties using named ranges on one sheet as validation for another sheet
- although I have done this on other pcs, mine has decided not to play and
tells me it can't use a range from another sheet or workbook. Anyway, I'm
going to start again, using your advice, and I'll let you know how I get on.

Thanks again.

Take care,

Karen

"Dave Peterson" wrote:

The workbook doesn't have to be in XLStart for this kind of macro to run.

(Files in the XLStart folder will open each time xl starts--this is nice for
generic routines that you want available for every workbook. That's where lots
of people put their personal.xls for this purpose.)

You could name the macro Auto_Open (in a General module) that would run when
that specific workbook is opened.

I think I'd do some setup work first.

Say your names are in a worksheet called "MyNames" in workbookA.xls.

I'd create a worksheet named "MyNames" in each of the workbooks that I need.
I'd set up the dynamic names for this worksheet/workbook (call it
workbookB.xls), too.

Then each time workbook2.xls opens, it opens workbookA.xls, copies the data and
pastes into that myNames worksheet. Then it closes workbookA.xls.

Kind of...

Option Explicit
Sub auto_open()

Dim MstrNamesWkbk As Workbook
Dim MstrNamesWkbkName As String
Dim MstrNamesWksName As String

MstrNamesWkbkName = "C:\my documents\excel\workbooka.xls"
MstrNamesWksName = "myNames"

Set MstrNamesWkbk = Workbooks.Open(Filename:=MstrNamesWkbkName, _
ReadOnly:=True)

MstrNamesWkbk.Worksheets(MstrNamesWksName).Cells.C opy _
Destination:=ThisWorkbook.Worksheets(MstrNamesWksN ame).Range("a1")

MstrNamesWkbk.Close savechanges:=False
End Sub

By doing the setup with the current data in each of the workbooks, I don't have
to worry about the names not being there.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


KarenF wrote:

Hi Dave,

Thanks for your prompt response. I've already created the lists as per
Debra Dalgleish's instructions. I think your idea of copying and pasting the
lists into each workbook when the workbook opens is a good one. Could this
be automated? I've read about macros(and maybe some VB code which I'm not
very familiar with) which run when a workbook is opened, but this requires
that the workbook containing the macrobe situated in an xlstart folder .
However, this is impractical for the team who will be working on these files.
I could record and run a macro which does this - this was my original
intention - but to do it without each team member having to make a conscious
effort to update the list would be a good thing. It also needs to be done in
such a way that more than one person can update their list at the same time.
If I was recording this procedure, do you feel it would be beneficial to open
the master list on a read-only basis (to try and avoid the read only messge
that can appear when an already open workbook is being opened by another
individual. Perhaps I'm making this more complicated than it needs to be.

Thanks for your thoughts. If you have any additional suggestions, it would
be very helpful.

Thanks

Karen.

"Dave Peterson" wrote:

Debra Dalgleish has instructions:
http://contextures.com/xlDataVal05.html

It may be easier to just copy|paste the lists into each workbook when the
workbook opens???

KarenF wrote:

Hi.

I have a spreadsheet with a several dynamic lists (ranges created using the
offset formula.) which I am using for data validation. Several people using
several different files need to be able to reference these lists as their
data is validated by them. For this to work, I know that every individual
needs to have the list file open while they work on their validated sheets.
I had thought about creating a master list file, which, if updated, could be
copied and pasted into a list file for each individual. The individual's
list file would need to open when they open their validated workbook, and
would need to be updated when the master list was updated (I thought about
recording a macro to copy and paste the master lists' data into the
individuals' lists which would be run by each individual when they were
informed that the master file had changed - thus relying on anyone editing

--

Dave Peterson