copying formula

[oskie89]

I have a formula in Sale #1, N9, N10 and N11 that refers to text in J9, J10 and J11 and in A9, A10 and A11 (text) and E9,
E10 and E11 (numerical). I would like to copy this formula to use the three cells in A column and E column in Sale #2 with the three cells in column J and column N in Sale #2. Is there a way to do this without having to change the cell number for each Sale?

[Richard Buttrey]

Hi,

In N9 copied down
=IF(J9=A$9,E$9,IF(J9=A$10,E$10,IF(J9=A$11,E$11)))

[oskie89]

I tried the formula and it worked on the first three rows (Sale #1). But when I copied to N20 (Sale #2), I need it to enter the data from Sale #2 (A20, A21 and A22 (text) and E20, E21 and E22 (numeric) not the information from Sale #1.

[oskie89]

I used the formula and it worked for Sale #1, however, it did not work for Sale #2. Sale #2 will have different data in the A-E columns that I need to copy into the J-N columns on the the right side of the spreadsheet; and the same for Sale #3 and #4. The left side of the spreadsheet (columns A-E) will change with each sale. The data on the right side (columns J-N) remain the same for each sale. I can use the formula as is and change the cells for each Sale, but I would like to have a formula that will work for any of my spreadsheets of this style. Also, what do I need to add to the formula to leave column N blank if the answer is false? I know that there is a way, I just can't remember it. Thank you for your help so far, and if the only way to do it is to change cell numbers in the formula, then thank you very much and yuour help was greatly appreciated.