+ Reply to Thread
Results 1 to 3 of 3

Replace Fixed Reference in 0000's of VLOOKUPs

  1. 11-23-2009, 03:34 PM #1
    2709236
    2709236 is offline
    Forum Contributor
    Join Date
    02-25-2008
    Location
    Windsor, ON Canada
    MS-Off Ver
    Excel 2007, 2003
    Posts
    119

    Replace Fixed Reference in 0000's of VLOOKUPs

    Is there a more efficient way that I can swap out an ending fixed reference value in thousands of vlookup formulas across many pages within the same workbook?

    For example, let's say one of my vlookup formulas is:

    =vlookup(A2,'Apples!'$A$2:$W$47,7,false)

    and I want to change the range to 'Apples!'$A$2:$W$200, across several variations of the same formula, across several pages within the same workbook. Can this be done easily without tackling each sheet separately using copy, paste special, formula?
    Last edited by 2709236; 11-23-2009 at 04:04 PM.
    Reply With QuoteRegister To Reply
  2. 11-23-2009, 04:00 PM #2
    ChemistB
    ChemistB is offline
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,582

    Re: Replace Fixed Reference in 0000's of VLOOKUPs

    CNTRL +H to bring up Replace window change options to search entire workbook and "Find What" $W$47
    Replace with: $W$200
    Does that work for you?
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  3. 11-23-2009, 04:03 PM #3
    2709236
    2709236 is offline
    Forum Contributor
    Join Date
    02-25-2008
    Location
    Windsor, ON Canada
    MS-Off Ver
    Excel 2007, 2003
    Posts
    119

    Re: Replace Fixed Reference in 0000's of VLOOKUPs

    OMG. . . I think that worked. Stupid me, I didn't think that you could use the "Find/Replace" for formulas. You have NO idea how much time you just saved me!!!! Thank you, thank you!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1