Conditional Drop Down / Auto Fill Option - is this possible?

I will do my best to describe this.

There are two tabs I am working with.

1) Data Source Tab
2) Competitor Auto Fill

On the Data Source tab there is a column that has a drop down selection, this column feeds into another column on the Competitor Auto Fill tab.

Once on the Competitor Auto Fill tab, I am wanting to match the Auto-populated field with the header to the far right.

If the header matches, I would like the list to auto-populate as a drop down selection to choose from.

Sample xls is attached.

Watch out for discrepancies in data titles between your two tabs.
For example, Hydraulic IND vs Industry HYD.
Another, example: Fluid connectors is double spaced in I9 of Market Share by Competitor tab, but single spaced in your manually entered list in data validation in D24 of Source Data Tab.
To keep that from happening, use a range in data validation instead, such as ='Market Share by Competitor'!$G$9:$J$9. And be careful with merged cells, cause when you select that range, it comes $G$9:$J$11, as rows 9, 10 and 11 and columns I and J are merged.
If your Excel version does not permit data validation from another worksheet, google for instructions to go around it. Or enter a list of items in current tab.

That noted, I use this formula in C10 of Source Data Tab and copy down:
(Formula deleted due to conflict with forum firewall.)

The height argument of 100 in the formula is the number of rows the OFFSET function covers from selected competitor columns and can be adjusted as desired.
Please run some test in attached file and let us know how it goes.

Good luck!

For the most part this works, the only thing that doesn't seem to work would be now in column I need this to be a drop down selection as the list on the right is just that a list, and when entering in, we will need for the list to appear as a drop down selection so we can choose out of the list the top 5, not just have the list copied.

Does that make sense?

OK, I guess I got it right this time.
I transferred the formula from C10 to a helper column, O9, for example. That column can be hidden if you like. Then I copy down 100 rows.
Then I created a named range Competitors with this formula in the Refers to box:

Formula:

Please Login or Register  to view this content.

Finally, I use data validation in C10, and in the Source box I type:
=Competitors

Please check file attached.

First of all thank you for helping me work through this. I do have a couple of questions.

Is the drop down selection referencing the columns in G, H, I & J at all? As this is part of the issue I am running into.

The header has to pull from the Data Source tab, if the header matches one of the columns in G, H, I or J, then we are needing those specific names to auto populate in the drop down selection for column C?

You're welcome.

Yes, the drop down is set to do that. The formula in helper column O, lists items from selected column in range G9:I9, in the Market Share by Competitor tab, as selected in data validation cell D24 in Source Data Tab.

You can increase that range to accomodate more columns, say to column Z, as required.
The last portion would be like this:
MATCH('Source Data Tab'!$D$24,$G$9:$Z$9,0)

I'm back again. I have tried to duplicate your efforts but it seems that I am missing something.
Please see attached and let me know where I went wrong.

Hi.
I have adapted the model to your new file.
Please check attachment.

I'll be at sea with no internet service (satellite antenna down) for the next 6 weeks.
I hope this works for you, otherwise I'll be glad to get back to you when I'm back online.
Take care.