Results 1 to 2 of 2

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

Threaded View

  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!

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