Filling a formula down a column where only one cell value referenced changes?

[takamaz]

I have a workbook where, in Sheet 2, I have a macro setup to copy several columns of data from Sheet 1. I then need to add several more columns of data to that data. The data that will fill in the remaining columns is in Sheet 3, so the formula is this:



=IF(AK3=Sheet2!A1,Sheet2!B1,IF(AK3=Sheet2!A2,Sheet 2!B2,IF(AK3=Sheet2!A3,Sheet2!B3,"")))

Is there a way to easily copy this formula to the rest of the column where it continues to compare the value on Sheet2 in column AK, but pull information from the same places in sheet 3? Does that question make sense? I need the formula in the next row to read:

=IF(AK4=Sheet2!A1,Sheet2!B1,IF(AK4=Sheet2!A2,Sheet 2!B2,IF(AK4=Sheet2!A3,Sheet2!B3,"")))

So that the only things changed is AK3 becomes AK4. This sheet has over 1800 rows, so I don't want to have to manually do this.


----------------------
So, I have found one way to do this, that involves putting the references I am trying to bring into the sheet inside quotes, filling the function down the page, and then use Find & Replace to remove the quotes, but I still think there must be a better way.

Additionally, Maybe there is another way to do this whole operation. I'm essentially wanting to pull column B from sheet 3, when column AK in sheet 2 is equal to column A in sheet 3. Essentially: IF(AK=Sheet3 Column A) then I want Sheet 3 Column B

[NoSparks]

Have you tried using Absolute Cell References where necessary in the formula and dragging or double-clicking the drag handle ?

[takamaz]

I don't have time to try it out this morning, but that looks like exactly what I need. Thanks very much.