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