+ Reply to Thread
Results 1 to 7 of 7

Data validation "list" - "source" has too many characters

  1. #1
    Registered User
    Join Date
    12-10-2014
    Location
    Czech Republic
    MS-Off Ver
    365
    Posts
    23

    Data validation "list" - "source" has too many characters

    I have a problem with the data validation. When I set it to "list" and in the "source" space I put a formula, then it is working only till a limit is reached. Is there a way to make it working with a longer formula then is fitting in the source space?

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Data validation "list" - "source" has too many characters

    The hard limit on the List function is 255 characters. What formula are you using, how are you use it? Perhaps it could be truncated.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    12-10-2014
    Location
    Czech Republic
    MS-Off Ver
    365
    Posts
    23

    Re: Data validation "list" - "source" has too many characters

    Hi, thanks for your time. In my project I have 7 sheets, in which there are 50 options (A3-A52). That is working fine. But now I need to add two more sheets, and that is too much:

    sheet 1 = Kuřecí
    sheet 2 = Vepřové
    sheet 3 = Hovězí
    sheet 4 = Ryby
    sheet 5 = Bezmasé
    sheet 6 = Sladké
    sheet 7 = Saláty

    =IF($B$13="Kuř";Kuřecí!$A$3:$A$52;IF($B$13="Vepř";Vepřové!$A$3:$A$52;IF($B$13="Hov";Hovězí!$A$3:$A$52;IF($B$13="Ryby";Ryby!$A$3:$A$52;IF($B$13="Bezmasé";Bezmasé!$A$3:$A$52;IF($B$13="Sladké";Sladké!$A$3:$A$52;IF($B$13="Saláty";Saláty!$A$3:$A$52)))))))

    So in B13 I choose the sheet, and then I can choose in B14 one of the 50 options from that sheet. To add sheet 8 and sheet 9 is too much for the formula. Do you see a way to shorten this formula?

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Data validation "list" - "source" has too many characters

    Ahh, I see. We can definitely consolidate your dropdown by using INDIRECT and VLOOKUP.

    Somewhere on another tab store the translations. I named that sheet Reference.

    A2: Kuř
    A3: Vepř

    B2: Kuřecí
    B3: Vepřové

    etc

    Your validation formula:

    =INDIRECT(VLOOKUP(B13,Reference!$A$2:$B$8,2,0)&"!$A$3:$A$52")

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Data validation "list" - "source" has too many characters

    And here's an example I put together.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-10-2014
    Location
    Czech Republic
    MS-Off Ver
    365
    Posts
    23

    Re: Data validation "list" - "source" has too many characters

    Hi Daffodil, thanks a lot for the formula and example. I had some problems getting it work, and I found that the sheets can't be named with two words, with a "one-word name" it's all works well. Thanks a lot for your help!!
    Last edited by bee88; 01-27-2015 at 03:26 AM. Reason: solved it

  7. #7
    Registered User
    Join Date
    08-25-2014
    Location
    Middlesbrough
    MS-Off Ver
    2010
    Posts
    9

    Re: Data validation "list" - "source" has too many characters

    Hi Daffodil,

    Thanks your example, it has help here with the same problem!

+ 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. Replies: 5
    Last Post: 01-31-2013, 01:03 PM
  2. [SOLVED] Data validation: allow entry into a cell if other three cells have "X", "Y" and "Z"?
    By RogerRangeRover in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-05-2013, 04:49 AM
  3. Using "if" for data validation list source
    By jay7227021 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-17-2012, 03:34 PM
  4. [SOLVED] Data Validation: Allow "List" or "Whole number"
    By monir in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-31-2006, 11:40 AM

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