Dynamic Update of Trend Function Based on Dropdown List

Hello. I am trying to figure out how to do the following in the attached spreadsheet...

As you can see in Cell B2:B11, I am calculating new X values for each of the sets of data in Sheet 2 (Data1, Data2, etc...)

In cell C4, I have a dropdown list that would allow me to choose a data set (column reference). What I am trying to do is make the trend formula dynamic so that when I select the data set in the dropdown and enter an X value into cell A4 I would get the calculated Y value in cell B3.

Please let me know if this makes sense.


Thanks in advance!
Matt

Thanks for the quick reply, Pierre!

Can you point me to where I can find the formula for the dynamic named ranges? I'm not sure exactly how you did this.


Thanks,
Matt

Pierre,

I found the named range formulas. The problem I see with this approach is that it looks like it will only work if I maintain the same naming convention for the columns (Data1, Data2, Data3, etc...) What if the column header became a part number from a supplier that is any number of random characters?

Thanks,
Matt