Dynamic List using INDEX MATCH and maybe OFFSET function

[nostrum]

Hi guys,

I have another issue I am unable to resolve.

Spreadsheet attached. I need to be able to create a dynamic list based on matching data on 2 tables.

So, if the nominal I.D of product 1,2,3,4,5,etc matches the nominal I.D of product a,b,c,d,e, etc, add it to the options column (D:L) in product 1's table. I can get it to match using INDEX MATCH on the first option cell, but I'm struggling to find a way for it to match the next value in the list automatically so that when they list get's updated it dynamically changes.

From there I want to create a data validation list of all options matching the chosen product (product 1,2,3,4,5,etc)

Hope you can help, apprecate any help as always

Thanks in advance

[jason.b75]

Please remember that your thread titles should describe your problem, not your anticipated solution.

Try this in Pipework D3, fill right and down.

Please Login or Register  to view this content.

Your data validation request makes no sense, there are no options for product 1, 2, 3, etc. each of them only appears once. Assuming that you want the options for products a, b, c, etc. instead, then you could use something like

Please Login or Register  to view this content.

You would need to change "Product C" to a reference point of interest. This will leave blank spaces at the end of the list, which could be removed by using offset instead of index. This is something that I try to avoid using unless it is strictly necessary.

[nostrum]

Hi Jason.b75,

OK, taken on board, sorry, still learning the ropes on the rules!

The first equation is perfect, thank you very much for that.

I am getting an error on the data validation though, it thinks it's not a formula?

Much appreciated

[jason.b75]

Oops!

I forgot that you can't use table names in data validation, there is a workaround though.

You can create a named range that refers to the formula, then use the named range to generate the validation list, for example.

Create a new named range by going to Formulas > Name Manager > New.
In the Name box, enter DV_List
In the Refers To box, enter the second formula from post 2.

Then create your data validation list by using =DV_List as the list source.

[nostrum]

Hi Jason.b75,


Thanks very much, that worked a treat!

Now, just before we finish off, I've been able to use the same formula in a similar way somewhere else.

The only problem I have is with the first part of the formula which generates the list in which I can use to create a dropdown.

=IF(Tablepipework[@[Nominal I.D]:[Nominal I.D]]="","",IFERROR(INDEX(TableInsulation[[Name]:[Name]],AGGREGATE(15,6,(ROW(TableInsulation[[Nominal I.D]:[Nominal I.D]])-ROW(TableInsulation[[#Headers],[Nominal I.D]]))/(TableInsulation[[Nominal I.D]:[Nominal I.D]]=Tablepipework[@[Nominal I.D]:[Nominal I.D]]),COLUMNS($D3:D3))),""))

Is there a way to make this criteria within a certain percentage of the resulting cell, as opposed to an exact match? For example A1:A1+10%?

Also, finally, does the name DV_list have any relevence to the equation or can it be anything?

Thanks for the help, really appreciate it

[jason.b75]

You could try

Please Login or Register  to view this content.

Note that the bit in red checks greater or equal to the actual value (100%), the bit in bold checks less than or equal to 110% of the actual value.

The name DV_list was just an example, it can be any valid name.