To keep in just one cell all values related to a monthly rate

I have to update in a monthly basis a currency rate.
I know that there is a function, formula (or something like that) to link a 
month descripton to a number.
e.g.
September09   1,325
October09        2,235
November09     1,258
I would like to select correspondent month and the cell be updated with the 
value.

0
Utf
5/25/2010 2:51:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
1324 Views

Similar Articles

[PageSpeed] 35

Data Validation dropdown list for choosing the month combined with a lookup
table and VLOOKUP function.

For help on creating data validation dropdown lists see Debra
Dalgleish's site.

http://www.contextures.on.ca/xlDataVal01.html

For help on VLOOKUP to populate the currency rate after choosing a date
see also at Debra's site.

http://www.contextures.on.ca/xlFunctions02.html


Gord Dibben  MS Excel MVP

On Tue, 25 May 2010 07:51:01 -0700, Dalcin <erderder@tiorlm.com> wrote:

>I have to update in a monthly basis a currency rate.
>I know that there is a function, formula (or something like that) to link a 
>month descripton to a number.
>e.g.
>September09   1,325
>October09        2,235
>November09     1,258
>I would like to select correspondent month and the cell be updated with the 
>value.

0
Gord
5/25/2010 4:15:55 PM
First of all: Thank you very much for your answer!
I tried Data Validation but I don't know how "to link" the month to the value.
I would like to select the month in the drop down list and as result a cell 
shows up just the value linked to the month.

"Gord Dibben" escreveu:

> Data Validation dropdown list for choosing the month combined with a lookup
> table and VLOOKUP function.
> 
> For help on creating data validation dropdown lists see Debra
> Dalgleish's site.
> 
> http://www.contextures.on.ca/xlDataVal01.html
> 
> For help on VLOOKUP to populate the currency rate after choosing a date
> see also at Debra's site.
> 
> http://www.contextures.on.ca/xlFunctions02.html
> 
> 
> Gord Dibben  MS Excel MVP
> 
> On Tue, 25 May 2010 07:51:01 -0700, Dalcin <erderder@tiorlm.com> wrote:
> 
> >I have to update in a monthly basis a currency rate.
> >I know that there is a function, formula (or something like that) to link a 
> >month descripton to a number.
> >e.g.
> >September09   1,325
> >October09        2,235
> >November09     1,258
> >I would like to select correspondent month and the cell be updated with the 
> >value.
> 
> .
> 
0
Utf
5/25/2010 6:14:06 PM
That's what VLOOKUP is used for.

Did you look at Debra's site for help on that function?


Gord



On Tue, 25 May 2010 11:14:06 -0700, Dalcin <erderder@tiorlm.com> wrote:

>First of all: Thank you very much for your answer!
>I tried Data Validation but I don't know how "to link" the month to the value.
>I would like to select the month in the drop down list and as result a cell 
>shows up just the value linked to the month.
>
>"Gord Dibben" escreveu:
>
>> Data Validation dropdown list for choosing the month combined with a lookup
>> table and VLOOKUP function.
>> 
>> For help on creating data validation dropdown lists see Debra
>> Dalgleish's site.
>> 
>> http://www.contextures.on.ca/xlDataVal01.html
>> 
>> For help on VLOOKUP to populate the currency rate after choosing a date
>> see also at Debra's site.
>> 
>> http://www.contextures.on.ca/xlFunctions02.html
>> 
>> 
>> Gord Dibben  MS Excel MVP
>> 
>> On Tue, 25 May 2010 07:51:01 -0700, Dalcin <erderder@tiorlm.com> wrote:
>> 
>> >I have to update in a monthly basis a currency rate.
>> >I know that there is a function, formula (or something like that) to link a 
>> >month descripton to a number.
>> >e.g.
>> >September09   1,325
>> >October09        2,235
>> >November09     1,258
>> >I would like to select correspondent month and the cell be updated with the 
>> >value.
>> 
>> .
>> 

0
Gord
5/25/2010 6:48:35 PM
Reply:

Similar Artilces:

Outlook keeps prompting for logon..
User is on Outlook 2k3, latest service packs, updates, etc. Mailbox lives on Exchange 2007 (SP1). Everytime the user starts Outlook they're prompted to logon. I've disabled the logon prompt at startup option (and enabled it and then disabled it again), but it's still happening. I've tried it with cached mode on and cached mode off. I've tried it with offline mode enabled and disabled. I've tried deleting his profile and recreating it. I've tried switching to an RPC/HTTPS connection. Finally, i completely uninstalled Outlook 2k3 and installed Outlook 2k7. Not...

Find highest value in each row.
Hi, I have a problem trying to find the highest value for each row. I have a query, which adds up scores. Its based on a table something like this orgID Score1 Score2 Score3 Score4 Score5 Score6 1 10 1 5 2 11 20 2 1 4 2 12 11 24 1 10 7 15 12 1 2 and so on..... I have query that basicly adds up some of the scores into groups. So its like: orgID ScoreSet1:[Score1]+[Score2]+[Score3] ScoreSet2:[Score4]+[Score5]+ [Score6] 1 16 ...

printing excel sheets duplex, make margin stay on one edge
Hi, I wish to printa large nummber of excel sheets in duplex (printed on both sides, portrait) and then bind them on one side. I cannot find the method to make the margin stay on one edge so that I can bind it. As there is a lot of data on each page I wish to use the maximum printing width and have the margin only on the side that is bound. Am using MS Excel 2000 and a HP laserjet Thanks Jim ...

Help Old addresses keep coming up
When I go to send an email to some people their old email addresses keep popping up together with their current ones. I do not have them listed in contacts or in an address book. It is a minor annoyance. I am using Outlook 2003. It is a minor annoyance. How do I delete these old addresses? Where they show up is where I am entering the e-mail address on the item being e-mailed. Thanks You did not specify how you were entering the addresses. Presumably you are using autocompletion and they are...

Help with printing one name on a report
On my Form that has No Record Source I have a Combo Box [cmbHorsePlanner] that has a row source SELECT qryHorseNameActive.HorseID, qryHorseNameActive.Name FROM qryHorseNameActive; And next to that Combo Box I have a Print control that prints my form that I only want the one name that I select from my Combo Box My Report is rptHorsePlanner, I suppose I will need a requery or Dirty so I can change selections to print a Report! Thanks for any Help...........Bob Bob, What is the Record Source of the report. Assuming your report is based on a query, then I think all you will need to do i...

How to find a record using more than one field?
I have created a table with several fields such as ID, name, date, etc. I also created a switchboard with a find command to locate a record in forms view . However the find command only searches under one field. Can I create a find command that matches 2 fields? I want my find command to find a record in form view that matches both the ID and date fields. Yes, it is possible. There are several methods that should work. Can you post what method you used to for your find command? Then if you used VBA code we can suggest a modification to the code. John Spencer Access MVP...

Selecting cell with mouse
Hi, Excel 2000 I have a spreadsheet that allows me to move to cells locked or unlocked with the cursor but doesn't allow me to use the mouse. I'd thought that the range of what could be selected had been restricted using vba but this isn't the case, I've opened the file and disabled vba and still can't select the unlocked cells with the mouse. If I unprotect the worksheet, then all works as expected. I'm at a loss! Any pointed most welcome. Rob I've never seen excel allow you to select a cell with the arrow keys, but not the mouse. My first thought w...

Confirmation of receipt keeps trying to send and fails
Help! I received an email message that requested a confirmation that it had been received. I told it to send confirmation, but it failed when attemtping to send it and produced an error. I tried deleting the email message and everything in the draft items and Outbox, but every time I switch on Outlook the darn thing tries to send this email confirmation. It seems there is nothing I can do to stop it, even though I cannot see it! I have tried uninstalling Office 2003 and reinstalling it, but all of my settings are remembered as are all the old emails. I just cannot seem to get rid of it! ...

how to enter a value, and have it SUMMED with the existing cell co
An Excel cell contains, say= 4. How do you enter, say=2, and have the result = 6? (not COPYING and Paste As ...) ...

Use a cell's value as row number for another cell in Excel
Excel XP: I'm trying to use the values in one row of cells (D1:F1) as the reference for the row value for a different series of cells (D2:F4). For example: Column A B C D E F Row 1 1000 30 170 1 2 3 2 1500 60 190 3 1200 20 175 I want to get the following cells to equal: D2=1000 (In other words: D2 = A1); D3=30 (In other words: F2 = B1); E2=1500 (In other words: E2 = A2); E3=60 (In other words: F2 = B2); F2=1200 (In other words: F2 = A3); F3=20 ...

Cell color change referencing the date in designated cell
HI, How can I have fill color of cell change according to due date. In example, red overdue, green - on time, black complete. Thanks, K Use Conditional Formatting. You'll need to determine what is Overdue, On-time and Complete Read up on the help and come back for specific help Regards Trevor "Klonja" <plunti@yahoo.com> wrote in message news:a18370dd.0401251510.2f990e0f@posting.google.com... > HI, > > How can I have fill color of cell change according to due date. In > example, red overdue, green - on time, black complete. > > Thanks, K Hi Cl...

copying cells #2
In cell A1 sheet1 I have a number which lets say represents a code for a specific shape that i have allready drawn in cell B1 in sheet2.Can you please tell me what kind of instruction i can give to cell B1,sheet1,so that i can get that shape from cell B1 sheet2 into B1 sheet1? How about putting those shapes on Sheet1 and hide them. Then you could have a macro unhide the one you want. J.E. McGimpsey shares this in a macro at his site: http://www.mcgimpsey.com/excel/lookuppics.html If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcrit...

Keep Lines Together; Keep with Next
I'm working in Vista. When I select lines and click on "Keep Lines Together," the lines do not stay together. They stay together if I also click "Keep with Next," but I'm working on a booklet in which the pages are 1/4 of a letter-sized page, and a lot of space (4 lines) is left at the bottom and wasted if I also click "Keep with Next." Why won't "Keep Lines Together" keep the lines together? The project is a small, bound country club booklet, with names, addresses, phones, etc. Each entry might be 2, 3, or 4 lines. Any h...

cell content
How do I get the content of list box? I clicked the cell after = sign to display the content, but it displayed 0. ...

why do my pictures keep disappearing?
Although I have checked the box in 'pictures' any form of graphic or word art that I have inserted into my publication keeps disappearing! If I click in the general direction of the insertion the bounding dots appear, but no picture Are you sure you have view, pictures, detailed display checked? If so try updating your graphics driver... -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "JG" <JG@discussions.microsoft.com> wrote in message news:57E101AC-A758-400A-9269-3B68B754CA0E@microsoft.com... > Altho...

update subform from value in another form
I have a mainform and two subforms liked through member number. I want to enter data in the subform for every quarter for each member. I have an unbound combo box in the main form to select the quarter. How do I store the quarter information for each member in the subforms based on the combo box. I don’t want to store the quarter information in the main form. SeekAns - It sounds like you are adding new records in the subform. If so, set the default value of the control in the subform to be the value selected in the combo box on the main form. -- Daryl S "See...

Total Value and Account Value do not match??
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I am using Microsoft Money 2004 Standard. I just set up a new account to keep track of mutual funds however even with just one transaction listed I get a weird total figure. Here are the figures: Shares: 1593.298 Price: $10.06 Total Value: $16028.58 Except Money says the "Account Value' is $16140.11 Where does the difference come from? How do I get the numbers to match up? Thanks - -- Due to large amount of spam mail as well as personal security, please respond directly to this newsgroup. Encrypted Messages, using PGP can be poste...

Memo data type shows just 2 boxes in Report Print Preview
I have searched few quite a few posts but have yet to see this issue addressed. I am befuddled as to how this happened b/c my report used to work just fine. Anyway, here is my situation: I am creating a report by selecting specific records from a combo box in a form that is linked to a query. Everything works great, except that one of my fields that is displayed on the report is showing just 2 boxes, or some other kind of giberish (chinese letters too) - but it is always just 2 characters. When I changed this field from a memo data type in the source table to just plain old text, the report d...

default value for one field based on another
I have a form with two date fields on it - Service Date and Posted Date. Since in most cases the Service Date will be the same as the Posted Date, I have the default value of Posted Date field set to the value of the Service Date field by using the following expression in the default value property: =[ServiceDate]. It's not working though and I'm not sure why. Try this in the AfterUpdate event of the first date: Private Sub Date1_AfterUpdate() If Nz(Me!Date1, "") <> "" And Nz(Me!Date2, "") = "" Then Me!Date2 = Me!...

how can I keep a tab in place?
I have workbooks with multiple worksheets. I would like to keep the summary tab in a fixed (first) position so that the user can easily, quickly find it without using the l<, <, >, >l (arrow) tabs. I use hyperlinks to get from the summary tab to the other tabs, and I know I can hyperlink tab back... but I'd rather not use the hyperlinks too much. Tools - Protection - Protect workbook (for structure) Or, create a custom button/macro that navigates to the summary sheet. -- Best Regards, Luke M "missy8892" <missy8892@discussions.microsoft.com>...

Forms -Add one more field
I am using outlook 2003 XP Pro I am happy with the standard default setup of Outlook. I wanted to add one more field to the main contacts view. i.e. Products So that we can add what products the customer has bought from us in the past and we can use that field for mailmerge. can anyone help me out on that. I am new to forms and don't know how to use it, hence don't want to take a wrong step just in case I mess things up. Appreciate your help and a prompt response. When you are viewing your contacts, you can right click on either of the fields already showing (i.e. full name,...

email problem with one user
Hello, I have a client using Outlook 2003 on a SBS 2003 server. The users has an exchange internal e-mail of USER1-5.MAIL.local and POP3 external e-mail USER1-5.INTERNET.COM. For most of the users the e-mail works the way it is suppose to. But one of the user sends an email to one recipient the e-mail is generated with the POP3 e-mail address but if the e-mail is created with multiple recipients for some reason the exchange internal is used. I have talked with the user and she does not do anything special when creating e-mail when it is for one person or multiple people. She does n...

Absolute Value Trendline
Hi, I'm working on a Mechanical Engineering lab with a plot of data in the form of an absolute value function. Is there any solution to allow Excel to plot a line of best fit as an absolute value function? In a range of your worksheet, convert the values into absolute values using the ABS() worksheet function. For example, if the values are in column B, starting in B2, in C2 enter =ABS(B2) and fill this formula down as far as you need. Plot this data and add a trendline to this series. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services - Tutorials and Cust...

"SMTP: Message Submitted to Categorizer" .. then just sits there
Hi We're running SBS2003 with all the latest service packs applied. We are using the pop3 connector to download mail from our ISP. Lately we've had problems with external mail not reaching specific user's mailboxes. (Messages sent locally from another user at the exchange server to this user are delivered.) These messages are going to be delivered to a second domain hosted on the exchange server, not the primary one. The external mail is downloaded with the pop3 connector, without errors, but the message doesn't show up in the users mailbox. The message tracking center lo...

Outlook 2003 keeps asking for password for POP3 account
I am running WinXP Pro and Office 2003. My Outlook 2003 keeps popping up intermittently a pop-up box requesting me to re-enter the POP3 account password. Now, there is nothing wrong with the password already entered (check box to save password), so all i am having to do is click OK, but it is really annoying. Does anyone know what i can do to stop Outlook from doing this? Thanks for your help. ...