+ Reply to Thread
Results 1 to 6 of 6

HELP! can I use offset fctn in data validation to reference a seperate worksheet?

  1. 09-20-2013, 01:43 PM #1
    MikeOy
    MikeOy is offline
    Registered User
    Join Date
    09-17-2013
    Location
    Oregon
    MS-Off Ver
    MS Office 2003, MS Office 2013
    Posts
    6

    HELP! can I use offset fctn in data validation to reference a seperate worksheet?

    i have created a list of names to use in data validation drop down lists. I used the method found here "http://www.bettersolutions.com/excel/EGH188/YI535920882.htm" to prevent duplicates which works perfectly. I want my drop down lists to be on a seprate sheet from where my list of names is stored. but then i run into a problem whith this source =OFFSET(LISTS!$E$2,0,0,COUNTA(LISTS!$E$2:$E$86)-COUNTBLANK(LISTS!$E$2:$E$86),1) .. excel says i can't use references to other worksheets for data validation criteria. is there anyway to get around this?
    Reply With QuoteRegister To Reply
  2. 09-20-2013, 01:47 PM #2
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: HELP! can I use offset fctn in data validation to reference a seperate worksheet?

    What version of Excel does this have to work in?

    You CAN NOT directly refer to other sheets in Excel 2007 and earlier.

    You CAN directly refer to other sheets in Excel 2010 and later.

    Also, what type of data makes up your list? Is it text? Numeric? Could be both? Something else?
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  3. 09-20-2013, 01:54 PM #3
    MikeOy
    MikeOy is offline
    Registered User
    Join Date
    09-17-2013
    Location
    Oregon
    MS-Off Ver
    MS Office 2003, MS Office 2013
    Posts
    6

    Re: HELP! can I use offset fctn in data validation to reference a seperate worksheet?

    currently using 2007.. the lists are just made up of text, names of people, and vehicle ID numbers

    Thank you for your quick response
    Last edited by MikeOy; 09-20-2013 at 01:58 PM.
    Reply With QuoteRegister To Reply
  4. 09-20-2013, 02:26 PM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: HELP! can I use offset fctn in data validation to reference a seperate worksheet?

    If using Excel 2007 then you CAN NOT directly refer to another sheet in data validation.

    So, give the formula a defined name.

    Goto the Formulas tab>Define Name

    Name: MyList (or whatever you want to name it)
    Refers to:

    =OFFSET(LISTS!$E$2,0,0,COUNTA(LISTS!$E$2:$E$86)-COUNTBLANK(LISTS!$E$2:$E$86),1)

    OK out

    Then, setup the data validation and as the source for the list use:

    =MyList
    Reply With QuoteRegister To Reply
  5. 09-20-2013, 02:34 PM #5
    MikeOy
    MikeOy is offline
    Registered User
    Join Date
    09-17-2013
    Location
    Oregon
    MS-Off Ver
    MS Office 2003, MS Office 2013
    Posts
    6

    Re: HELP! can I use offset fctn in data validation to reference a seperate worksheet?

    BRILLIANT! very simple solution, thank you for the help!
    Reply With QuoteRegister To Reply
  6. 09-20-2013, 02:44 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: HELP! can I use offset fctn in data validation to reference a seperate worksheet?

    You're welcome. Thanks for the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. OFFSET function - column reference over the edge of the worksheet
    By ronaldev in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2013, 10:09 PM
  2. Is there a way to enter a date in a seperate cell just by using data validation?
    By Danny1992 in forum Excel General
    Replies: 6
    Last Post: 08-19-2013, 01:00 PM
  3. [SOLVED] Indirect Data Validation with Auto Populate of Seperate Values
    By twopmjunction in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-24-2013, 04:37 AM
  4. Creating reference to array which has range defined in a seperate worksheet
    By cdrwolfe in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-28-2008, 09:02 PM
  5. [SOLVED] Can the offset worksheet function reference another worksheet
    By AlistairJ in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-09-2005, 02:06 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1