Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: VLOOKUP to external references not updating properly

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My workbook contains a number of sheets, each with many formulas including two that reference another file with a VLOOKUP. When I change to a different sheet, the two cells whose formulas include VLOOKUP continue to display the result from the previously selected sheet until a recalc occurs. I can force an update by F9 or by entering something in another cell which triggers the recalc.

    Should the two cells in question update when the sheet they're on is selected? Or must I add some code to force a recalc when a new sheet is selected?

    This probably has something to do with the two formulas using the sheet name to determine where the VLOOKUP occurs. I'll post the formulas below. Maybe there's a better way to do the same thing without creating the problem described above.

    =RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename")))&" "&(VLOOKUP(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))),'C:1JFHR[Employee info.xls]Summary'!$A$3:$B$25,2,FALSE))

    =VLOOKUP(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))),'C:1JFHR[Employee info.xls]Summary'!$A$3:$D$25,4,FALSE)

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    341
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Bumping this to the top of the list one time in hopes of response.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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