Pasting a volatile formula in Excel 2007
I am pasting the following formula into a sheet - =VLOOKUP(D2,'Lookup current policies.xlsx'!ExpiryDates,2,0). Once I have it in the sheet and location I want, is there any way of Excel knowing what Column/Row it is in automatically and referring to the previous column and current row, as I do not always paste it into row 2, but it is always pasted into column F. I want Excel to determine the row but refer to the column 2 columns to the left. Column D contains a policy number and the lookup file has policy numbers with expiry dates in columns A and B respectively, and the VLOOKUP returns the relevant expiry date.
I have changed the Excel options to use R1C1 reference style but this removes the letters and rows and is not always what I want.
I have figured it out and decided to post this anyway to assist others.
My new formula is as follows: =VLOOKUP(OFFSET(F:F,,-2),'Lookup current policies.xlsx'!ExpiryDates,2,0) and this works exactly as needed even when I copy it down to other rows. Now I do not have to change the row number before copying the formula down.