find VLOOKUP help

We have several maintenance packages that we sell and we need to know what we will be paid on a recurring basis.

I've created a Validation table to give me a drop down and beside it I have placed a number of values relating to the different terms and packages available. For example, if a customer takes a standard care package on a three year term, I know the company will only realise revenue for the 2nd and 3rd year so I want to enter a value for the whole term and use LOOKUP to determine the package selected and then the relative calculation.(3 year standard care contract value multiplied by.6666667 will give me our achieveable revenue).

I have listed the options and the multiplying factors side by side on a separate sheet and have named the ranges above. But when I try and layout the VLOOKUP formula it gives me a name error.

Can anyone help?

could you give us a small example of maybe 5 rows,
your range names
and of course your formula

Term Calc
Std 1 Yr 0
Std 3 Yr 0.6666667
Std 5 Yr 0.8
Pmpt 1 Yr 0.7143
Pmpt 3 Yr 0.2381
Pmpt 5 Yr 0.14286
Total 1 Yr 0.5
Total 3 Yr 0.1666667
Total 5 Yr 0.1

Above are the ranges named on a separate sheet.

A B C D
1 Std 1 Value Result
2
3

In the main sheet, in A1 would be a drop down box that I want to multiply with the associated value in the Calc column. B1 will be a value that is populated to give a total value in C1.

I'm probably that far away with the VLOOKUP formula I don't want to confuse matters by displaying it.

Thanks for your prompt response.

Sorry the post hasn't come out clearly at all

ok I am not sure what this is, and I am going to assume that no cells are merged so maybe this will help:
On one sheet (Sheet1)you have:
Column A is the list (as well as the drop down list)
Column B is a value (this is what you want to show up beside your selection)

The next Sheet(Sheet2) you will have Cell A1 as the dropdown menu
Cell B1 will be the value that you will be multiplying to another Cell
Cell C1 will be the Product from that
I am going to assume you did the data validation properly or else it wouldn't work
In Sheet1 highlite the total range of the two columns
goto to your top menu and select insert=>names=>define
type a one word name in there such as Data
Now that range is named

Now highlite the values in column A only goto insert,name,define lets name that range:
Years

Now goto sheet2 cell A1
select data in the menu and goto validation, in the dropdown menu select list, in the source box type this =Years

In B1 enter this formula
=Lookup(A1,Data)

There you go, now make a pick from your drop down list, you should get your value in B1
In C1 you will have your formula =B1*'wherever

I can't stay to check you status, I have to go, I will check later tonight to see how you have done

Thanks for this, I know where I haven't explained myself properly now.

In sheet 2, A1 is the dropdown which has been achieved by Validation.

A2 will be a variable contract value. This will be manually entered line on line.

A3 Would be the result.

So what I want to do is pick an option from the drop down, enter any number in A2 and then have the result of A2*whatever option is picked from A1 dsiplayed in A3.

So if we picked STD 5 Yr, we want whatever we enter in A2 to be multiplied by 0.8.

Hope this makes sense.

So what I want to do is pick an option from the drop down, enter any number in A2 and then have the result of A2*whatever option is picked from A1 dsiplayed in A3.

So if we picked STD 5 Yr, we want whatever we enter in A2 to be multiplied by 0.8.
----------------------------------------------
No Problem, you can place that formula anywhere, even out of sight Say AA1 for example then then the formula in
A3 =A2*AA1
------------------------------------------------

"DK1314" wrote
> In sheet 2, A1 is the dropdown which has been achieved by Validation.
> A2 will be a variable contract value. This will be manually entered
> line on line. A3 would be the result.
> So what I want to do is pick an option from the drop down, enter any
> number in A2 and then have the result of A2*whatever option is picked
> from A1 displayed in A3.
> So if we picked STD 5 Yr, we want whatever we enter in A2 to be
> multiplied by 0.8.

Assuming the reference packages / pricing
are listed in Sheet1's cols A and B

In Sheet2,

Put in A3:
=IF(OR(A1="",A2=""),"",VLOOKUP(A1,Sheet1!$A:$B,2,0)*A2)

As-is, the formula in A3 above can be copied across to return
correspondingly
for other DV selections/inputs in B1:B2, C1:C2, etc

A sample construct for the above is available at:
http://cjoint.com/?dkhZjed4ed
DK1314_newusers.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

That formula works absolutely perfectly Max, thank you.

Dave thanks for your help.

You're welcome !
Glad it worked for you ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"DK1314" <[email protected]> wrote in
message news:[email protected]...
>
> That formula works absolutely perfectly Max, thank you.
>
> Dave thanks for your help.