+ Reply to Thread
Results 1 to 3 of 3

How to use Formula in DataValidation List Source

  1. #1
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Panama City, FL
    MS-Off Ver
    Excel 2016
    Posts
    175

    How to use Formula in DataValidation List Source

    Hi friends,
    I need to use a formula in the Source: of my Data Validation to include the Sheetname for my drop down list.
    The following formula should work if someone can please show me how to properly QUOTE it. I get very confused trying to put quotes within quotes.

    To be clear, the Sheetname that has my data is DataDec2018. The Sheetname I am in currently is Dec2018.

    =OFFSET("Data"&RIGHT(CELL("filename"),7)!$I$2,,,"Data"&RIGHT(CELL("filename"),7)!$F$3)

    This will allow me to have multiple tabs and use the same formula: DataJan2019, DataFeb2019, etc.

    Thanks in advance for any assistance provided.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,701

    Re: How to use Formula in DataValidation List Source

    Your quotes are OK. The problem is that you can't use a string that gives a sheet name, and use it as if it were an actual sheet name. The good news is that you can use INDIRECT to achieve that, but everything has to be in quotes, including the range references:

    =OFFSET(INDIRECT("Data"&RIGHT(CELL("filename"),7)&"!$I$2"),,,INDIRECT("Data"&RIGHT(CELL("filename"),7)&"!$F$3"))

    I can't test this without setting up a file that would be the same as yours. If it doesn't work, please attach a sample file.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Forum Contributor
    Join Date
    06-08-2012
    Location
    Panama City, FL
    MS-Off Ver
    Excel 2016
    Posts
    175

    Re: How to use Formula in DataValidation List Source

    Works like a charm!
    Thank you so much for the help and the clear explanation.
    Have a good weekend.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Vba to connect 3 macros to loop through datavalidation list
    By joezzz in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-10-2018, 10:38 PM
  2. [SOLVED] Datavalidation custom list
    By Jacc in forum Excel General
    Replies: 3
    Last Post: 02-28-2017, 07:10 AM
  3. [SOLVED] DataValidation Source box with formula invalidates "List"
    By njs27 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-06-2014, 12:46 AM
  4. ADD VALUES AT TOP OF THE List in datavalidation cell
    By kumari in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-19-2013, 07:03 AM
  5. [SOLVED] How to use VBA so that a list box, not DataValidation pops up
    By marksm33 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-23-2013, 02:05 PM
  6. Display ChartObject from a datavalidation list
    By bktong in forum Excel General
    Replies: 1
    Last Post: 02-05-2011, 06:34 AM
  7. problem with Formating the DataValidation List
    By appunu in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-31-2010, 06:13 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1