Please help with code to recalc when selecting different she

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)

[pilot]

Meant to post above under my username.

[pilot]

Requesting assistance, one more try. Any takers?

[pilot]

Last try, can anyone help with this?

[Chris Davison]

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

[Jack in the UK]

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??

[Chris Davison]

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 !

[Aladin Akyurek]

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

[Jack in the UK]

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.