+ Reply to Thread
Results 1 to 2 of 2

Data Validation Formula needed to fill sequence by dragging the selection.

  1. #1
    Registered User
    Join Date
    11-29-2022
    Location
    Amsterdam
    MS-Off Ver
    16.65 (22091101)
    Posts
    1

    Question Data Validation Formula needed to fill sequence by dragging the selection.

    Hi there,

    If the user selects a specific Product category in column F through a dropdown menu from list '.'!$B$2:$B$9. (data validation formula: ='.'!$B$2:$B$10).

    Then Attribute name 1-20 will be prefilled with information of tab '.'. The attributes are linked to a specific product category. This overview can be found on tab '.'. The following formula is used:
    =ALS('3. Product Attributes'!$F9='.'!$B$2;'.'!$B$15;ALS($F9='.'!$B$3;'.'!$B$45;ALS($F9='.'!$B$4;'.'!$B$75;ALS($F9='.'!$B$5;'.'!$B$105;ALS($F9='.'!$B$6;'.'!$B$135;ALS($F9='.'!$B$7;'.'!$B$165;ALS($F9='.'!$B$8;'.'!$B$195;ALS($F9='.'!$B$9;'.'!$B$225;0))))))))

    Then for each Attribute value a specific dropdown menu should be available to the user. This overview can be found on tab '.'. I have made the following data validation formula: =ALS(F9='.'!B2;'.'!B16:B44; ALS(F9='.'!B3;'.'!B46:B74; ALS(F9='.'!B4;'.'!B76:B104; ALS(F9='.'!B5;'.'!B106:B134; ALS(F9='.'!B6;'.'!B136:B164; ALS(F9='.'!B7;'.'!B166:B194; ALS(F9='.'!B8;'.'!B196:B224; ALS(F9='.'!B9;'.'!B226:B254;0))))))))

    This formula works. However, I am not able to fill the sequence by dragging the selection. This needs to be done for 4000 cells. Therefore, I tried the following data validation formula:
    =ALS(F9='.'!$B$2;'.'!B$16:$B$44; ALS(F9='.'!$B$3;'.'!B$46:B$74; ALS(F9='.'!$B$4;'.'!B$76:B$104; ALS(F9='.'!$B$5;'.'!B$106:B$134; ALS(F9='.'!$B$6;'.'!B$136:B$164; ALS(F9='.'!$B$7;'.'!B$166:B$194; ALS(F9='.'!$B$8;'.'!B$196:B$224; ALS(F9='.'!$B$9;'.'!B$226:B$254;0))))))))

    Unfortunately, this formula is too long, since it exceeds the limitation of characters. I am also not able to fill the sequence by dragging the selection.

    How can I solve this?

    Thanks in advance!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Data Validation Formula needed to fill sequence by dragging the selection.

    Hi there.

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

+ 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. dfgdfgdfgght thtrh trhr
    By aslam5420 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-07-2022, 03:32 AM
  2. Replies: 11
    Last Post: 02-19-2017, 06:45 AM
  3. [SOLVED] Dragging down a formula in sequence
    By carmap12 in forum Excel General
    Replies: 3
    Last Post: 05-13-2015, 04:49 AM
  4. Data validation formula : Sequence above
    By Jhon Mustofa in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-19-2014, 04:44 AM
  5. Replies: 4
    Last Post: 08-01-2013, 09:56 AM
  6. Replies: 0
    Last Post: 05-10-2011, 11:05 AM
  7. Auto-Fill selection when using Data Validation?
    By duugg in forum Excel General
    Replies: 2
    Last Post: 07-31-2007, 10:34 AM

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