Excel is having a tough time shifting from one tab to another and
recalculating the page. If however I clikc F9 the tab is fine. Is
there anyway to make it when clicking upon the tabs that they refresh
automatically? I was considering a macro that would refresh every 15
seconds or something, but I thought I'd ask this first. I'd
appreaciate help with either, thanks.


--
MACRE0
------------------------------------------------------------------------
MACRE0's Profile: http://www.excelforum.com/member.php...o&userid=10848
View this thread: http://www.excelforum.com/showthread...hreadid=478527

MACRE0

Not sure why it won't calculate but you could force it by placing code in
the Workbook_SheetActivate event like so

Private Sub Workbook_SheetActivate(ByVal sh As Object)
sh.Calculate
End Sub

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"MACRE0" wrote in
message ...

Excel is having a tough time shifting from one tab to another and
recalculating the page. If however I clikc F9 the tab is fine. Is
there anyway to make it when clicking upon the tabs that they refresh
automatically? I was considering a macro that would refresh every 15
seconds or something, but I thought I'd ask this first. I'd
appreaciate help with either, thanks.


--
MACRE0
------------------------------------------------------------------------
MACRE0's Profile:
http://www.excelforum.com/member.php...o&userid=10848
View this thread: http://www.excelforum.com/showthread...hreadid=478527

While that's exactly the type of thing that I'm looking for, it doens't
seem to work in this case. If you'd be willing, I'd happily send you
the speadsheet so you can see exactly what I mean about excel not
caculating the tab until some other action is performed.


--
MACRE0
------------------------------------------------------------------------
MACRE0's Profile: http://www.excelforum.com/member.php...o&userid=10848
View this thread: http://www.excelforum.com/showthread...hreadid=478527

MACRE0

Try

Application.CalculateFull

In place of sh.calculate

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"MACRE0" wrote in
message ...

While that's exactly the type of thing that I'm looking for, it doens't
seem to work in this case. If you'd be willing, I'd happily send you
the speadsheet so you can see exactly what I mean about excel not
caculating the tab until some other action is performed.


--
MACRE0
------------------------------------------------------------------------
MACRE0's Profile:
http://www.excelforum.com/member.php...o&userid=10848
View this thread: http://www.excelforum.com/showthread...hreadid=478527

No :~( but I've discovered the root of the problem. I am basing
several lookup formulas on a truncated down version of the
cell("filename") to get the tab name. Is there better way of
referencing the tab with a formula than this?
{=MID(CELL(“filename”),FIND(“]“,CELL(“filename”))+255)}
Though the tabs of course remain static, the cell in the sheet states
the previous tab's name until recalced. Perhaps if I change this
formula, which all the lookups are based upon, a sheet refresh will no
longer be required.


--
MACRE0
------------------------------------------------------------------------
MACRE0's Profile: http://www.excelforum.com/member.php...o&userid=10848
View this thread: http://www.excelforum.com/showthread...hreadid=478527

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Any cell address like A1 will anchor the formula to the worksheet.


Gord Dibben Excel MVP

On Sun, 23 Oct 2005 18:57:13 -0500, MACRE0
wrote:


No :~( but I've discovered the root of the problem. I am basing
several lookup formulas on a truncated down version of the
cell("filename") to get the tab name. Is there better way of
referencing the tab with a formula than this?
{=MID(CELL(“filename”),FIND(“]“,CELL(“filename”))+255)}
Though the tabs of course remain static, the cell in the sheet states
the previous tab's name until recalced. Perhaps if I change this
formula, which all the lookups are based upon, a sheet refresh will no
longer be required.

Hi,
I may not understand what you are doing but this will give you the tab
name
in whatever cell you put the formula in.

Why do you have array brackets on your formula?

Try this:
'=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

Don't change the A1 except:
To refer to another sheet, replace A1 in formula with SheetName!A1.

Dave
MACRE0 Wrote:
No :~( but I've discovered the root of the problem. I am basing
several lookup formulas on a truncated down version of the
cell("filename") to get the tab name. Is there better way of
referencing the tab with a formula than this?
{=MID(CELL(“filename”),FIND(“]“,CELL(“filename”))+255)}
Though the tabs of course remain static, the cell in the sheet states
the previous tab's name until recalced. Perhaps if I change this
formula, which all the lookups are based upon, a sheet refresh will no
longer be required.


--
Piranha
------------------------------------------------------------------------
Piranha's Profile: http://www.excelforum.com/member.php...o&userid=20435
View this thread: http://www.excelforum.com/showthread...hreadid=478527

TY, that lack of an anchor really did me in. Shame on me for trying to
cut corners.
No longer does my active sheet retain the prior tab's just viewed
information.


--
MACRE0
------------------------------------------------------------------------
MACRE0's Profile: http://www.excelforum.com/member.php...o&userid=10848
View this thread: http://www.excelforum.com/showthread...hreadid=478527