Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Please help with code to recalc when selecting different she

  1. #1
    Guest

    Default

    I have no doubt several on this forum could propose a better solution to the problem stated below, but if not, I need to add code that triggers a recalc when a different sheet is selected.

    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:\1JF\HR\[Employee info.xls]Summary'!$A$3:$B$25,2,FALSE))

    =VLOOKUP(RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("]",CELL("filename"))),'C:\1JF\HR\[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

    Meant to post above under my username.

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

    Default

    Requesting assistance, one more try. Any takers?

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

    Default

    Last try, can anyone help with this?

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Pilot,

    hopefully someone more knowledgeable can add to this, but I think it has something to do with the fact that your =cell"filename" function isn't anchored.......

    when I use that to pop the filename on my printouts, I always anchor it thusly :

    =cell("filename",aa9999)

    I seem to remember reading on this board that the cell reference (aa9999) anchors it to the active sheet or something

    I've seen a co-worker do the same but miss out the last bit and he then got printouts with all the same sheet name on them, sort of defeating the object of the function

    lots of replies will say use a1 or $a$1 to anchor, but I find I often delete the first row or column for whatever reason...so the aa9999 reference just takes care of my penchant for deleting....

    Hope this helps, albeit very late

    Chris

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris

    BTW

    Anchors???

    I always fell top left A1
    better than bot right IV666536 as will effect size of sheet proformanc eand search

    erh! i getting a twitch already

    why delet row 1 whats it done to you, remove that and they all might fall off!

    I NEVER delete A col or row 1 as they are my dunmping grounds....

    idea??

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-19 16:10, Jack in the UK wrote:
    ... as will effect size of sheet proformanc eand search

    erh! i getting a twitch already

    why delet row 1 whats it done to you, remove that and they all might fall off!

    I NEVER delete A col or row 1 as they are my dunmping grounds....

    idea??

    Hmmmm, good point Jack.... I hadn't thought about the effects on filesize and search time.... maybe I'll slim it down to "Z99" or something.

    reason, I often delete row 1 through the design process and as I expand the worksheet (say, formula hiding and subtotals etc etc)

    ditto with the first column - I usually leave column A as a border (have a look at the F1 prediction spreadsheet) there's nothing in it...it's a border ! sometimes I delete it if I've run out of printing space at 100%

    just the way I design things, I guess !



  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,645
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    Chris,

    Try

    =CELL("filename",INDIRECT("A1"))

    to circumvent the touble ensuing of your habit of "deleting column A," entered of course somewhere else than in A1 itself.

    BTW, that usage of "anchor", I'm afraid, points to me.

    Aladin


  9. #9
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris

    Your way or my way hows the best?

    I dont know or care! Its a care of awairness i learn for you and i pray to everyone they learn just the smallisty snip of what i have to offer, then i truly be happy i speak from the heart and heart on sleave, what else fro Simple South London boy!

    No be seriouse your work i have seen iand is PERFECTION and very well designed, and we all can learn lots from you, i just happen yo have a passionate love affair with row 1 and so she loves me and she stay, the way i work?? It matter not a drop how or why.. but i take on board your commenst and i to use cells as boarders in colour.

    I will tell you secret, F1 predictions are causing me agros EVERYONE wants it and i work for softwaRE DESIGN COMPAMY!

    ether way.. good points.

    Aladin is 100% if a minimun and so ancors is his way, if we follow iom sure we wil be just abvout 100% if we hit a few problems along the way.. Again my way is to lock the minimun and roll what i can.. why??? I dont know just my way,

    ALL in all a very intresting post feed this as we have opened honest open comments and all seem to agree we all are best and all have a cool way to work, and each others is just as cool and just as best ...

    No wonder i love excel....... cheers guys.
    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

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
  •