Pasting a volatile formula in Excel 2007

Topic

New Reply

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.

Reply | Quote

Replies

Shouldn’t you refer to a single cell, not the whole column

=VLOOKUP(OFFSET(F2,0,-2),’Lookup current policies.xlsx’!ExpiryDates,2,0)

Reply | Quote

Bob,

I deliberately did not use F2 as I am not always pasting into row 2, by using the column my lookup worked perfectly.

Regards,
Maria

Reply | Quote

or =VLOOKUP(INDIRECT(“RC[-2]”,0),’Lookup current policies.xlsx’!ExpiryDates,2,0)

Reply | Quote

Rory,

Thanks, your formula worked slightly quicker than mine. I had thought about using INDIRECT, but could not remember how to set the reference.

Regards,
Maria

Reply | Quote