Hi can someone help
i have created a spreadsheet to work out how much money people will make
from each product.

the 20 products are all in one cell (f8) in a drop down list format

so for e.g if i went to cell f8 and select a product from it e.g VOIP then
in the cell next to it, it should bring up the price of it.

also if my product has a space in it e.g Featureline 1 Year how do i put
that into the formula? AS IT SHOWS UP #N/A

this is what im using but it only works for the first 4 products.

=LOOKUP(F8,{"Fusion","Openzone","Switch","Voip","M aintanance","CNE","RCF","CNI","Featureline
1 Year","Featureline 3 Year","Featureline 5 Year","Mobile Low Tier","Mobile
Low Tier","Mobile Low Tier","Telephone System","VAS -
StaticIP";15,35.7,57,78,145,34.7,53,7.8,2.5,6.6,5. 67,8.78,1.45,3.4,5.7,7.53})


--
please can you help... its urgent

They need to be ordered. Try VLOOKUP

=VLOOKUP(F8;{"Fusion",15;"Openzone",35.7;"Switch", 57;"Voip",78;"Maintanance"
,145;"CNE",34.7;"RCF",53;"CNI",7.8;"Featureline 1 Year",2.5;"Featureline 3
Year",6.6;"Featureline 5 Year",5.67;"Mobile Low Tier",8.78;"Mobile Low
Tier",1.45;"Mobile Low Tier",3.4;"Telephone System",5.7;"VAS -
StaticIP",7.53};2;FALSE)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Moh" wrote in message
...
Hi can someone help
i have created a spreadsheet to work out how much money people will make
from each product.

the 20 products are all in one cell (f8) in a drop down list format

so for e.g if i went to cell f8 and select a product from it e.g VOIP then
in the cell next to it, it should bring up the price of it.

also if my product has a space in it e.g Featureline 1 Year how do i put
that into the formula? AS IT SHOWS UP #N/A

this is what im using but it only works for the first 4 products.


=LOOKUP(F8,{"Fusion","Openzone","Switch","Voip","M aintanance","CNE","RCF","C
NI","Featureline
1 Year","Featureline 3 Year","Featureline 5 Year","Mobile Low
Tier","Mobile
Low Tier","Mobile Low Tier","Telephone System","VAS -

StaticIP";15,35.7,57,78,145,34.7,53,7.8,2.5,6.6,5. 67,8.78,1.45,3.4,5.7,7.53}
)


--
please can you help... its urgent

Bob i like your idea and it looks right, but its not accepting it, it says
there is an error on the formula. F8 is being highlighted... what do i do?
--
please can you help... its urgent


"Bob Phillips" wrote:

They need to be ordered. Try VLOOKUP

=VLOOKUP(F8;{"Fusion",15;"Openzone",35.7;"Switch", 57;"Voip",78;"Maintanance"
,145;"CNE",34.7;"RCF",53;"CNI",7.8;"Featureline 1 Year",2.5;"Featureline 3
Year",6.6;"Featureline 5 Year",5.67;"Mobile Low Tier",8.78;"Mobile Low
Tier",1.45;"Mobile Low Tier",3.4;"Telephone System",5.7;"VAS -
StaticIP",7.53};2;FALSE)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Moh" wrote in message
...
Hi can someone help
i have created a spreadsheet to work out how much money people will make
from each product.

the 20 products are all in one cell (f8) in a drop down list format

so for e.g if i went to cell f8 and select a product from it e.g VOIP then
in the cell next to it, it should bring up the price of it.

also if my product has a space in it e.g Featureline 1 Year how do i put
that into the formula? AS IT SHOWS UP #N/A

this is what im using but it only works for the first 4 products.


=LOOKUP(F8,{"Fusion","Openzone","Switch","Voip","M aintanance","CNE","RCF","C
NI","Featureline
1 Year","Featureline 3 Year","Featureline 5 Year","Mobile Low
Tier","Mobile
Low Tier","Mobile Low Tier","Telephone System","VAS -

StaticIP";15,35.7,57,78,145,34.7,53,7.8,2.5,6.6,5. 67,8.78,1.45,3.4,5.7,7.53}
)


--
please can you help... its urgent

It could be NG wrap-around

=VLOOKUP(D6{"Fusion",15;"Openzone",35.7;"Switch",5 7;"Voip",7.8;
"Maintanance",145;"CNE",34.7;"RCF",53;"CNI",7.8;"F eatureline 1 Year",2.5;
"Featureline 3 Year",6.6;"Featureline 5 Year",5.67;"Mobile Low Tier",8.78;
"Mobile Low Tier",1.45;"Mobile Low Tier",3.4;"Telephone System",5.7;
"VAS - StaticIP",7.53};2;FALSE)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Moh" wrote in message
...
Bob i like your idea and it looks right, but its not accepting it, it says
there is an error on the formula. F8 is being highlighted... what do i do?
--
please can you help... its urgent


"Bob Phillips" wrote:

They need to be ordered. Try VLOOKUP


=VLOOKUP(F8;{"Fusion",15;"Openzone",35.7;"Switch", 57;"Voip",78;"Maintanance"
,145;"CNE",34.7;"RCF",53;"CNI",7.8;"Featureline 1 Year",2.5;"Featureline
3
Year",6.6;"Featureline 5 Year",5.67;"Mobile Low Tier",8.78;"Mobile Low
Tier",1.45;"Mobile Low Tier",3.4;"Telephone System",5.7;"VAS -
StaticIP",7.53};2;FALSE)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Moh" wrote in message
...
Hi can someone help
i have created a spreadsheet to work out how much money people will
make
from each product.

the 20 products are all in one cell (f8) in a drop down list format

so for e.g if i went to cell f8 and select a product from it e.g VOIP
then
in the cell next to it, it should bring up the price of it.

also if my product has a space in it e.g Featureline 1 Year how do i
put
that into the formula? AS IT SHOWS UP #N/A

this is what im using but it only works for the first 4 products.



=LOOKUP(F8,{"Fusion","Openzone","Switch","Voip","M aintanance","CNE","RCF","C
NI","Featureline
1 Year","Featureline 3 Year","Featureline 5 Year","Mobile Low
Tier","Mobile
Low Tier","Mobile Low Tier","Telephone System","VAS -


StaticIP";15,35.7,57,78,145,34.7,53,7.8,2.5,6.6,5. 67,8.78,1.45,3.4,5.7,7.53}
)


--
please can you help... its urgent

sorry bob i dont understand, what do u mean by NG? all i need is this formula
to work.
the only thing is stopin it from working is the 1st part =VLOOKUP(D6
have you actually checked this formula coz i think u have a mistake in it
somewhere.

Many thanks
Moh
--
please can you help... its urgent


"Bob Phillips" wrote:

It could be NG wrap-around

=VLOOKUP(D6{"Fusion",15;"Openzone",35.7;"Switch",5 7;"Voip",7.8;
"Maintanance",145;"CNE",34.7;"RCF",53;"CNI",7.8;"F eatureline 1 Year",2.5;
"Featureline 3 Year",6.6;"Featureline 5 Year",5.67;"Mobile Low Tier",8.78;
"Mobile Low Tier",1.45;"Mobile Low Tier",3.4;"Telephone System",5.7;
"VAS - StaticIP",7.53};2;FALSE)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Moh" wrote in message
...
Bob i like your idea and it looks right, but its not accepting it, it says
there is an error on the formula. F8 is being highlighted... what do i do?
--
please can you help... its urgent


"Bob Phillips" wrote:

They need to be ordered. Try VLOOKUP


=VLOOKUP(F8;{"Fusion",15;"Openzone",35.7;"Switch", 57;"Voip",78;"Maintanance"
,145;"CNE",34.7;"RCF",53;"CNI",7.8;"Featureline 1 Year",2.5;"Featureline
3
Year",6.6;"Featureline 5 Year",5.67;"Mobile Low Tier",8.78;"Mobile Low
Tier",1.45;"Mobile Low Tier",3.4;"Telephone System",5.7;"VAS -
StaticIP",7.53};2;FALSE)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Moh" wrote in message
...
Hi can someone help
i have created a spreadsheet to work out how much money people will
make
from each product.

the 20 products are all in one cell (f8) in a drop down list format

so for e.g if i went to cell f8 and select a product from it e.g VOIP
then
in the cell next to it, it should bring up the price of it.

also if my product has a space in it e.g Featureline 1 Year how do i
put
that into the formula? AS IT SHOWS UP #N/A

this is what im using but it only works for the first 4 products.



=LOOKUP(F8,{"Fusion","Openzone","Switch","Voip","M aintanance","CNE","RCF","C
NI","Featureline
1 Year","Featureline 3 Year","Featureline 5 Year","Mobile Low
Tier","Mobile
Low Tier","Mobile Low Tier","Telephone System","VAS -


StaticIP";15,35.7,57,78,145,34.7,53,7.8,2.5,6.6,5. 67,8.78,1.45,3.4,5.7,7.53}
)


--
please can you help... its urgent

You are right, I lost the first ; in my rewrite

=VLOOKUP(D6;{"Fusion",15;"Openzone",35.7;"Switch", 57;"Voip",7.8;
"Maintanance",145;"CNE",34.7;"RCF",53;"CNI",7.8;"F eatureline 1 Year",2.5;
"Featureline 3 Year",6.6;"Featureline 5 Year",5.67;"Mobile Low Tier",8.78;
"Mobile Low Tier",1.45;"Mobile Low Tier",3.4;"Telephone System",5.7;
"VAS - StaticIP",7.53};2;FALSE)


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Moh" wrote in message
...
sorry bob i dont understand, what do u mean by NG? all i need is this
formula
to work.
the only thing is stopin it from working is the 1st part =VLOOKUP(D6
have you actually checked this formula coz i think u have a mistake in it
somewhere.

Many thanks
Moh
--
please can you help... its urgent


"Bob Phillips" wrote:

It could be NG wrap-around

=VLOOKUP(D6{"Fusion",15;"Openzone",35.7;"Switch",5 7;"Voip",7.8;
"Maintanance",145;"CNE",34.7;"RCF",53;"CNI",7.8;"F eatureline 1
Year",2.5;
"Featureline 3 Year",6.6;"Featureline 5 Year",5.67;"Mobile Low
Tier",8.78;
"Mobile Low Tier",1.45;"Mobile Low Tier",3.4;"Telephone System",5.7;
"VAS - StaticIP",7.53};2;FALSE)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Moh" wrote in message
...
Bob i like your idea and it looks right, but its not accepting it, it
says
there is an error on the formula. F8 is being highlighted... what do i
do?
--
please can you help... its urgent


"Bob Phillips" wrote:

They need to be ordered. Try VLOOKUP



=VLOOKUP(F8;{"Fusion",15;"Openzone",35.7;"Switch", 57;"Voip",78;"Maintanance"
,145;"CNE",34.7;"RCF",53;"CNI",7.8;"Featureline 1
Year",2.5;"Featureline
3
Year",6.6;"Featureline 5 Year",5.67;"Mobile Low Tier",8.78;"Mobile
Low
Tier",1.45;"Mobile Low Tier",3.4;"Telephone System",5.7;"VAS -
StaticIP",7.53};2;FALSE)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Moh" wrote in message
...
Hi can someone help
i have created a spreadsheet to work out how much money people
will
make
from each product.

the 20 products are all in one cell (f8) in a drop down list
format

so for e.g if i went to cell f8 and select a product from it e.g
VOIP
then
in the cell next to it, it should bring up the price of it.

also if my product has a space in it e.g Featureline 1 Year how do
i
put
that into the formula? AS IT SHOWS UP #N/A

this is what im using but it only works for the first 4 products.




=LOOKUP(F8,{"Fusion","Openzone","Switch","Voip","M aintanance","CNE","RCF","C
NI","Featureline
1 Year","Featureline 3 Year","Featureline 5 Year","Mobile Low
Tier","Mobile
Low Tier","Mobile Low Tier","Telephone System","VAS -



StaticIP";15,35.7,57,78,145,34.7,53,7.8,2.5,6.6,5. 67,8.78,1.45,3.4,5.7,7.53}
)


--
please can you help... its urgent

I'd highly recommend you use a separate space (other sheet, if you want to,
or just a separate place on the same sheet, and just white color the text so
it's not visible). List down one column all your product names. List down
the adjoining column the values. Name that range whatever you like (let's
say "ProdLookup" for this example). Let's say your drop down is in cell A1.
Formula to use next to would be:

vlookup(a1,prodlookup,2,false).

English translation is that it will find a1 in the first COLUMN, then shift
over to the 2nd column of that range, and return the result. It will ONLY
find exact matches, because you say false, as opposed to not stating false or
putting in true, which makes the formula find the closest thing to it, and
then take the next value (in case of not an exact match...basically, you
pretty much always want to use false parameter).

Just FYI, an HLookup, would look in a row and then down, as opposed to down,
and across.

That should do it for you. If you wanted to go to your validation of A1 and
use the same list of products, you could name just the first column Products,
and have that be the list value in your validation.
--
Boris


"Moh" wrote:

Hi can someone help
i have created a spreadsheet to work out how much money people will make
from each product.

the 20 products are all in one cell (f8) in a drop down list format

so for e.g if i went to cell f8 and select a product from it e.g VOIP then
in the cell next to it, it should bring up the price of it.

also if my product has a space in it e.g Featureline 1 Year how do i put
that into the formula? AS IT SHOWS UP #N/A

this is what im using but it only works for the first 4 products.

=LOOKUP(F8,{"Fusion","Openzone","Switch","Voip","M aintanance","CNE","RCF","CNI","Featureline
1 Year","Featureline 3 Year","Featureline 5 Year","Mobile Low Tier","Mobile
Low Tier","Mobile Low Tier","Telephone System","VAS -
StaticIP";15,35.7,57,78,145,34.7,53,7.8,2.5,6.6,5. 67,8.78,1.45,3.4,5.7,7.53})


--
please can you help... its urgent