How to do automated linear regression equation with multiple varying criteria?

[tvwright]

Hi folks,

So I have used the data analysis toolpak to do seven linear regressions (seven XY combinations) on six different variables, and found the seven y=mx+b equations for the seven regressions that I conducted. I conducted seven different regressions because they were of varying time lags, and used different data points. Needless to say, y=mx+b is the standard linear equation used to predict Y based on X and m (slope) and b (intercept). I created a table storing all of the y's, all of the x's, all of the slopes (m), and all of the intercepts (b) in a worksheet named "data 3" (see attachment) and now I want to create a form where I can just easily run the automatic calculations using the y=mx+b equation based on selecting relevant criteria in a form (or a similar user interface/template). So for instance, I ran a regression between CCI (consumer confidence index) and Case-Schiller national home price index at time lag 0. IN this example, X=CCI and Y=Case Shiller national home price index. I want to be able to simply select "CCI & Case Shiller National Home price index" on the attached form (see attachment), and have the linear equation automatically calculate what the predicted Case Shiller national home price index is. Further, I want to be able to change the X or Y to any of the six different variables and have the equation automatically re-calculate using the correct x, y, m & b values that are stored in the table on worksheet named "data 3".

Please see the attached form to get a better understanding of what I am looking for, and I'd like to discuss ideas on what ya'll think would be the best approach to achieve this goal.

Form.xlsx

I am most definitely open to restructuring my data or changing the form completely to better accomplish this task. I am not an expert level excel user, and I am wanting to learn how to use it. I would like to learn VBA coding, but practice makes perfect and so if ya'll have VBA ideas on how to accomplish this then I would love to discuss and learn!

Thank you so much!

Look forward to hearing from any of you for ideas or input. Also please ask questions if this is unclear. Thank you!

[tvwright]

OK just an update... I am getting it to work on my own without any help so far. I will post a new link to my updated workbook in a bit just in case any one else wants to pitch in.