in a single worksheet,hw to match values based on 2 list

i got an excel sheet with 3 columns

the first column B9 is header "Risk Source classification" 
the second column c9 has header "Risk effect" 
the third column should be d9 with header "Risk description" 


now from b10-d10 till b40-d40 we enter values as the user chooses like

B10 will have list values for "Risk source classification" predefined list 
with about 80 values to choose from in the dropdown
C10 will have list values for "Risk effect" and has 3 values 
"high","medium","low"

i.e when "Risk Source classification" and "Risk effect" are selected "Risk 
description" should be populated automatically ie in d10.

Now in my excel rows say 4 to 40 are having rows for these values as 
mentioned above.



and from row 51 they have specified again headers

D51 has header called "Risk Source classification" and D52,D53,D54 is 
specified as headers with "high","medium","low"

for ex if d52 is "Risk source classification" value "workflow"  
correspondingly
then for e52 "high" it has value "directly supports customer organization 
mission and/or goals"
     for f52 "medium"it has value "directly supports customer organization "
     for g52 "low" it has value "to be null"

similarly values are defined for each risk source classification from 
d52,e52,f52,g52 until d138,e138,f138,g138

now in B10 if user selects "Workflow" and if he chooses c10 as "high" then i 
should automatically get value from e52 as "directly supports customer 
organization mission and/or goals"


This way risk source classificaiton values range from D51 to D138 adn for 
each there are 3 probable values for high, medium and low.
and the formula should be performed in d10 for any value selected..  how can 
i achieve this please let me know.

hope i have asked the question in a way that can be understood


      



0
Max1 (168)
4/7/2009 3:47:01 AM
excel 39879 articles. 2 followers. Follow

2 Replies
637 Views

Similar Articles

[PageSpeed] 30

Max,

=INDEX($E$51:$G$138,MATCH(B10,$D$51:$D$138,FALSE),MATCH(C10,{"High","Medium","Low"},FALSE))

HTH,
Bernie
MS Excel MVP



"Max" <Max@discussions.microsoft.com> wrote in message 
news:1853C3A0-D2E6-4D2F-AE1B-0CD84865881E@microsoft.com...
>i got an excel sheet with 3 columns
>
> the first column B9 is header "Risk Source classification"
> the second column c9 has header "Risk effect"
> the third column should be d9 with header "Risk description"
>
>
> now from b10-d10 till b40-d40 we enter values as the user chooses like
>
> B10 will have list values for "Risk source classification" predefined list
> with about 80 values to choose from in the dropdown
> C10 will have list values for "Risk effect" and has 3 values
> "high","medium","low"
>
> i.e when "Risk Source classification" and "Risk effect" are selected "Risk
> description" should be populated automatically ie in d10.
>
> Now in my excel rows say 4 to 40 are having rows for these values as
> mentioned above.
>
>
>
> and from row 51 they have specified again headers
>
> D51 has header called "Risk Source classification" and D52,D53,D54 is
> specified as headers with "high","medium","low"
>
> for ex if d52 is "Risk source classification" value "workflow"
> correspondingly
> then for e52 "high" it has value "directly supports customer organization
> mission and/or goals"
>     for f52 "medium"it has value "directly supports customer organization 
> "
>     for g52 "low" it has value "to be null"
>
> similarly values are defined for each risk source classification from
> d52,e52,f52,g52 until d138,e138,f138,g138
>
> now in B10 if user selects "Workflow" and if he chooses c10 as "high" then 
> i
> should automatically get value from e52 as "directly supports customer
> organization mission and/or goals"
>
>
> This way risk source classificaiton values range from D51 to D138 adn for
> each there are 3 probable values for high, medium and low.
> and the formula should be performed in d10 for any value selected..  how 
> can
> i achieve this please let me know.
>
> hope i have asked the question in a way that can be understood
>
>
>
>
>
> 

0
Bernie
4/8/2009 12:33:56 AM
Thanks a lot Bernie
It worked.

"Bernie Deitrick" wrote:

> Max,
> 
> =INDEX($E$51:$G$138,MATCH(B10,$D$51:$D$138,FALSE),MATCH(C10,{"High","Medium","Low"},FALSE))
> 
> HTH,
> Bernie
> MS Excel MVP
> 
> 
> 
> "Max" <Max@discussions.microsoft.com> wrote in message 
> news:1853C3A0-D2E6-4D2F-AE1B-0CD84865881E@microsoft.com...
> >i got an excel sheet with 3 columns
> >
> > the first column B9 is header "Risk Source classification"
> > the second column c9 has header "Risk effect"
> > the third column should be d9 with header "Risk description"
> >
> >
> > now from b10-d10 till b40-d40 we enter values as the user chooses like
> >
> > B10 will have list values for "Risk source classification" predefined list
> > with about 80 values to choose from in the dropdown
> > C10 will have list values for "Risk effect" and has 3 values
> > "high","medium","low"
> >
> > i.e when "Risk Source classification" and "Risk effect" are selected "Risk
> > description" should be populated automatically ie in d10.
> >
> > Now in my excel rows say 4 to 40 are having rows for these values as
> > mentioned above.
> >
> >
> >
> > and from row 51 they have specified again headers
> >
> > D51 has header called "Risk Source classification" and D52,D53,D54 is
> > specified as headers with "high","medium","low"
> >
> > for ex if d52 is "Risk source classification" value "workflow"
> > correspondingly
> > then for e52 "high" it has value "directly supports customer organization
> > mission and/or goals"
> >     for f52 "medium"it has value "directly supports customer organization 
> > "
> >     for g52 "low" it has value "to be null"
> >
> > similarly values are defined for each risk source classification from
> > d52,e52,f52,g52 until d138,e138,f138,g138
> >
> > now in B10 if user selects "Workflow" and if he chooses c10 as "high" then 
> > i
> > should automatically get value from e52 as "directly supports customer
> > organization mission and/or goals"
> >
> >
> > This way risk source classificaiton values range from D51 to D138 adn for
> > each there are 3 probable values for high, medium and low.
> > and the formula should be performed in d10 for any value selected..  how 
> > can
> > i achieve this please let me know.
> >
> > hope i have asked the question in a way that can be understood
> >
> >
> >
> >
> >
> > 
> 
> 
0
Max1 (168)
4/13/2009 4:23:01 AM
Reply:

Similar Artilces:

Security Permissions on Address List!!!!!!!!
I accidently set the authenticated users group to deny all on an address list and set it not to inherit permissions..... can i somehow reset this?! help! thanks. I cant even delete it either in adsi! Correction, I deleted the address list container out of the configuration schema in AD. is there any way to restore this? a reinstall of exchange, or using an authoritative restore of AD just for that object? Thanks. "Gabe Matteson" <gmatteson@inquery.biz.nospam> wrote in message news:%23hZuKDMHGHA.1760@TK2MSFTNGP10.phx.gbl... >I accidently set the authenticated users ...

negative values causing column title problem
How do I get the Column titles x axis lables at he bottom of the chart field when have negative values It s friday and this is killing me. Select the axis Choose Format>Selected axis On the Patterns tab, for Tick Mark Labels, select Low TFrisch wrote: > How do I get the Column titles x axis lables at he bottom > of the chart field when have negative values > > It s friday and this is killing me. -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html hey thanks - boy was thaqt annoying >-----Original Message----- >Select th...

User distribution lists do not appear in GAL
Hello All, Today I had a problem with my Exchange 2003 server for the distribution lists and their GAL (Global Address List) listings. I solved the *my* problem and I would like to share it as a reference..(The problem might be different than yours but hope it may help somebody) ---- The Problem --- The distribution groups that are created under the "Active directory Users and Computers" Panel do not appear in GAL , even after the RUS (Recipent Update Service) is updated over the accounts. Additional info A ) The groups were apperaring under the All Global Address List > Defa...

Change the numeric value of a dropdown to text value
I'm trying to email the values of form fields that I have created in a form. So basically what I want to do is in the Subject I want to take the "value" of the dropdown called dropdown1 and put it in the subject. It is working but it is putting the numeric value instead of the text value. How do I convert it. Thanks much! Use the .Result, not the .Value -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "Wo...

Multiple copies of emails #2
I've read some of the other posts on this problem, but nothing I've tried seems to work. Here's the background. I'm using Outlook 2000 (SP-3). I have 3 different email accounts with comcast that I have forwarded to my Outlook. If some sends an email to any one of those account, I end up getting 3 copies of it in my inbox ... all to the same one of my email addresses. I know this isn't a problem with Comcast, because when I look at my messages on their server, there's only one copy of each. So I figure it must have something to do with the Rules ... but w...

How do you keep a list drop-down menu visible?
I would like to keep the drop-down menu created when you make a list in Excel 2003 visable, even when you dont have a cell active in the area the list has been created. It seems the default behavior is to hide the drop-down menu unless a cell in the list area is active. I have another document here that somone else created (dont know who) where the drop-down menu for filtering using a list is ALWAYS visable. Its driving me nuts trying to find out how it was done. If anyone knows i would be very grateful. Thanks It sounds like they used Data|Filter|autofilter. phatfish wrote: > > I...

Worksheet name
Hi What should i do so that the Worksheet (Tab) name is the same as what i type in a specific cell? Regards, Hi Walrus. I think that the only thing to do is to type them in separately. I am not aware that you can pull one from the other automatically. Please hit Yes if my comments have helped. Thanks. "walrus" wrote: > Hi > > What should i do so that the Worksheet (Tab) name is the same as what i type > in a specific cell? > > Regards, Hi, Right click your sheet tab, view code and paste the code in. Change A1 to the cell y...

SELECT large amount of data in a worksheet
I have my mathematical model results in excel format every times I run my model. Huge amount of data --10,000 rows, with 10 columns. Now I like to copy certain portion of data into a new workbook for further analysis. And the data I wish to select is from F10:F10010 to P10:P10010. I can always select F10, scroll down and select P10010. This is a bit cumbersome and takes time. I cannot assign NAME to these data because every time I run my model, my range of data that I am interested in could differ -- meaning sometimes it could be ranging from H5:Q5 to H5000:Q5000. Is that a way for...

File List Folder Outlook 2003
Hi, I found an article which describes how to setup a folder to view any file system folder in Outlook 2002. I can't find any equivalent in Outlook 2003. Basically I just want to have an Outlook 2003 folder named something like "my files" and have its address pointing to a mapped drive. I have tried setting up a "home page" location on a folder, but it only works for web addresses, not file systems. Thanks in advance. Regards, Troy At the bottom of the Navigation pane, you should have buttons for Folder List, Shortcuts, etc. Select the Folder List button. Op...

Dynamics 2.0 Locks up
All of a sudden yesterday my dynamics POS 2.0 started locking up when it was minimized or idol for a few minutes of non use. Has anyone else had this problem? I am running Vista. Thanks If you haven't already, you may want to post this question in the retail management forum. -- Charles Allen, MVP "shadowrunner11" wrote: > All of a sudden yesterday my dynamics POS 2.0 started locking up when it was > minimized or idol for a few minutes of non use. Has anyone else had this > problem? I am running Vista. > > Thanks ...

RMS SO 2.0 Manager
On one of my RMS SO 2.0 Manager's computer I'm having a huge delay (about 5 minutes) before the Item list shows up (Database - Items). This only happens on this one machine that's "directly" connected to the RMS server (no VPN etc), while it's trying to bring up the Items List the SOMANAGER.EXE process CPU Utilization stays at 99% and there is no network utilization/activity. When I bring up an (all) Item List/Value report it doesn't take very long at all. Has anybody else encountered this problem and knows of a potential solution? I've already tried r...

Installing office 2000 disk 2 with publisher on upgraded windows x
I can not get disk 2 to install. It preloads then as files start to copy, it stalls then ends with error message. this is office2000 professional 2 cd set. everything else works exept publisher and its components. Please advise Thanks What is the error message? -- JoAnn Paules MVP Microsoft [Publisher] "cugolfn" <cugolfn@discussions.microsoft.com> wrote in message news:F57B8716-65FA-4619-93A4-AA24D44ABC33@microsoft.com... >I can not get disk 2 to install. It preloads then as files start to copy, >it stalls then ends with error message. this is office2000 profess...

redirect from worksheet
could you please tell me if you are on spreadsheet that has 3 worksheets, if you are on 1 of the 3 worksheets and you want to go to an entirely new spreadsheet, that is not part of that spreadsheet, can you do that and how? or can you have worksheets under a worksheet in a spreadsheet? "quinn111" wrote: > could you please tell me if you are on spreadsheet that has 3 worksheets, if > you are on 1 of the 3 worksheets and you want to go to an entirely new > spreadsheet, that is not part of that spreadsheet, can you do that and how? > > or can you have worksheets...

Selecting Photos from and ComboBox list then print on a Report
I would like to have a ComboBox on a Form that lists the names of photographs, and somehow have the names pointing to the actual image stored in the database or on the PC. Then by clicking a button on the Form, I'd like to print a Report with the photo printed on it, along with other data from the record. I hope that makes sense. Thanks in advance for the help. Dave Have a look at PictureMgr, the free demo software that I wrote to manage and print images. http://www.datastrat.com/Download/Picture2K.zip -- Arvin Meyer, MCP, MVP http://www.datastrat.com http:/...

spellcheck on protected worksheet
how can I have spellcheck activated on a password protected worksheet for cells that are unlocked Chris, here is one way, password for sheet in this macro is 123, assign this to a button on the sheet Sub spellcheck() ActiveSheet.Unprotect password:="123" Cells.CheckSpelling CustomDictionary:="CUSTOM.DIC", IgnoreUppercase:=False _ , AlwaysSuggest:=True ActiveSheet.Protect password:="123" End Sub -- Paul B Always backup your data before trying something new Using Excel 97 & 2000 Please post any response to the newsgroups so others can benefit ...

how to automate exporting excel worksheet to csv file
Looking for a way to create a macro or script to export the contents of an excel spreadsheet ( 6columns) to an ASCII comma separated file. I would like to be able to script this to run weekly. On Fri, 11 Jul 2003 20:23:43 -0500, "Bob" <rpl-erroroneous@goldengate.net> wrote in microsoft.public.excel.misc: >Looking for a way to create a macro or script to export the contents of an >excel >spreadsheet ( 6columns) to an ASCII comma separated file. >I would like to be able to script this to run weekly. With VBA, I would do it this way: someWorkBook.SaveAs Fil...

Working hours #2
Hi, I have helpdesk inquiries logged in at various times of the day. Sinc our working hours are from 08:00 to 17:00, I would like to the clock t start the next working day if the inquiry arrives after working hours. Can anyone suggest what formula I should use? Example of cell A1 content: 13/06/2005 14:05 Thanks Han -- Hani Muhtad ----------------------------------------------------------------------- Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2679 View this thread: http://www.excelforum.com/showthread.php?threadid=46722 Hi Hani Try =IF...

Copy Public Folders #2
I need to copy a ton of public folders and their sub folders to a single public folder on my exchange 2003 server. I once saw a program that would accomplish this for me. Does anyone know how to do this? Thanks PFmigrate, http://www.msexchange.org/tutorials/PFMIGRATE.html If you need to keep the same permissions for each folder you will also need PVADMIN, http://hellomate.typepad.com/exchange/2003/10/the_pfdavadmin_.html -- John Oliver, Jr MCSE, MCT, CCNA Exchange MVP 2006 Microsoft Certified Partner "Curious Joe" <joebob.johnson@gmail.com> wrote in message news:11...

CRM 3
HI all, i was unable using CRM 1.2 report in CRM 3. so, where i can find the guide for using custom crystal report in CRM 3? what software i should install. thank you, regards, Ng HI, all, it is possible add the crystal report crm 1.2 to CRM 3 report page? bec when i try using CRM3 Add new Report. i see the following msg Error Uploading Report An error occurred while trying to add the report to Microsoft CRM. Try adding the report again. If this problem continues, contact your system administrator. if can add, the crystal report crm 1.2 it is require re-mapping the fields? bec i...

Linked List & Dynamic Memory Allocation
Hi, I am learning about data structures. I am somewhat confused and unable to apply concept of dynamic memory allocation to my application. This is what I am trying to do. I have an array of structures as follows: #define MAX 3 typdef struct node { char name[30]; struct node* next; }nodes; int main (void) { nodes m_node[MAX]; // play around with link list here add, remove, search items etc. return 0; } I am trying to dynamically allocate memory if user chooses to enter more than 3 records. I run following code to allocate memory dynamically. Code is running fine....

Report #2
Hi we have a different company and for each company we are using different report dictionary is it possible to use a single report dictionary all companies Regards Srinivasan Yes. -- Lyle U Srinivasan wrote: > > Hi we have a different company and for each company we are using > different report dictionary is it possible to use a single report > dictionary all companies > > Regards > Srinivasan OK, that was a bit too brief. Remember that security access to modified reports is specific to each company. Grant access to the modfied reports for each company where...

the picture is too large and will be truncated #2
Hi, When i try to select an entire row and push the copy button. I'm getting the following error: "the picture is too large and will be truncated" There are no pictures in the selection!!(?) I work with Excel 2002 on Win xp prof. Somebody has an idea thanxs in advance greets, Van Steenbergen Jan See Jon Peltier's site about this error http://www.geocities.com/jonpeltier/Excel/XL_PPT.html This is the only KB obout this error This is for 2000 but check it out http://support.microsoft.com/default.aspx?scid=kb;en-us;318265&Product=xlw2K XL2000: Error Message: The P...

insert number in column #2
I have a column with threedigits for telephone extensions. I want to insert a digit before the three throughout the column as the extension has been increased to 4 digits. Any easy way.../ Thanks. AFD One way: In a blank column (say column H), enter H1: =1 & A1 or, if you want them as numbers rather than text: H1: =1000+A1 copy down as far as necessary. Select the column and copy it. Select the original column and choose Edit/Paste Special/Values. In article <Om6vrJpEIHA.3548@TK2MSFTNGP06.phx.gbl>, "afdmello" <afdmello@hotmail.com> wrote: &...

Trouble scanning 3 of 9 #2
I have a client who is using 1"x1" labels to label some internal use products or products that come through the door without UPCs. We have set them up to use the 3 of 9 barcode format when there is no UPC available. The trouble is, the barcode is so condensed that the scanners at the POS can't read them. The line scanners in the receiving department read the barcodes without issue. Has anybody else had this problem? What can be done to remedy this? I have considered writing a simple program to automatically generate an internal use barcode as the UPC-A type scans without issue....

combination validated list and free input
Two cells, first one "Name", the next a validated dropdown ("Dept."), based on "Name". "Dept" currently validates by Indirect(Name cell reference). Each Name of employees is defined as a range that has their allowable departments. The challenge is that I want this to be able to handle non- employees also. So I need the validation to allow for a range name (the list of employees), but also a "Non- Employee" entry, which would allow for someone outside of the company to submit one of these forms. My one idea of how to do this is to hav...