Functions - Automatic Update

Hi,

I have a function,

    myfunction( )

which searchs for a string in a specific worksheet

    W

and returns a cell's value

    v

The problem is, when v is changed the cell that uses myfunction( ) is not 
updated automatically.

I know this is the way functions work and maybe I'll need a different 
approach. Hoping I'm clear enough, what to do?

(I may try forcing the worksheet to recalculate (how to do that btw?) but 
looking for a better way.)

Thanks. 


0
serdar
5/8/2006 10:07:25 AM
excel 39879 articles. 2 followers. Follow

8 Replies
496 Views

Similar Articles

[PageSpeed] 27

This is a multi-part message in MIME format.

------=_NextPart_000_007A_01C672A2.115FB070
Content-Type: text/plain;
	charset="iso-8859-9"
Content-Transfer-Encoding: quoted-printable

Use=20

Application.Volatile True

as a first line in your function.

Function MyFunction()
Application.Volatile True
....your code

End Function
--=20
Haldun Alay
  "serdar" <s@s.com>, haber iletisinde =FEunlar=FD =
yazd=FD:eMCX2cocGHA.3348@TK2MSFTNGP03.phx.gbl...
  Hi,

  I have a function,

      myfunction( )

  which searchs for a string in a specific worksheet

      W

  and returns a cell's value

      v

  The problem is, when v is changed the cell that uses myfunction( ) is =
not=20
  updated automatically.

  I know this is the way functions work and maybe I'll need a different=20
  approach. Hoping I'm clear enough, what to do?

  (I may try forcing the worksheet to recalculate (how to do that btw?) =
but=20
  looking for a better way.)

  Thanks.=20


------=_NextPart_000_007A_01C672A2.115FB070
Content-Type: text/html;
	charset="iso-8859-9"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-9">
<META content=3D"MSHTML 6.00.2900.2873" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>Use </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Application.Volatile True</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>as a first line in your =
function.</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Function MyFunction()</FONT></DIV>
<DIV>
<DIV><FONT face=3DArial size=3D2>Application.Volatile True</FONT></DIV>
<DIV><FONT face=3DArial size=3D2>...your code</FONT></DIV></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>End Function<BR>-- <BR>Haldun =
Alay</FONT></DIV>
<BLOCKQUOTE=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
  <DIV><FONT face=3DArial size=3D2>"serdar" &lt;</FONT><A=20
  href=3D"mailto:s@s.com"><FONT face=3DArial =
size=3D2>s@s.com</FONT></A><FONT=20
  face=3DArial size=3D2>&gt;, haber iletisinde =FEunlar=FD=20
  yazd=FD:eMCX2cocGHA.3348@TK2MSFTNGP03.phx.gbl...</FONT></DIV><FONT =
face=3DArial=20
  size=3D2>Hi,<BR><BR>I have a function,<BR><BR>&nbsp;&nbsp;&nbsp; =
myfunction(=20
  )<BR><BR>which searchs for a string in a specific=20
  worksheet<BR><BR>&nbsp;&nbsp;&nbsp; W<BR><BR>and returns a cell's=20
  value<BR><BR>&nbsp;&nbsp;&nbsp; v<BR><BR>The problem is, when v is =
changed the=20
  cell that uses myfunction( ) is not <BR>updated =
automatically.<BR><BR>I know=20
  this is the way functions work and maybe I'll need a different =
<BR>approach.=20
  Hoping I'm clear enough, what to do?<BR><BR>(I may try forcing the =
worksheet=20
  to recalculate (how to do that btw?) but <BR>looking for a better=20
  way.)<BR><BR>Thanks. <BR><BR></FONT></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_007A_01C672A2.115FB070--

0
spam5724 (5)
5/8/2006 10:19:42 AM
Excellent!

(Te�ekk�r ederim :) )

"Haldun Alay" <spam@istemiyorum.ben>, haber iletisinde �unlar� 
yazd�:edQuvjocGHA.1272@TK2MSFTNGP03.phx.gbl...
Use

Application.Volatile True

as a first line in your function.

Function MyFunction()
Application.Volatile True
....your code

End Function
-- 
Haldun Alay
"serdar" <s@s.com>, haber iletisinde �unlar� 
yazd�:eMCX2cocGHA.3348@TK2MSFTNGP03.phx.gbl...
Hi,

I have a function,

    myfunction( )

which searchs for a string in a specific worksheet

    W

and returns a cell's value

    v

The problem is, when v is changed the cell that uses myfunction( ) is not
updated automatically.

I know this is the way functions work and maybe I'll need a different
approach. Hoping I'm clear enough, what to do?

(I may try forcing the worksheet to recalculate (how to do that btw?) but
looking for a better way.)

Thanks. 


0
serdar
5/8/2006 10:45:24 AM
Just to add ...

If you use application.volatile in your code, then your formula will recalculate
when ever excel recalculates.

It might be a better idea to be more specific in your function.

Pass it the range that could change.

function myfunction(rng1 as range, rng2 as range) as variant
....
end function

Now excel knows to reevaluate your function when something in rng1 or rng2
changes.

===
If you decide to use the application.volatile approach, remember to recalculate
before you trust the function result--it could be waiting for the next
calculation and may be incorrect for the current data.

serdar wrote:
> 
> Hi,
> 
> I have a function,
> 
>     myfunction( )
> 
> which searchs for a string in a specific worksheet
> 
>     W
> 
> and returns a cell's value
> 
>     v
> 
> The problem is, when v is changed the cell that uses myfunction( ) is not
> updated automatically.
> 
> I know this is the way functions work and maybe I'll need a different
> approach. Hoping I'm clear enough, what to do?
> 
> (I may try forcing the worksheet to recalculate (how to do that btw?) but
> looking for a better way.)
> 
> Thanks.

-- 

Dave Peterson
0
petersod (12005)
5/8/2006 12:27:47 PM
That's even better, thanks.

"Dave Peterson" <petersod@verizonXSPAM.net>, haber iletisinde sunlari 
yazdi:445F3943.788CB763@verizonXSPAM.net...
> Just to add ...
>
> If you use application.volatile in your code, then your formula will 
> recalculate
> when ever excel recalculates.
>
> It might be a better idea to be more specific in your function.
>
> Pass it the range that could change.
>
> function myfunction(rng1 as range, rng2 as range) as variant
> ...
> end function
>
> Now excel knows to reevaluate your function when something in rng1 or rng2
> changes.
>
> ===
> If you decide to use the application.volatile approach, remember to 
> recalculate
> before you trust the function result--it could be waiting for the next
> calculation and may be incorrect for the current data.
>
> serdar wrote:
>>
>> Hi,
>>
>> I have a function,
>>
>>     myfunction( )
>>
>> which searchs for a string in a specific worksheet
>>
>>     W
>>
>> and returns a cell's value
>>
>>     v
>>
>> The problem is, when v is changed the cell that uses myfunction( ) is not
>> updated automatically.
>>
>> I know this is the way functions work and maybe I'll need a different
>> approach. Hoping I'm clear enough, what to do?
>>
>> (I may try forcing the worksheet to recalculate (how to do that btw?) but
>> looking for a better way.)
>>
>> Thanks.
>
> -- 
>
> Dave Peterson 


0
serdar
5/8/2006 3:49:52 PM
I used a different approach, let me know what do you think.

Since the constants are in a seperate worksheet (W) and nearly all 
worksheets use the data in W;

Private Sub Worksheet_Change(ByVal Target As Range)

Application.CalculateFull

End Sub



"Dave Peterson" <petersod@verizonXSPAM.net>, haber iletisinde sunlari 
yazdi:445F3943.788CB763@verizonXSPAM.net...
> Just to add ...
>
> If you use application.volatile in your code, then your formula will 
> recalculate
> when ever excel recalculates.
>
> It might be a better idea to be more specific in your function.
>
> Pass it the range that could change.
>
> function myfunction(rng1 as range, rng2 as range) as variant
> ...
> end function
>
> Now excel knows to reevaluate your function when something in rng1 or rng2
> changes.
>
> ===
> If you decide to use the application.volatile approach, remember to 
> recalculate
> before you trust the function result--it could be waiting for the next
> calculation and may be incorrect for the current data.
>
> serdar wrote:
>>
>> Hi,
>>
>> I have a function,
>>
>>     myfunction( )
>>
>> which searchs for a string in a specific worksheet
>>
>>     W
>>
>> and returns a cell's value
>>
>>     v
>>
>> The problem is, when v is changed the cell that uses myfunction( ) is not
>> updated automatically.
>>
>> I know this is the way functions work and maybe I'll need a different
>> approach. Hoping I'm clear enough, what to do?
>>
>> (I may try forcing the worksheet to recalculate (how to do that btw?) but
>> looking for a better way.)
>>
>> Thanks.
>
> -- 
>
> Dave Peterson 


0
serdar
5/8/2006 4:10:15 PM
I'd rather pass it the info the function needs.

If cells change as the result of formulas, then the worksheet_change event won't
fire.

And I would assume that most changes made to the worksheet really wouldn't cause
your function to recalculate.

And sometimes running macros (even event macros) will kill the edit|undo stack. 
(This didn't happen in a small test of your code in xl2003 for me, though.)



serdar wrote:
> 
> I used a different approach, let me know what do you think.
> 
> Since the constants are in a seperate worksheet (W) and nearly all
> worksheets use the data in W;
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
> Application.CalculateFull
> 
> End Sub
> 
> "Dave Peterson" <petersod@verizonXSPAM.net>, haber iletisinde sunlari
> yazdi:445F3943.788CB763@verizonXSPAM.net...
> > Just to add ...
> >
> > If you use application.volatile in your code, then your formula will
> > recalculate
> > when ever excel recalculates.
> >
> > It might be a better idea to be more specific in your function.
> >
> > Pass it the range that could change.
> >
> > function myfunction(rng1 as range, rng2 as range) as variant
> > ...
> > end function
> >
> > Now excel knows to reevaluate your function when something in rng1 or rng2
> > changes.
> >
> > ===
> > If you decide to use the application.volatile approach, remember to
> > recalculate
> > before you trust the function result--it could be waiting for the next
> > calculation and may be incorrect for the current data.
> >
> > serdar wrote:
> >>
> >> Hi,
> >>
> >> I have a function,
> >>
> >>     myfunction( )
> >>
> >> which searchs for a string in a specific worksheet
> >>
> >>     W
> >>
> >> and returns a cell's value
> >>
> >>     v
> >>
> >> The problem is, when v is changed the cell that uses myfunction( ) is not
> >> updated automatically.
> >>
> >> I know this is the way functions work and maybe I'll need a different
> >> approach. Hoping I'm clear enough, what to do?
> >>
> >> (I may try forcing the worksheet to recalculate (how to do that btw?) but
> >> looking for a better way.)
> >>
> >> Thanks.
> >
> > --
> >
> > Dave Peterson

-- 

Dave Peterson
0
petersod (12005)
5/8/2006 5:50:28 PM
You're right, and passing the range is a brilliant way to solve my problem. 
My only concern is that the function will be used by a novice user and,

    myfunction(string1 as string, range1 as range)

    =myfunction(A1;'worksheet_name'!D:D)

would not be clean enough for him to use, than simply

    =myfunction(A1)


It is even hard for him to understand the purpose of the semicolon or the 
option to use a string instead of a cell reference as

    =myfunction('value of A1 here')

Anyway, this is the level of the user, but as I type this, I decided to use 
the straight forward way you suggested. Finally, yes the undo stack was a 
problem for me in the past as well, good point again!

I really appreciate your help,

Thanks.



"Dave Peterson" <petersod@verizonXSPAM.net>, haber iletisinde sunlari 
yazdi:445F84E4.B668B075@verizonXSPAM.net...
> I'd rather pass it the info the function needs.
>
> If cells change as the result of formulas, then the worksheet_change event 
> won't
> fire.
> And I would assume that most changes made to the worksheet really wouldn't 
> cause
> your function to recalculate.
> And sometimes running macros (even event macros) will kill the edit|undo 
> stack.
> (This didn't happen in a small test of your code in xl2003 for me, 
> though.)


0
serdar
5/8/2006 7:42:40 PM
If you changed any cell in 'worksheet_name'!d:d, then you'd want your function
to recalc.  It sounds to me to be more of a training issue.

Personally, I think you can either treat your users as people who are unwilling
to learn (and they won't!) or you could tell them what's expected.

You may be pleasantly surprised when they learn something from your function and
can implement it in another function (like =vlookup()).

Don't sell them short.

serdar wrote:
> 
> You're right, and passing the range is a brilliant way to solve my problem.
> My only concern is that the function will be used by a novice user and,
> 
>     myfunction(string1 as string, range1 as range)
> 
>     =myfunction(A1;'worksheet_name'!D:D)
> 
> would not be clean enough for him to use, than simply
> 
>     =myfunction(A1)
> 
> It is even hard for him to understand the purpose of the semicolon or the
> option to use a string instead of a cell reference as
> 
>     =myfunction('value of A1 here')
> 
> Anyway, this is the level of the user, but as I type this, I decided to use
> the straight forward way you suggested. Finally, yes the undo stack was a
> problem for me in the past as well, good point again!
> 
> I really appreciate your help,
> 
> Thanks.
> 
> "Dave Peterson" <petersod@verizonXSPAM.net>, haber iletisinde sunlari
> yazdi:445F84E4.B668B075@verizonXSPAM.net...
> > I'd rather pass it the info the function needs.
> >
> > If cells change as the result of formulas, then the worksheet_change event
> > won't
> > fire.
> > And I would assume that most changes made to the worksheet really wouldn't
> > cause
> > your function to recalculate.
> > And sometimes running macros (even event macros) will kill the edit|undo
> > stack.
> > (This didn't happen in a small test of your code in xl2003 for me,
> > though.)

-- 

Dave Peterson
0
petersod (12005)
5/8/2006 10:02:52 PM
Reply:

Similar Artilces:

Try on the correction update
--rxvxblmp Content-Type: multipart/related; boundary="iaolrohdbstd"; type="multipart/alternative" --iaolrohdbstd Content-Type: multipart/alternative; boundary="mviesyha" --mviesyha Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "November 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to help maintain the security of your computer from these vulnerabilities, ...

How to get Outlook to open automatically when computer loads up?
How can I get Outlook to open up automatically when I turn on my computer? On Sun, 6 Sep 2009 12:41:01 -0700, hgonzale <hgonzale@discussions.microsoft.com> wrote: > How can I get Outlook to open up automatically when I turn on my computer? This is not really an Outlook question. You get any program or programs to start automatically when you boot the computer and you can do each of them the same way. The easiest way to do this is simply to put a shortcut to each program you want to start automatically in the Startup folder: C:\Documents and Settings\your user name\Start Menu\Pro...

how do i recovery a document that has been updated & resaved?
I completed a worksheet and when I was asked if i wanted to save changes, i answered yes. Well now my worksheet is a blank worksheet! Help! Are you sure it's blank? Maybe you did your work on one sheet, then accidentally selected a blank sheet after you saved. Click on each of the sheet tabs (Sheet1, Sheet2, Sheet3) at the bottom of the Excel window, and see if your data is on one of those. Also, check that your sheet isn't hidden: choose Format>Sheet>Unhide. But, if the workbook really is blank, you'll have to recreate it. Dottie@HBC wrote: > I completed a wor...

Automatic Send and Receive
I just installed Office 2003. I cannot get Outlook 2003 to do a "Automatic Send and Receive" when I first get on- line. I always had to click on "Send and Receive" to get email. Any thoughts. Start by configuring an automatic Send/Receive interval in Tools > Send/Receive > Send/Receive Settings... -- Russ Valentine [MVP-Outlook] "Mark D" <anonymous@discussions.microsoft.com> wrote in message news:086b01c3fafd$79a5e150$a401280a@phx.gbl... > I just installed Office 2003. I cannot get Outlook 2003 to > do a "Automatic Send and Receive"...

Excel merged cell
I have merged two cells and want it to expand (get bigger) as more text is typed or added. I'm able to do this with just a single cell (row autofit, wrap text), but when I merge two or more cells, these options no longer work. I don't think you can and-in my opinion-it's just another reason to stay away from merged cells. -- Regards; Rob ------------------------------------------------------------------------ "Joanne (JAS)" <Joanne (JAS)@discussions.microsoft.com> wrote in message news:58E1D6FD-3FEB-4C2B-B66C-8363091AA39C@microsoft.com... > I have merged tw...

ADOBE AFTER EFFECTS PRO V6.5 and tutorials, After Effects Plugins Collection (WINMAC), updated 28/Aug/2005
ADOBE AFTER EFFECTS PRO V6.5 and tutorials, After Effects Plugins Collection (WINMAC), updated 28/Aug/2005 buy, risk-free purchase, working, tested, fully functional, very cheap discounted price, low cost, quality OEM software, ------------------------------------------------------------- Adobe After Effects Pro 6.5 CD NR 15 752 IMAGE LIBRARY VIDEO TEXTURE EFFECTS (c) ADOBE CD NR 13 867 TOTAL TRAINING WHATS NEW IN AFTER EFFECTS 6.5 PART2 [3 CDs] CD NR 16 036 2005/08/14 Total.Training.Adobe.After.Effects.6.5.Pro-The.Fundamentals.I&II.DVDrip 4CD 2005/08/14 Total.Training.Adobe.After.E...

Today Function
I created a calendar using conditional formatting that runs with the today function. Past days are red, current day black and future days green. Can I create a web page that still utilizes the today function. Sure. for Excel 2002 and up Open a fresh clean worksheet and in cell A1 enter: =TODAY() Then: File > Save as WebPage The result is an .htm file that can be directly opened with IE or Excel. The result of today() will appear in both applications. -- Gary''s Student - gsnu200768 "stevestrib" wrote: > I created a calendar using conditional formatting that...

how to automatically update a cell value?
I have a data table in Excel as follows: Month Percent Jan 2% Feb 7% Mar 4% Apr May Jun I would like to have a separate cell (i.e. Cell X) that will automatically update with the latest value from the data column. In the above example, the Cell X value would be 4%, but if I add additional data to the column, such as 9% in April, the Cell X value would show 9%. How do I create a formula for this? One way, in cell x put =INDEX(B:B,MATCH(9.99999999999999E+307,B:B)) where column B holds the percentage, format x as percentage -- Regards, Peo Sjoblom (No private emails please, for e...

send mail automatically off server
Hi everyone, I have a server that runs some tasks at certain times during the month. These tasks create a log file which I would like to email to my mailbox automatically. I noticed that you can run outlook through a command line: outlook.exe /a c:\temp\log.txt this will start outlook with the file attached but it does not send the email and does not fill out the To: is there any way to do this through a command line? Thanks in advance for any help, Constance ...

IMFv2 filter update failing on install
I received the update in my Microsoft Update this morning, but when i try to install it, it fails with this error: Error Code: 0x80244022 Try to install the update again, or request help from one of the following resources. This is a windows exchange 2003 standard server with sp2 running on a windows 2003 standard server with sp1. It's a front end server (which handles my smtp, passing everything back to my BE server, which is an enterprise exchange 2003 and enterprise windows 2003 server). I have the IMF turned on, the registry entries have been created. I did use to use IM...

lookup function 06-02-10
I have a column listing numbers in worksheet B. I'm looking for a formula to place in worksheet A that will list the bottom number listed in that worksheet B column. Any help would be much appreciated. Try the below..to retieve the last number =LOOKUP(10^10,Sheet2!A:A) -- Jacob (MVP - Excel) "Keith" wrote: > I have a column listing numbers in worksheet B. I'm looking for a formula > to place in worksheet A that will list the bottom number listed in that > worksheet B column. > > Any help would be much appreciated. > ...

Offline Address Book not updating.
MY Offline Address Book has stopped updating. I have installed nothing new on this server, except Windows Updates. This server is not an AD server and only functions as an Exchange 2003 Enterprise server. When I go into the ESM, then go to Offline Address Lists, right click on my Offline Address Book, then select Rebuild, I get a message that pops up stating: "MAPI or an unspecified service provider ID no: 00000000-0000-00000000 ID no: c1050000 Exchange System Manager" On Mon, 30 Jan 2006 07:45:27 -0800, "Dave" <Dave@discussions.microsoft.com> wrote: >MY Off...

If function using cell format as the condition
I would like to create an "If" logical function formula, where the "true" condition is based on the format of the cell. For instance, if cell b3 is formatting with a fill color, then add that number; if not, then add zero. Can anyone help? That will need VBA, unless the fill colour is derived from conditional formatting (in which case you could impose an equivalent condition). -- David Biddulph "Janie" <Janie@discussions.microsoft.com> wrote in message news:25499DED-25B8-4E7D-8CA2-A22788839E56@microsoft.com... >I would like to create...

Automatic Calculation #5
I have a spreadsheet with a single forumla that is not updating automatically. All other formulea on the sheet update automatically. Anyone seen anything like this? The specifics: Time sheet spreadsheet, bosses want to track billable vs. unbillable hours automatically. I set up a dummy page that captures (via IF statements) the unbillable hours based on the Department number (7 nos. for billable, 1 for unbillable). I then sum those unbillable hours and refer to that summation from the main page. All that works fine. I then have a cell with formula subtracting the Non-billable hours from the ...

Check this update
--bxedmallcmvwsdaw Content-Type: multipart/related; boundary="niiqhwgkej"; type="multipart/alternative" --niiqhwgkej Content-Type: multipart/alternative; boundary="bvictdhlxmshrrhq" --bvictdhlxmshrrhq Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "December 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to protect your com...

update links
Hi I have big problem with update links in excel file When I'm opening excel file I click button "don't update links" (I need 'old data') but I still get fields with "#ARG!" Why ? and how can I resolve this problem. Thanks for help I bet #ARG! translates to #Ref! in English. If I'm correct: xl2002+ likes to recalculate any workbooks that were created in previous versions. In earlier versions of excel, if you answer No to the update links prompt, the existing values are kept. In xl2002+, you get those errors. Jim Rech posted a registry tweak:...

outlook express 6 spell check non functional
I have a new install of outlook express 6 and just installed works 7 - I have spell check in the works programs but it is completely absent in my outlook express - can someone tell me how to get the spell check function working in my email program? Thanks very much! ...

calling DLL function from another DLL
Hi, I need to call functions of a third party dll in a another dll, where both dll are explicit linking. What is the best way to do this? If the main dll is dll-A and the third party dll is dll-B. Now I need to load and unload dll-B within every dll-A function call. What can I do to have the dll-B loaded when dll-A is loaded and the I need to load and free the third party dll every time the main dll funtion is called. How can I load and unload the dll-B just once and use the handler still valid? > Now I need to > load and unload dll-B within every dll-A function call. Why would you...

automatically retrieving email #4
Outlook 2003 How do I stop it from automatically retrieving email on start up? ...

rules don't run automatically
I'm having problems with my rules lately because they aren't running automatically like they used to. Everytime it seems like I have to go to tools>rules wizard>run now... My rules are all the same: apply after message arrives with "something" in the subject move it to the "something" folder "ZenMasta" <me@nospam.kthx> wrote in message news:uAk9WlVuKHA.812@TK2MSFTNGP06.phx.gbl... > I'm having problems with my rules lately because they aren't running > automatically like they used to. > > Everytime it ...

count if function-Urgent deadline to meet-Thanks
I Have two columns. Column A has Yes or No, generaed via a lis Column B has a regions (1 - 12 I want to count the number of Yes in column A only if Colum B has a 12 in it I have tried SUMPRODUCT but does not work Colum B is genarated via another column so I have several #n/A in that column The fourmula for column B is =VLOOKUP(F5,'spreadsheet1.xls'!Store_Number,3 SO by entering a number in cell F5 it looks up a table named Store_Number and populates Column Many Thanks in advanc Chri One way =SUMPRODUCT(--(A2:A5000="Yes"),--(B2:B5000=12)) -- For everyone's benef...

Outlook Client Auto Update Permissions #2
Can the Outlook Client Auto Update be run by users without administrative permissions? ...

Any way to use "standard" Excel functions inside VBA functions
I am new to this group, so don't mind if I ask somthing that is too easy for you. My question is whether I may use all "standard" Excel 2007 functions. I would like to find interval in which some function returns "#NUM!" although it must not, because all parameters are inside "permited ranges". My idea is to start with some interval [A, B] for which A is "acceptable" argument and B is "unacceptable" argument, and, by halving it (C=A+(B-A)/2.0) and asking : IF C is "unacceptable" argument THEN B=C ELSE A=C WHILE (B-A...

Adding late fees automatically
I work for a property management company and details of our rental properties are stored in Excel workbooks, one month to a worksheet. I have been asked to make the spreadsheets automatically change the value of the "Late fee" column to $60 on the 5th of each month if the rent has not been paid (ie if the following column is blank). I need to know if this is possible in Excel, and if so, how do I do it? =IF(AND(B2="",DAY(TODAY())=>5),60,0) assuming the rent is in B2 -- HTH RP (remove nothere from the email address if mailing direct) "Nick Xylas" <...

Hyperlink Function not working as expected
Hi, I want to create a hyperlink to another WORKSHEET in the same workbook. I wrote the following in Worksheet "Sheet1" and Cell A1 I wrote the following =HYPERLINK('Input Sheet'!E13,"Hi") Now, this cell A1 displays -Hi- and the font is blue color and it is underlined. but when i click on the cell (mouse cursor changes to HAND ) am not navigating to the --Input Sheet-- .What am I doing wrong ? (Please note a sheet by the name -Input Sheet- exists.) On the other hand If i make the hyperlink as =HYPERLINK("c:\q.xls", N10) -- then when i click on t...