Any notification through Excel Spreadsheet

[Guest]

Hi all,

How are you ?

Need your advise on Excel spreadsheet again.

I have an ammout, let said 12000 (USD) and would like to
divide into 20 months as follows. Before I divided in 20
months, I Have to convert the accounting rate from USD to
Singapore dollars. Meaning is every I hv to do a
calculation and update in followng excel format. As the
accounting rate will different every month.

May I know is there a notification function in Excel
spreadsheet where the user will be able to set for
triggerring user to update the speadsheet by monthly or
weekly as we like.

Really appeciate you help on this.


Month currency rate actual ammunt in SGP Dollars
Sep-04 2.5
Oct-04
Nov-04
Dec-04
Jan-05
Feb-05
Mar-05
Apr-05
May-05
Jun-05
Jul-05
Aug-05
Sep-05
Oct-05
Nov-05
Dec-05
Jan-06
Feb-06
Mar-06
Apr-06

 

[Dave R.]

Hello.

If you are talking about notification, you can use a formula with
conditional formatting to "show" when something may be out of date. Is that
what you are looking for?

 

[amy]

Thank s Dave.

Just need a function or formula to trigger user by monthly
basic to update the excel spreadsheet.

Your advice is deply appreciated.

 

[Dave R.]

Still not 100% sure what you want but it's getting late.

Ok - if your data (month & rate) is in A1:B20..

You can try this array formula

=IF(TEXT(INDEX(A1:A20,MAX(IF(B1:B20<>"",ROW(B1:B20)))),"mmyy")<>TEXT(TODAY()
,"mmyy"),"Update Exchange Rate","")

entered with CTRL-SHIFT-ENTER.

If the exchange rate for the current month/year is not filled in, this will
remind a person to fill in the exchange rate. Exchange rates beyond the
current month/year will show the same message.

 

[Harlan Grove]

Still not 100% sure what you want but it's getting late.

Ok - if your data (month & rate) is in A1:B20..

You can try this array formula

=IF(TEXT(INDEX(A1:A20,MAX(IF(B1:B20<>"",ROW(B1:B20)))),"mmyy")
<>TEXT(TODAY(),"mmyy"),"Update Exchange Rate","")

entered with CTRL-SHIFT-ENTER.

....

A bit long. The INDEX expression could be shortened to

LOOKUP(2,1/(B1:B20<>""),A1:A20)

and not require array entry. However, if the OP just needs an indicator
whether there were an exchange rate in B1:B20 for every date in A1:A20, and
the dates in A1:A20 were date serial numbers, then the following array
formula would suffice.

=IF(OR(ISNUMBER(A1:A20)<>ISNUMBER(B1:B20)),"Update Exchange Rates","")

 

[Alok Joshi]

You can try the following
Highlight the range B1 to B20.
Select Menu option Format/Conditional Formatting
Enter the following in the Formula (Select Formula Is option)
=(INDEX($B$1:$B$20,MATCH(TODAY(),$A$1:$A$20))="")
Select an appropriate (warning) format.
What this will do is format the range B1:B20 with the specified format in
case the
exchange rate entry for the current month is missing.

Alok