I have to chart progress on 40 programs for several children and each program has it's own chart. To avoid opening 40 charts for each child and entering data manually in each chart, I was hoping to be able to create a master sheet for each child where I can enter all the data into the master sheet, then link the cells to feed the data into the respective individual charts. My issue is that when I either try to drag or copy/paste the formula down the column, the reference cell for the master sheet has to be changed manually to enter the next line of data in the individual chart. How can I get the linked formula to automatically change the cell location instead of entering each formula manually? The data have to match the dates. I can learn new formulas, etc. but haven't done much with macros, pivotal tables or vba. Thank you!
Screenshots:
Master spreadsheetScreen Shot 2014-02-15 at 4.23.06 PM.jpg One of 40 individual charts Screen Shot 2014-02-15 at 4.22.46 PM.jpg