Using INDEX to create a Dynamic Drop Down List

**waytoskinny** · 07-10-2024, 03:45 PM

Hello everyone,

This is my first post here, and i'm urging for any help to this situation.

(Just to be clear, I can't use INDIRECT on my data validation, because the value i'm trying to find is automatically assigned from another page)

Ok, so this is the case, I have a long spreadsheet, and i'm stuck trying to create a DropDown list, from a value that i've pulled from another sheet, this value is one of many headers from a Table, now I need to create the DropDown list from that value column so I can select another value.

I'm using this formula =INDEX((Table3[Cleaning],Table3[SteelWorker],Table3[Carpinter],Table3[Electrician]),,,J6), ***take into account that this formula will be used in a DataValidation formula using a Named Formula.

But the thing is that anyway I put it, it always returns with a #VALUE error.

Any help at all will be hugely appreciated.

Thanks

**TMS** · 07-10-2024, 04:00 PM

Welcome to the forum

.

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

**waytoskinny** · 07-10-2024, 05:35 PM

Ok, ill see if these images are clear for the explanation:

This first image represents the formula in column K, which takes reference from column J (this value is pulled from another sheet)
Screenshot 1.png

This second image shows the dropdown list info I need displayed in column K from the first image, where "Electricista" is the reference for column D in this image
Screenshot 2.png

Thanks in advance

**AliGW** · 07-11-2024, 01:15 AM

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

**waytoskinny** · 07-11-2024, 10:43 AM

So very sorry about that, I'll try to read more carefully, the forums suggestions.

I made this Excel Worksheet example of the INDEX problem i'm facing, hopefully, this way someone might the answer to the problem

Thanks in advance

**TMS** · 07-11-2024, 12:31 PM

Not 100% sure what you want. But maybe this, copied across and down:

Formula:

Please Login or Register  to view this content.

Or, if you have TRANSPOSE, this copied down:

Formula:

Please Login or Register  to view this content.

**waytoskinny** · 07-11-2024, 02:33 PM

Thanks a lot for the reply TMS,

But that's not exactly what i'm looking for, I'm trying to make a dynamic dependant drop down list using index. To be a little bit clearer, I can't use Data Validation in Column 2 (sheet 1) because that information is extracted from sheet 2, so I can't do the dependant drop down list traditionally using the INDIRECT function in data validation.

I'm still not allowed to paste links, cause I'm new here, but I found a video of exactly what I need to do, using the INDEX function.

The problem is that the formula is not giving me the results in my worksheet. Or if anyone has any other ideas of how can I achieve this, i'm all for trying it out.

I tried using the UNIQUE function as well, but could not get anywhere with it.

**TMS** · 07-11-2024, 02:40 PM

Ah, I think I misunderstood. Maybe try this:

Formula:

Please Login or Register  to view this content.

**TMS** · 07-11-2024, 02:41 PM

If you have tried using UNIQUE, you are probably not using Excel 2016. Please update your profile.

**waytoskinny** · 07-11-2024, 03:00 PM

It is almost almost what I need, the only thing is that I need it to be a DropDown list, I placed this formula in the Name Manager, but in that sense it returned an error. Any ideas on getting it on a DropDown list now so the item can be selected?

Thanks again

With regards to Excel 2016, I tried it on another computer, to see if I needed to upgrade or something.

**TMS** · 07-11-2024, 03:18 PM

Or maybe this:

Formula:

Please Login or Register  to view this content.

**waytoskinny** · 07-11-2024, 03:29 PM

Each new Formula gets a little bit closer, just tested out that last one, it did let me show the DropDown menu, but the only item that was pulled from the data was the first item on the list.

**TMS** · 07-11-2024, 08:22 PM

Please see the updated example workbook.

**waytoskinny** · 07-11-2024, 10:22 PM

Wow, thanks TMS! I was going around in circles and the thing that I never thought of was to give the headers a Table name as well. Thanks a lot!

This problem is solved.

**AliGW** · 07-12-2024, 12:53 AM

However, you have failed to update your forum profile - please do so without delay. Thanks.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

**TMS** · 07-12-2024, 04:24 AM

Originally Posted by waytoskinny

Wow, thanks TMS! I was going around in circles and the thing that I never thought of was to give the headers a Table name as well. Thanks a lot!

This problem is solved.

To be honest, giving the headers a name adds very little; it is not a Table, it is just a Named Range.

What was more important was to convert the original Table1 back to a range and then create separate Tables for each function.

As AliGW (the Moderator) has asked, please update your profile. It affects what solutions can and will be offered.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

**waytoskinny** · 07-12-2024, 08:27 AM

Ok, i'll get to it.

Thanks for all your help.

**TMS** · 07-12-2024, 09:04 AM

You're welcome. Thanks for the rep.

Using INDEX to create a Dynamic Drop Down List

LinkBack

Thread Tools

Rate This Thread

Display

Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Re: Using INDEX to create a Dynamic Drop Down List

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Trying to create a dynamic table so I can create a updating drop down list.

How to create dynamic drop down list

Create dynamic drop down list from dynamic data source

Create a Dynamic Drop Down List

create dynamic drop down list for large list of data

Create a dependent drop down list with dynamic list

Way to create a dynamic drop down list.

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions