If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Complicated
In a sheet named "Confirmed" I have a spreadsheet which contains the
following:- Column F (a list of languages) Column G (a list of languages) Column H (a fee per 1000 words) Column I (a fee per 1000 words) Now, I have set up a data validation series in a new sheet, so that when a language is selected, say for example, Arabic which is in F1 of the sheet "Confirmed", the languages listed next to it in G1 appears. If Arabic appears in F1, F2 and F3, then the languages which are listed in G1,G2 and G3 appear. Now what I want to do, is for in a new sheet in cell A1, I want to be able to type in an ammount, say for example 5000. Now, what I would like is for Excel to have a look at what language is selected from my first dropdown list and my second dropdown list. Then I would like it to cross-reference this to my sheet called "Confirmed" and when it finds a match for those two languages, I would like it to multiply the value in column H. If, however I select "Yes" from another dropdown menu, then I would like it to multiply the value in column I by the ammount typed in A1. This is really hard to explain, and unless you have the sheets, it's hard to imagine. If anybody is willing, I could email you the sheets to have a look. Thanks all |
#2
|
|||
|
|||
Complicated
I forgot to mention, if there are several of the same language combinations
in columns F and G, then I would like it to return the cheaper fee from column H or I Continental Translations wrote in message ... In a sheet named "Confirmed" I have a spreadsheet which contains the following:- Column F (a list of languages) Column G (a list of languages) Column H (a fee per 1000 words) Column I (a fee per 1000 words) Now, I have set up a data validation series in a new sheet, so that when a language is selected, say for example, Arabic which is in F1 of the sheet "Confirmed", the languages listed next to it in G1 appears. If Arabic appears in F1, F2 and F3, then the languages which are listed in G1,G2 and G3 appear. Now what I want to do, is for in a new sheet in cell A1, I want to be able to type in an ammount, say for example 5000. Now, what I would like is for Excel to have a look at what language is selected from my first dropdown list and my second dropdown list. Then I would like it to cross-reference this to my sheet called "Confirmed" and when it finds a match for those two languages, I would like it to multiply the value in column H. If, however I select "Yes" from another dropdown menu, then I would like it to multiply the value in column I by the ammount typed in A1. This is really hard to explain, and unless you have the sheets, it's hard to imagine. If anybody is willing, I could email you the sheets to have a look. Thanks all |
#3
|
|||
|
|||
Complicated
Hi
give the following array formula a try (entered with CTRL+SIFT+ENTER): =INDEX(OFFSET('confirmed'!$H$1:$H$100,0,--(X1="Yes")),MATCH(1,('confirm ed'!$F$1:$F$100='sheet2'!$F$1)*('confirmed'!$G$1:$ G$100='sheet2'!$G$1), 0))*A1 Assumptions: - cell X1 contains your drop-down with 'Yes' - sheet2 is the sheet with the data validation listboxes with F1 and G1 as the relevant fields -- Regards Frank Kabel Frankfurt, Germany Continental Translations wrote: In a sheet named "Confirmed" I have a spreadsheet which contains the following:- Column F (a list of languages) Column G (a list of languages) Column H (a fee per 1000 words) Column I (a fee per 1000 words) Now, I have set up a data validation series in a new sheet, so that when a language is selected, say for example, Arabic which is in F1 of the sheet "Confirmed", the languages listed next to it in G1 appears. If Arabic appears in F1, F2 and F3, then the languages which are listed in G1,G2 and G3 appear. Now what I want to do, is for in a new sheet in cell A1, I want to be able to type in an ammount, say for example 5000. Now, what I would like is for Excel to have a look at what language is selected from my first dropdown list and my second dropdown list. Then I would like it to cross-reference this to my sheet called "Confirmed" and when it finds a match for those two languages, I would like it to multiply the value in column H. If, however I select "Yes" from another dropdown menu, then I would like it to multiply the value in column I by the ammount typed in A1. This is really hard to explain, and unless you have the sheets, it's hard to imagine. If anybody is willing, I could email you the sheets to have a look. Thanks all |
Thread Tools | |
Display Modes | |
|
|