re-try..excel wizards!

[studinuk]

hey all again..

having tried contacting a couple of you, i have been told its best t
be desciptive over here to allow a larger response, and just in cas
anyones got the same problem teh y can use the advice too. so here i a
with an indepth descrptive thread about what i am trying to do.

Basically i am desgning an ordering system for a takeaway. a custome
will come in order, where the staff behind the counter will use m
system to take the order.

my system is made up off 3 sheets. order sheet, delivery sheet an
warehouse sheet.

the order sheet has 3 coloumns:

(A)Product b) Price: (c)quantity.

when the product gets entered, i.e chips i would like the price to com
up itself.

So if in A1 the product coloumn, chips was entered i would like th
price to come up iteself. so ideally a forumla like

=VLOOKUP(A12,'Warehouse Products'!A14:B18,2,0)

would b used in B1.

However the possible products are devided into 2 groups.half th
products come from the warehouse and the other half is delviered.

so say if chips and fish was ordered. chips in A1 and fish in A2. fis
comes from the delviery sheet, so i would need a vlook formula linkin
it to the delviery sheet. however the chips are from the warehous
sheet. but the customer could order a product from any of the tw
sheets. is it possible to have a vlookup that will look up the ordere
product from both the delviery sheet and thewarehouse sheet? in thi
case the fish would could from one sheet and the chips from another
something like:

=VLOOKUP(A12,'Warehouse Productselivery Products'!A14:B17,2,0)

this does not work. on the warehouse and delviery product sheets ther
is a list of the relvant products from cells a14:b17.

hope this makes it all a liltle more clear. and i hope there is someon
that knows how to do this! thanx allot again guys.cheers

 

[RagDyer]

Just suggested a formula for the same question yesterday.

See if you can follow the suggestions in this link:

http://tinyurl.com/2vn2n

If you have a problem, post back with some specifics on your own formula
that you're having trouble with.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

hey all again..

having tried contacting a couple of you, i have been told its best to
be desciptive over here to allow a larger response, and just in case
anyones got the same problem teh y can use the advice too. so here i am
with an indepth descrptive thread about what i am trying to do.

Basically i am desgning an ordering system for a takeaway. a customer
will come in order, where the staff behind the counter will use my
system to take the order.

my system is made up off 3 sheets. order sheet, delivery sheet and
warehouse sheet.

the order sheet has 3 coloumns:

(A)Product b) Price: (c)quantity.

when the product gets entered, i.e chips i would like the price to come
up itself.

So if in A1 the product coloumn, chips was entered i would like the
price to come up iteself. so ideally a forumla like

=VLOOKUP(A12,'Warehouse Products'!A14:B18,2,0)

would b used in B1.

However the possible products are devided into 2 groups.half the
products come from the warehouse and the other half is delviered.

so say if chips and fish was ordered. chips in A1 and fish in A2. fish
comes from the delviery sheet, so i would need a vlook formula linking
it to the delviery sheet. however the chips are from the warehouse
sheet. but the customer could order a product from any of the two
sheets. is it possible to have a vlookup that will look up the ordered
product from both the delviery sheet and thewarehouse sheet? in this
case the fish would could from one sheet and the chips from another?
something like:

=VLOOKUP(A12,'Warehouse Productselivery Products'!A14:B17,2,0)

this does not work. on the warehouse and delviery product sheets there
is a list of the relvant products from cells a14:b17.

hope this makes it all a liltle more clear. and i hope there is someone
that knows how to do this! thanx allot again guys.cheers.

 

[Guest]

No one cares about spelling/typos, but it does help to
use proper grammer also.

 

[Frank Kabel]

Hi
some ways:
1. Using combined VLOOKUPS:
=IF(ISNA(VLOOKUP(A12,'Warehouse
Products'!A14:B18,2,0)),IF(ISNA(VLOOKUP(A12,'Delivery
Products'!A14:B18,2,0)),"No match",VLOOKUP(A12,'Delivery
Products'!A14:B18,2,0)),VLOOKUP(A12,'Warehouse Products'!A14:B18,2,0))

2. You may also try to download the free add-in Morefunc.xll
(http://longre.free.fr/english). The function THREED converts a 3D
array to a 2D array. This may work (the help of this function at least
suggested it). e.g. you may use the following formula
=IF(ISERROR(VLOOKUP(A12,THREED('Warehouse Productselivery
Products'!A14:B17),2,0)),"no match",(VLOOKUP(A12,THREED('Warehouse
Productselivery Products'!A14:B17),2,0)))