How to apply the formula on other dropboxes based on a formula on another dropbox

[g0dmenuelz]

I would really appreciate any help.

On my table, the combination of dropdown in A19 and C19 should create another dropdown at D19 based on the result.
But Both A19 with C19 will auto-populate C20 as well.

Since I am using very long name ranges we created a table instead to avoid the 255 character limit.
Everything is perfect except that I need to apply the A19+ C19 on D20 as well.

A really good person helped me with the the table.

This seems to be the updated formula to apply but it seems not to be working.

=OFFSET(Sheet2!$Q$1;MATCH(LEFT(ROC!$A19;FIND(" -";ROC!$A19)-1);Tbl_LearnObj[Code];0);MATCH(ROC!$C$19;Tbl_LearnObj[#Headers];0)-1;COUNTIF(Tbl_LearnObj[Code];DevCode);1)

Can any expert here take a look at the table and show me how to make make the D20 work.

Thank you and more power to you all.

[FDibbins]

Yikes, all those merged cells just make me cringe!! WE all advocate to avoid using merged cells if at all possible, they cause all sorts of problems with formulas and other aspects in excel. I have even seen them described as the devils work lol.

is there way way you could normalize that sheet and then let us try again?

[g0dmenuelz]

Thanks for the reply.
One issue I encountered is that I ran into the 255 character limit because of very long name ranges. A good person in the forums help me create the table as a workaround. Since all options start with a code like RO1 or similar he said we can create a table with in column 1 the codes, in column2 the text and col 3 the combined col1&2. He says the The Named Range DevCode holds the Left / Find function.

These are the name ranges and how it links together.



Tabels Description of content

Tbl_Competencylvl Simple table with 5 competency levels and setup of dependant dropdowns

Tbl_DevelopmentTarget All development targets followed by a general description fitting the competency level

Tbl_LearnObj Holds all learning objectives per Dev Target code


Named Ranges Description of content

DevCode Finds the code of The Development target in column A

LearnObjective Finds the available learning objectives for the chosen Target and Comp. Level

Is there any way to make it work on the D20? Or starting from scratch will affect all other formulas?

Please bear with me ....... Thank you.

[FDibbins]

For a start with, your named ranges dont have anything to do with all those merged cells.

Also, those range names could be shortened considerably by using understandable abbreviations, instead of full words
eg
RO2_Identification_of_interventions_and_integrating_strategies_across_sectors_forestry_coastal_agriculture_urban_air_space_thru_consensusadvanced
could become
RO2_ID_interventions_adv
etc
There is no need to have the whole thing spelled out

Now, can you do anything about those merged cells?

[g0dmenuelz]

Hi,

Just an update another expert was able to make it work perfectly.
Just in case someone will encounter this issue in the future,
Here is the guide he gave me. For each 2nd dropdown of a dev target you will create a separate named range.

SO this formula will work only as datavalidation on Cell D21 (and D19 if wanted) after you created it as named range first.
Then after that just replace the Cell values at $A23, $C23 with the correct value.

=OFFSET(Tables!$Q$1;MATCH(LEFT(ROC!$A$23;FIND(" -";ROC!$A$23)-1);Tbl_LearnObj[Code];0);MATCH(ROC!$C$23;Tbl_LearnObj[ #Headers];0)-1;COUNTIF(Tbl_LearnObj[Code];LEFT(ROC!$A$23;FIND(" -";ROC!$A$23)-1));1)

please also note that copying and pasting the formula might change the , to ; due to local settings which gave me an error.

Thanks