title: Creating relationship between two different columns- a relationship that effects other values
body: <p>I'm going to try and make this as least confusing as possible, I apologize in advance if my attempt is a failure.</p>
  
  <p>I work in education and am trying to create a predictive analysis document that will tell us how many class sections we will need to offer in a given semester. </p>
  
  <p>I pulled data from the past five years and consolidated it into a pivot chart. I set the pivot chart to combine all courses with a common Subject, Course Title and Catalog Number (See image below for more detail), and output 3 different columns of values based on what we need. </p>
  
  <p>The problem I am facing now is with curriculum changes throughout the years. There are some courses within the list that are no longer being offered and a new course with a new Course Title, Subject and Catalog Number that can now be substituted for the previously needed course. Since the data has been pulled into one pivot chart, both the old curriculum courses and the new curriculum courses are in one list. </p>
  
  <p>I would like to somehow create a relationship between the old curriculum courses and the new curriculum courses. If possible I would like the names of the courses to remain separate, but the values of the old and new to be averaged out together in their respective rows. </p>
  
  <p>In a new page, I plan on putting an easy to use form where the user can select a course subject and name, enter in some other necessary data and the document will output the amount of course sections needed.</p>
  
  <p>Does anyone know of a way to make a relationship between two cells and have other cells effected by this relationship?</p>
  
  <p>Thanks so much!</p>
  
  <p>Mike</p>
  
  <p><a href="http://i.stack.imgur.com/CYjrv.png" rel="nofollow">enter image description here</a></p>