Hi - I need to create a form or tool for users by tomorrow morning to select
from a few choices and then look in another tab for the actual amts

For example they would choose from drop downs for the following questions

Which Price list (they would have 5 choices) USD, Euro, Euro/GBP, Euro/USD,
Japan/Yen

Which Product (2 options) product A, product B

Duration of License (they could choose from 1 month up to 36 months)

Geography ( they could choose 5 options)

Price (this is where it does the calculation)

so in another tab there would be the currencies by product
and in another tab there would be geography and an amt attached to those to
multiple by

Anybody have any ideas? Thank you

Hi,
Lets assume you have in sheet2 the information as follows
Column A Product
Column B location
Column C Product

Then in sheet 1 you have your information as follow

in cell A2 = product
in A3 = duration
in A4= location

then to pull the price use

=sumproduct(--(A2=sheet2!$A$1:$A$100),--(A4=sheet2!$B$1:$B$100),$C$1:$C$100)

That will pull the price from sheet2


"H" wrote:

Hi - I need to create a form or tool for users by tomorrow morning to select
from a few choices and then look in another tab for the actual amts

For example they would choose from drop downs for the following questions

Which Price list (they would have 5 choices) USD, Euro, Euro/GBP, Euro/USD,
Japan/Yen

Which Product (2 options) product A, product B

Duration of License (they could choose from 1 month up to 36 months)

Geography ( they could choose 5 options)

Price (this is where it does the calculation)

so in another tab there would be the currencies by product
and in another tab there would be geography and an amt attached to those to
multiple by

Anybody have any ideas? Thank you

Thank you, but I'm still confused Product shows up twice? how does this work
as user interface?

"Eduardo" wrote:

Hi,
Lets assume you have in sheet2 the information as follows
Column A Product
Column B location
Column C Product

Then in sheet 1 you have your information as follow

in cell A2 = product
in A3 = duration
in A4= location

then to pull the price use

=sumproduct(--(A2=sheet2!$A$1:$A$100),--(A4=sheet2!$B$1:$B$100),$C$1:$C$100)

That will pull the price from sheet2


"H" wrote:

Hi - I need to create a form or tool for users by tomorrow morning to select
from a few choices and then look in another tab for the actual amts

For example they would choose from drop downs for the following questions

Which Price list (they would have 5 choices) USD, Euro, Euro/GBP, Euro/USD,
Japan/Yen

Which Product (2 options) product A, product B

Duration of License (they could choose from 1 month up to 36 months)

Geography ( they could choose 5 options)

Price (this is where it does the calculation)

so in another tab there would be the currencies by product
and in another tab there would be geography and an amt attached to those to
multiple by

Anybody have any ideas? Thank you

maybe this will help explain it a little better

here's Sheet1 (user interface)
column B are from drop down choices

Price List (Currency) US/USD
Product A
License Type A
Duration of License 2
Geographic Float Country


Price $5

Sheet 2 Currency
PN Description License Type US/USD Euro Euro/GBP
123 A A 5 100 2,500
B 10 1,000 3,700
345 B A 15 20 4,600
B 20 20 4,700


"Eduardo" wrote:

Hi,
Lets assume you have in sheet2 the information as follows
Column A Product
Column B location
Column C Product

Then in sheet 1 you have your information as follow

in cell A2 = product
in A3 = duration
in A4= location

then to pull the price use

=sumproduct(--(A2=sheet2!$A$1:$A$100),--(A4=sheet2!$B$1:$B$100),$C$1:$C$100)

That will pull the price from sheet2


"H" wrote:

Hi - I need to create a form or tool for users by tomorrow morning to select
from a few choices and then look in another tab for the actual amts

For example they would choose from drop downs for the following questions

Which Price list (they would have 5 choices) USD, Euro, Euro/GBP, Euro/USD,
Japan/Yen

Which Product (2 options) product A, product B

Duration of License (they could choose from 1 month up to 36 months)

Geography ( they could choose 5 options)

Price (this is where it does the calculation)

so in another tab there would be the currencies by product
and in another tab there would be geography and an amt attached to those to
multiple by

Anybody have any ideas? Thank you

Here's my sample for easy reference
It illustrates a way to achieve the complex lookup that you seek:
http://www.savefile.com/files/2142559
Complex lookup on multiple var.xls

In "Enquiry", you'd have the variables selection table in A1:B6

Variables Select from droplist
Price List (Currency) US/USD
PN 345
Description A
License Type B
Geographic Float UK

To retrieve the hire rate based on the 5 variables selected in B2:B6
array-enter (ie press CTRL+SHIFT+ENTER to confirm the formula) in say, B10:
=OFFSET(INDIRECT("'"&B6&"'!A3:A100"),MATCH(1,(INDI RECT("'"&B6&"'!A3:A100")=B3)*(INDIRECT("'"&B6&"'!B 3:B100")=B4)*(INDIRECT("'"&B6&"'!C3:C100")=B5),0)-1,MATCH(B2,INDIRECT("'"&B6&"'!2:2"),0)-1)

This is what you'd have in each of the identically structured source
"country" sheets, eg in "USA":

PN Description License Type US/USD Euro Euro/GBP
123 A A 24 23 21
123 A B 26 98 55
345 A A 20 12 100
345 A B 70 17 68
etc

Ensure data is fully populated in each variable's column.
There should be no intervening blanks.

See Debra's page for ways to fill-in, if necessary:
http://www.contextures.com/xlDataEntry02.html
Excel -- Data Entry -- Fill Blank Cells


--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---