+ Reply to Thread
Results 1 to 5 of 5

How to bypass/workaround for 255 character formula limit? need help please.

  1. #1
    Registered User
    Join Date
    02-05-2019
    Location
    PH
    MS-Off Ver
    2013
    Posts
    31

    How to bypass/workaround for 255 character formula limit? need help please.

    Hello, new excel user here. Any help is appreciated
    My problem is that I have multiple dependent dropboxes and on the last column I need to autopopulate the cell based on the result of the 2 dropboxes.
    My issue is that the results is in SENTENCES and took up all the 255 formula limit. Here is an example.

    =(IF(AND(A23="RO1_Concept_and_Application_of_Integrated_Ecosystems_Management_IEM",C23="basic"),"Assist in the application of IEM and other related approaches to Natural Resources Management NRM",IF(AND(A23="RO1_Concept_and_Application_of_Integrated_Ecosystems_Management_IEM",C23="intermediate"),"Apply IEM and other related approaches to Natural Resources Management NRM",IF(AND(A23="RO1_Concept_and_Application_of_Integrated_Ecosystems_Management_IEM",C23="advanced"),"Lead the application of IEM and other related approaches to natural resources management NRM in basin watersheds subwatershed protected areas ancestral domains mineral reservations islands others",IF(AND(A23="RO1_Concept_and_Application_of_Integrated_Ecosystems_Management_IEM",C23="superior"),"Formulate and recommends policies, guidelines, procedures and criteria on integrated ecosystem

    that is just 25% of the formula. I have attached the file so any expert can take a look at it.

    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,438

    Re: How to bypass/workaround for 255 character formula limit? need help please.

    your sheet is quite a mess very hard to make heads and tails of it..

    since all the valid options start with a code like "R01 -" or simular you should create a table with in column 1 the codes, in column2 the text and col 3 the combined col1&2

    then in the if you can use LEFT(A23,FIND("-",A23)-2) to find the code where the find function helps to catch both RO1 as RO10 the -2 avoids having space and - in the search text.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    alternatively it is also worth considering building a table with a created combined searchkey of A23 and C23 to lookup the long text in a standardized table.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the outcome of above would be searchkey R01 -Basic so you should have a table that has all possible searchkeys with their applicatle text.


    in both solutions you can keep the dropdownlists as is with the "R01 - long text" only for formula handling you use the referencetables

  3. #3
    Registered User
    Join Date
    02-05-2019
    Location
    PH
    MS-Off Ver
    2013
    Posts
    31

    Re: How to bypass/workaround for 255 character formula limit? need help please.

    Thanks for the reply.
    I have now updated the table and condensed it to only 3 choices. RO1 to RO3. Let's say if it I have now replace it with spaces instead of a dash. It is now RO1_Concept_and_Application_of_Integrated_Ecosystems_Management_IEM instead of the "RO - long text" May I ask what is the correct formula to use instead of
    IF(AND(LEFT(A23,FIND("-",A23)-2),C23="Basic"),"Assist in the application of IEM and other related approaches to Natural Resources Management NRM"

    Please bear with me.
    The thing is for every "RO_1 long text" there is a dropdownbox which is basic, advanced, intermediate and superior that should populate the final column. When another "RO" is selected and another choice is selected on the "target competency dropdown" (basic,advance etc.) a different sentence will autopopulate again. If I can only make the 5th column work (Office goals) I can do everything.

    Thank you.
    Attached Files Attached Files

  4. #4
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,438

    Re: How to bypass/workaround for 255 character formula limit? need help please.

    That makes it a clearer. But I still think you make it to complicated by using very long named ranges and you will keep running into limits, also limits to the human brain to understand long formulas...

    So I simplified the required named ranges and tables to use. Use 3 tables and in essence 3 named ranges to handle all
    see below what I created in the attachement..

    A
    B
    11
    Tabels Description of content
    12
    Tbl_Competencylvl Simple table with 5 competency levels and setup of dependant dropdowns
    13
    Tbl_DevelopmentTarget All development targets followed by a general description fitting the competency level
    14
    Tbl_LearnObj Holds all learning objectives per Dev Target code
    15
    16
    Named Ranges Description of content
    17
    DevCode Finds the code of The Development target in column A
    18
    LearnObjective Finds the available learning objectives for the chosen Target and Comp. Level
    19
    20
    Competency_level Standard dropdown for B19
    21
    None Dependant dropdown for C19 based on choice B19
    22
    Advanced Dependant dropdown for C19 based on choice B19
    23
    Basic Dependant dropdown for C19 based on choice B19
    24
    Intermediate Dependant dropdown for C19 based on choice B19

    The Named Range DevCode holds the LEFT / FIND function I gave earlier, just a convienience thing, to use in other named range instead of left/find combi.

    Also I created some dependant dropdown lists for the Target Compentency level (col C)
    my reasoning is if you start at Basic the target level should be higher.. so for Basic name range it starts at intermediate.
    I might be off and it might be a Basic-Basic development is possible, then simply add that to the named range list.
    Or also use named range "Competency_level" for col C. But then there is more risk of error if someone select a lower Target then current level

    The most important named range is the LearnObjective I will explain in detail. this is the formula

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    reference____Sheet2! $Q$1___________________________________is the topleft cell of tabel Tbl_Learnobjective
    row__________MATCH(DevCode;Tbl_LearnObj[ Code];0)___________finds the first row with the code of the chosen Development target
    column_______MATCH(ROC!$C$19;Tbl_LearnObj[ #Headers];0)-1___finds the correct column with the appropiate Competency level
    heigth_______COUNTIF(Tbl_LearnObj[ Code];DevCode)___________is sort of a helper column it simply counts the amount of codes in the table Learn objectives.
    width________1____________________________________________Keeps the dropdown list limited to 1 column

    In the Table Tbl_LearnObj you can put as much objectives as you want and a variable number per DevelopmentTarget or target level, simply make sure that each (extra) line you put in holds the code of the Dev Target you want to list out.
    I kept white lines between each DevCode but in the final table you can leave those out, just put them there to make it easier to understand what I did.

    With these 3 basic tables you have maximum flexibility while each change will not cause you to change named ranges.
    If you have new development target you just add it to the Tbl_DevelopmentTarget and fill the columns with appropiate texts. Also fill the tbl_LearnObj with the objectives per competency level
    Even if at some point the competency requirements change you can simply add another row for that DevCode or remove one if it becomes obsolete.


    please look at the attachement to see if this would make a (more) usable structure for you problem.
    In this model also the 255 character limit is solved as per cell you can go to wel over 32000 characters
    Last edited by Roel Jongman; 02-21-2019 at 09:15 AM. Reason: Uploaded 3nd attachement with new version

  5. #5
    Registered User
    Join Date
    02-05-2019
    Location
    PH
    MS-Off Ver
    2013
    Posts
    31

    Re: How to bypass/workaround for 255 character formula limit? need help please.

    Thank you so much! The tables are perfect.

+ 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. [SOLVED] Excel 218 character path limit workaround
    By Jacc in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-08-2017, 09:25 AM
  2. [SOLVED] Ways Bypass 255 Character String Variable Type Limit
    By rkl303 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-26-2015, 01:59 PM
  3. [SOLVED] VBA Workaround for 255 character limit in cell?
    By Bjordion in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-14-2013, 10:25 AM
  4. VBA formulaarray 255 character limit workaround
    By moses67 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-17-2013, 06:41 AM
  5. [SOLVED] Workaround to the 32767 character limit for strings in VBA?
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-05-2013, 06:18 AM
  6. Workaround needed for 255 character find limit
    By NukedWhale in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-27-2009, 04:10 PM
  7. busted workaround for 255 character limit in cells
    By mklapp in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-11-2005, 05:05 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