Validation List

[marc747]

I have a Workbook that has two worksheets. The Data is on one
worksheet and I would like to have a Validation list in the other
worksheet will get data from the first worksheet, Is this possible, I
tried and I could not go to the other worksheet.

Thanks,
Marc

 

[Per Jessen]

Hi Marc

You have to name the range in first sheet (Insert > Name > Define),
and then you can use the name as reference in the other sheet.

Regards,
Per

 

[marc747]

Hi,

How would you do that. I know the Data in the first worksheet is
A9:A300 this is the data that I want to be in the Validation list in
the other worksheet.

Thanks,
Marc

 

[Per Jessen]

Hi

In first sheet, elect A9:A900, goto Insert > Name > Define >
Name the list MyList

Now goto sheet 2, select the desired cell and goto Validation > List >
Source: =MyList

Notice the equal sign in front of the range name.

Hopes this helps.
....
Per

 

[marc747]

Hi,
Thanks, this was what I was looking for, everything worked but when I
created the "MyList" I seclected the default "Ignore Blank" and when I
go to the Validation it still list the Blank Cells, any idea why or
what I can do so that Blank cell are not listed.

Thanks,
Marc

 

[Gord Dibben]

You misunderstand the purpose of "ignore blanks"

Blank Cells in Source List

If the source list is a named range that contains blank cells, users may be
able to type any entry, without receiving an error message.

I think what you really want is a dynamic named range that will show only
non-blank cells in the range and expand as you add more items.

See Debra Dalgleish's site for creating dynamic ranges.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

 

[marc747]

Thanks, But does not work, maybe I am doing something wrong. This is
the Formula that I have.

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

The cell that I want to start is A10 to all the way down and to show
in the Validation list on Sheet2 every cell that is not Blank.

Thanks,
Marc

 

[Gord Dibben]

Apologies.

I thought the blanks were at the bottom of a defined range you made large
enough to accomodate more entries.

That's why I pointed you to a Dynamic Range.

But you're saying the blanks are interspersed within the list range?

If you cannot remove the blanks from where they are sourced.................

I would autofilter for non-blanks then copy the visible cells to a new sheet
or range where the data would be contiguous.

Use that contiguous range as MyList for the DV source.

As far as the formula goes......to start from A10

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

Note the ,9,0 which is offset number of rows from A1 so list starts at
A10

But if you follow the filter etc. steps you will have no need for the
dynamic range to start at A10


Gord