Need imported data to copy over into another sheet

I want to search for an answer to this question, but don't know how to ask it. I keep writing it all out, trying to simplify it.

I’m using Excel 2010

This is for a babysitting co-op and we are using it to keep track of points earned and used.

I have a form that automatically sends data to a spreadsheet.

Specifically, it sends the data to a sheet called "raw form data".

Another sheet called “point calculations” has a series of calculations that are based on the raw data.

The problem I'm having is that when new data is imported onto the “raw data” sheet, it isn’t transferred to my calculation sheet even though I've pulled down the formulas to row 100 on the second sheet.

After playing with it for a while, I think the problem is that the new data is *inserted* into the data sheet, rather than being filled into the next available row. (I don't think I have a way to demonstrate the data being imported into the spreadsheet from the form) When that happens, the formula references change on my calculation sheet. Excel is trying to help by doing that, but Because of that, the formulas on my second sheet (the calculations) are forever off. Excel, in its infinite wisdom, changes the cell reference for me. I tried changing to absolute references but that didn't work.

Oh I hope that makes sense.

If there is an easier way to ask the question and someone has already answered it, I'm all eyes for the link!

Here is a *very* basic sample of what the sheets would be like.

Thank you so much!

Hi Jenny,

Please refer to attached. The INDEX() formula will automatically return the reference regardless of any inserted rows/columns.

I've also taken the liberty of cleaning up the blank rows at the bottom.

Aha! Thank you so much! I figured there was some sort of function to do this, and I just didn't know what it was. I'm going to dig apart the formula so I make sure I understand how it works. Our actual form reporting and data will be quite a bit more complex.

No problems! =INDEX() basically just looks up a cell address; the rows() bit is just a shortcut instead of entering 2 in row 2, 3 in row 3, etc.

If this has taken care of your problem, please don't forget to mark the thread as [SOLVED]

I'm going to test it in my full spreadsheet in the morning to make sure I understand it and can make it work and then I'll be thrilled to mark it solved!

I'm marking this solved, because it does absolutely do exactly what I was asking! I've spent all morning reading as much as I can about the INDEX function. My brain is not as fast as it used to be and I'm having some trouble with it except in it's simplest form (INDEX(array,row,column). I will be back with new threads (in the appropriate sections, of course!) asking questions about this function. I need to totally understand it because our forms and calculations will definitely be changing as we refine our points system.

Thank you again for your quick help! I am confident I will get this eventually.