A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Complicated



 
 
Thread Tools Display Modes
  #1  
Old June 12th, 2004, 01:39 AM
Continental Translations
external usenet poster
 
Posts: n/a
Default 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  
Old June 12th, 2004, 02:02 AM
Continental Translations
external usenet poster
 
Posts: n/a
Default 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  
Old June 12th, 2004, 05:59 AM
Frank Kabel
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:44 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.