|
Validation List
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 |
Validation List
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 |
Validation List
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 - |
Validation List
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 - |
Validation List
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 - |
Validation List
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 - |
Validation List
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 - |
Validation List
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 - |
| All times are GMT +1. The time now is 06:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com