Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Location
    Indiana, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Data Validation (2000 SR-1)

    I am attempting to follow the instructions in the Help file for using a data list on a different sheet than the active sheet that contains the cells with data validation on them.

    I have named a cell on the active sheet 'ValidData' and entered =[Estimate_Tool.xls]List_Values!$C$2:$C$78 in the named cell. Every time I enter the above equation (as stated in the help topic) the spreadsheet name - [Estimate_Tool.xls] disappears leaving - List_Values!$C$2:$C$78. The result is a #VALUE! in the named cell. I have selected list on the data validation dialog and entered =ValidData in the Source box. Then when clicking on the cell drop down to view the list, all that is displayed is the #VALUE!.

    What am I doing wrong? Is there a way to do data validation on a cell with the data on a different sheet than the active sheet as the help file seems to indicate is possible?

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Data Validation (2000 SR-1)

    Mark

    If your workbook that you are working with is called Estimate_Tool.xls then this would be normal behavior.

    Essentially you are qualifying where Excel gets the data, and if its the same workbook, Excel will know that its in Estimate_Tool.xls.

    Now why are you getting the #Value, are you sure that the data is in the data validation list? Could you upload a copy so that we can test it

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    New Lounger
    Join Date
    Mar 2001
    Location
    Indiana, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2000 SR-1)

    I am attaching a scaled down version of the workbook I am having trouble with. The Cell named ValidData is A1 and the cell with the data validation turned on is cell C6. As you will see, the value displayed in cell A1 and the drop down list for the data validation is #VALUE!

    I appreciate any help you can provide.
    Attached Files Attached Files

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Data Validation (2000 SR-1)

    'ValidData' is supposed to be a defined name - it's not supposed to refer to a spreadsheet range.
    With the sheet where you want the validation to occur as the active sheet, select menu choices Insert | Name | Define, then try again as per the help topic...

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2000 SR-1)

    Changing the definition of the defined name ValidData to the following seems to work:

    <pre>=List_Values!$C$2:$C$8
    </pre>

    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Mar 2001
    Location
    Indiana, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Data Validation (2000 SR-1)

    Thank you for the reply. I was confusing naming a cell and defining a name with the Insert |Name|Define command. Thank you for showing me the error of my ways.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •