Excel Complex Calculations to alter some row numbers

Hey All,

I have an Excel spreadsheet, in which resides a very complex set of polynomials we use to calculate engineering values from data we receive from our equipment. These calculations utilize many different cells, and also some coefficient data which resides in an array/table within another worksheet in the same book. The spreadsheets works like this: a technician enters the data from our equipment into one cell, and the output from the calculation is spawned in another, but what I am attempting to do is have a column of data converted. I cannot seem to find a way to enter the first cell of data into the calculation and have the calculation produce the results in another cell, but through the range of the entire column. Basically what I need is cell A1 on Sheet1 to be entered into Cell D1 on the calculation sheet, and have the calculation sheet return the results to cell B1 in Sheet1...............easy enough, but how do I make it so Cells A2, A3, A4, etc, are entered into Cell D1on the calculation sheet with the calculation returning their respective outputs into cells B2, B3, B4, etc...........

Another way around this, that I suppose could work, would be for me to move the calculations into sheet1, and copy the calculation so that each row has it's own, but I have to point the calculations to the correct coefficient value (which will be the same for all rows) and when I do this and try to copy/paste special, Excel sequentially changes the numbers; can I prevent this in any way? An example:

I have this formula in A6:
='COE' !D47+J5*('COE' !D48+J5*('COE' !D49+J5*('COE' !D50)))
When I try to copy/paste special to the next row, I get this:
='COE' !D48+J5*('COE' !D49+J5*('COE' !D50+J5*('COE' !D51)))
I need this:
='COE' !D47+J6*('COE' !D48+J6*('COE' !D49+J6*('COE' !D50)))

So, I need the function to get the data from the same cells on the COE sheet, but sequentially alter the row cell from J5 to J6, J7, J8, etc............


I know this is a mouthful, and there are probably more efficient means to achieving then end results, so I appreciate any input you all may have to offer.

ST