Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    80
    Thanks
    10
    Thanked 3 Times in 3 Posts

    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.
    Maria
    Simmo7
    Victoria, Australia

  2. #2
    New Lounger
    Join Date
    Jul 2012
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Shouldn't you refer to a single cell, not the whole column

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

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,895
    Thanks
    0
    Thanked 85 Times in 81 Posts
    or =VLOOKUP(INDIRECT("RC[-2]",0),'Lookup current policies.xlsx'!ExpiryDates,2,0)
    Regards,
    Rory
    Microsoft MVP - Excel.

  4. #4
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    80
    Thanks
    10
    Thanked 3 Times in 3 Posts
    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
    Maria
    Simmo7
    Victoria, Australia

  5. #5
    Star Lounger
    Join Date
    Feb 2009
    Location
    Kings Park, Victoria, Australia
    Posts
    80
    Thanks
    10
    Thanked 3 Times in 3 Posts
    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
    Maria
    Simmo7
    Victoria, Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •