Indirect/substitute formula help

    I have been working on improving a data sheet that narrows down choice options but hit a snag.


    The columns so far have been drop downs via indirect substitute formulae that have been dependent on the previous column content. The one that is needed now needs to limit the range of responses available in the drop downs to 2 sets of options depending what is chosen in the previous column. There are a significant number of responses that can go into the previous column (see example attached as column F on the Reports tab - Jobs), and depending on these it needs to allow either the top half of the table in the data tab - showing D2:D17 to show in the drop-downs, or the lower half, showing D18:D23. Column C on the Data tab would be the responses that would show on the Report tab column F.


    This is just a sample sheet and there is significantly more data in the actual sheet that needs to be capture, though only 2 range options.Sample.xlsx


    Cross Post: also posted here https://www.mrexcel.com/board/…ute-formula-help.1129611/

    I've found that the problem usually lies inbetween the keyboard and the chair

    Edited once, last by S O: added cross post ().

    Quote

    The columns so far have been drop downs via indirect substitute formulae that have been dependent on the previous column content.

    I see nothing like that in your sample book. Explain more fully or provide a better example.

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

    The previous columns were only given to show that this was part of a bigger data sheet, each one contains the same formulae e.g. =INDIRECT(SUBSTITUTE(A1," ","_")) which is dependant on the previous column/cell.


    This works fine for a small list of information relative to one option chosen, which in turn has been dependent on the previous options chosen.


    Issue here is this: in the 'Jobs' column (in this example) you will be presented with 22 options in a drop-down, the formula required in column G (levels) needs to provide the options in data from D to G, only relative to what is chosen as the Job i.e. different options would be available for the lower part of the data table.


    I know a solution would be to set these up with individual names ranges and the information below relative to each, however the true data has much longer names and there are also approx 300 names in the list so hoping for a simpler solution.

    I've found that the problem usually lies inbetween the keyboard and the chair

    Your description is still not clear ... so am having to guess.


    Data Validation added which works off a defined name, which does a lookup into Data, returning First Half or Second Half.


    Definition: =INDEX(Data!$J$2:$J$17,MATCH(Report!E2,Data!$I$2:$I$17,0))


    The INDIRECT of that result (substituting " " with "_") gives a drop-down of the first half or the second half of the list.


    The first half and second half are defined name ranges.


    See guess workbook: (attached)
    Sample_guess.xlsx

    Cheers, Glenn.


    Beauty is in the eye of the beer-holder.

    Sample 2.xlsxThank you for this and I can see how this works. Unfortunately it doesn't do what I was looking for but maybe this was due to my explanation.


    I have attached another sample so hopefully this may show better (along with the first).

    In this sample I have just used a list for the drop-downs in column A to speed things up, but this would normally be an indirect substitute, and there would be a number of columns previous to this also that contains options for the user dependent on the previous option chosen.


    Column B here (Role) is similar and dependent on what is chosen in column A.


    Depending on what is chosen in column B then needs to allow a restricted list only to be available, the information is shown in the List tab. So if one set of options are chosen then it would only allow the drop-down to show the choice of ab, cd, de, or fg. If some of the other options are chosen then it only allows a list of hi, jk, lm, or no to be chosen.


    The list of options in column B is quite extensive due to the previous possibilities (approx 250-300) and only approximately a quarter fall into the latter category.

    I've found that the problem usually lies inbetween the keyboard and the chair

    Thank you, this was ideal.......


    Until somebody moved the goalpoasts :(


    It now seems that some of the original choices that were in the 'first half' also need to be included in the 'second half' list and only have those options available (not first half) - as highlighted on the sheet.


    Not sure if would be simpler/possible just to do a list and have one set at the top for all those of one type, and another for the other type, but then they need to have the drop-down options?

    Sample 3.xlsx

    I've found that the problem usually lies inbetween the keyboard and the chair

    In "Report" sheet, select C2 >> Data Validation >>


    >> Allow : List


    >> Source : =OFFSET(IF(MATCH($A2,List!$C$1:$O$1,0)<10,List!$D$14,List!$K$14),,1,,4)


    >> OK


    >> Finish


    then,


    Copied down




    Regards

    Thanks, but I don't think that this would capture those highlighted as in sample 3 which also need to be included with the second half options?

    I've found that the problem usually lies inbetween the keyboard and the chair

    To choose the options in column C in the "Report" sheet if chosen in Column B


    In "Report" sheet, select C2 >> Data Validation >>


    >> Allow : List


    >> Source : =OFFSET(List!$B$19,COUNTIF(List!$C$2:$K$11,$B2)+COUNTIF(List!$L$2:$O$11,$B2)*2,,,COUNTA(CHOOSE(COUNTIF(List!$C$2:$K$11,$B2)+COUNTIF(List!$L$2:$O$11,$B2)*2,First_Half,Second_Half)))


    >> OK


    >> Finish


    then,


    Copied down


    A great lot of functions exist in Microsoft Excel, some being easy-to-understand, other requiring a long learning curve, and the former being used more often than the latter. And yet, Excel INDIRECT is one of the kind. This Excel function does not perform any calculations, nor does it evaluate any conditions or logical tests.


    Well then, what is the INDIRECT function in Excel and what do I use it for? This is a very good question and hopefully you will get a comprehensive answer in a few minutes when you've finished reading this tutorial.


    Excel INDIRECT function - syntax and basic uses

    Excel INDIRECT formula examples

    Creating indirect references from cell values

    References from cell values and text

    INDIRECT with named ranges

    Creating an Excel dynamic reference to another sheet

    Making a dynamic reference to another workbook

    Locking a cell reference

    INDIRECT with other functions (ROW, ADDRESS, VLOOKUP)

    INDIRECT & Excel Data Validation

    Possible errors and issues in INDIRECT formulas

    Excel INDIRECT function - syntax and basic uses

    As its name suggests, Excel INDIRECT is used to indirectly reference cells, ranges, other sheets or workbooks. In other words, the INDIRECT function lets you create an Excel dynamic cell reference or range reference rather than "hard coding" those references into your formulas. As a result, you can change a cell reference within a formula without changing the formula itself. Moreover, these indirect references won't change when some new rows or columns are inserted in the worksheet or when you delete any existing ones.



    https://www.impkeys.com/

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login