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

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

On 13 Nov., 00:38, marc747 wrote:
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

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



On Nov 12, 3:45*pm, Per Jessen wrote:
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

On 13 Nov., 00:38, marc747 wrote:



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- Hide quoted text -

- Show quoted text -

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

On 13 Nov., 00:53, marc747 wrote:
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

On Nov 12, 3:45*pm, Per Jessen wrote:



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

On 13 Nov., 00:38, marc747 wrote:

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- Hide quoted text -

- Show quoted text -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

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



On Nov 12, 4:40*pm, Per Jessen wrote:
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

On 13 Nov., 00:53, marc747 wrote:



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

On Nov 12, 3:45*pm, Per Jessen wrote:

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

On 13 Nov., 00:38, marc747 wrote:

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- Hide quoted text -

- Show quoted text -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Hide quoted text -

- Show quoted text -

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


On Fri, 13 Nov 2009 09:06:24 -0800 (PST), marc747
wrote:

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



On Nov 12, 4:40*pm, Per Jessen wrote:
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

On 13 Nov., 00:53, marc747 wrote:



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

On Nov 12, 3:45*pm, Per Jessen wrote:

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

On 13 Nov., 00:38, marc747 wrote:

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- Hide quoted text -

- Show quoted text -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Hide quoted text -

- Show quoted text -

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




On Nov 13, 9:30*am, Gord Dibben <gorddibbATshawDOTca wrote:
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

On Fri, 13 Nov 2009 09:06:24 -0800 (PST), marc747
wrote:



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

On Nov 12, 4:40*pm, Per Jessen wrote:
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

On 13 Nov., 00:53, marc747 wrote:

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

On Nov 12, 3:45*pm, Per Jessen wrote:

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

On 13 Nov., 00:38, marc747 wrote:

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- Hide quoted text -

- Show quoted text -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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


On Fri, 13 Nov 2009 11:14:54 -0800 (PST), marc747
wrote:

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




On Nov 13, 9:30*am, Gord Dibben <gorddibbATshawDOTca wrote:
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

On Fri, 13 Nov 2009 09:06:24 -0800 (PST), marc747
wrote:



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

On Nov 12, 4:40*pm, Per Jessen wrote:
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

On 13 Nov., 00:53, marc747 wrote:

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

On Nov 12, 3:45*pm, Per Jessen wrote:

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

On 13 Nov., 00:38, marc747 wrote:

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- Hide quoted text -

- Show quoted text -- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -