Vlookup:how are you going to guess the lowest price ...?

**thomas68** · 03-09-2007, 08:40 AM

Hi I am new in this forum, I really hope you all can help me solve a problem.
OK.....This is what I would like MS Excel to do:

I have a list of items with relatives prices, I would like whenever I type the item name (on another excel document, not in the same workbook)....its price will appear on a defined cell of another workbook.

Do you think this is possible to set it or Am I just dreaming for an easy life?

Somebody told me about the V LOOKUP formula, but,being new I don't really know how it works and if it will work.

thank you in advance for your time and help.

Thomas

**starguy** · 03-09-2007, 08:52 AM

Originally Posted by thomas68

Hi I am new in this forum, I really hope you all can help me solve a problem.
OK.....This is what I would like MS Excel to do:

I have a list of items with relatives prices, I would like whenever I type the item name (on another excel document, not in the same workbook)....its price will appear on a defined cell of another workbook.

Do you think this is possible to set it or Am I just dreaming for an easy life?

Somebody told me about the V LOOKUP formula, but,being new I don't really know how it works and if it will work.

thank you in advance for your time and help.

Thomas

VLOOKUP may solve your problem.
see link below to understand its working.

http://www.contextures.com/xlFunctions02.html

however if it does not solve your problem, you should brief about your data with cell references.

**Carim** · 03-09-2007, 08:59 AM

Hi,

If you are working with separate workbooks, you will have to ask yourself if you would be working with opened or closed workbooks ... since the solutions will differ ...

**thomas68** · 03-09-2007, 09:08 AM

Hi Carim,
Thanks for your reply, unfortunately I am new with excel

so I don't really know if it is better to work with open or close workbook

.
I am a chef,and I just would like whenever I put down my list of ingredient (on another workbook) not to always have to open the price list and check the price.I hope this explanation will help you to help me.

thank you very much

regards

thomas

**Carim** · 03-09-2007, 09:20 AM

Hi,

In order to get data from closed files, in an easy way ...

My recommendation is to download Laurent Longre's add-in, and use the function indirect.ext

http://xcell05.free.fr/downloads/Morefunc.zip

**thomas68** · 03-10-2007, 05:43 AM

Hi Carim

I am sorry to say that I don't know where to start from the link you gave me, unfortunately thats a bit to difficult for my knowledge.As I said I am knew with EXCEL.
I'll try to explain what I want to do in an easier way:

I am a Chef and I have a Excel document with all the ingredients and prices
Whenever I need to calculate the expenses for my dishes I open another EXCEL document and I write all that I need.
I would like in some way that whenever i write the ingredient I' ll be able to have its price on a particular cell.
GENERAL PRICE LIST.zip

What should I do ?
Would you all be so kind to give me a step-by-step.??

Am I dreaming of an easy life?

Thank you so much for your time

regards

thomas

**Carim** · 03-10-2007, 06:28 AM

Hi Thomas,

I fully understand your final objective ...
But, given your present Excel knowledge, my recommendation would be to build exactly what you need within the same Excel workbook ...
Once you fully master this solution, you will then move to separate workbooks

Regarding recipe costing, if you are looking for ideas ...
http://www.rrgconsulting.com/page/RRG/PROD/SS/MENUCOST

**thomas68** · 03-10-2007, 01:26 PM

Hi Carim,
thank you for your suggestions and link.

ok than,how can I build exactly what I need within the same Excel workbook?

would you be so kind to give me kind of step-by-step??

I followed the link,very interesting,I know some other software but I wanted to save some money doing it myself.

Regards

thomas

**Carim** · 03-10-2007, 01:33 PM

First things first ...

1. In your workbook, select a second worksheet and build your template ...
2. Then will come the time to write the various formulae, based on the requirements of your template ...

**thomas68** · 03-10-2007, 03:56 PM

Originally Posted by Carim

First things first ...

1. In your workbook, select a second worksheet and build your template ...
2. Then will come the time to write the various formulae, based on the requirements of your template ...

sorry Carim, it seams all so difficult.....if you don't have time to explain it to me with a step-by-step....could you please give me some links where I can try to find some information??

Thank you for your time

Regards

thomas

**Carim** · 03-11-2007, 04:51 AM

Hi,

Select A2 and B1 from the drop-down boxes, and you will get price in B2 ...

**thomas68** · 03-12-2007, 06:54 AM

Hi Carim, I really am thankful for all your help sorting this matter.As I have said before my knowledge in excel is a bit more than basic.
I don't understand your idea of my requirement. Your zip file is ok but I don't think this is what I really need.
This is an example of one of my work.
Test.zip

Once I type in A2 I would like to have just the prices available appearing in C1 so that I just have to click on it to chose it. Is this clear enough or am I making it more difficult.

If you think that this is too difficult for me just let me know if there are any tutorial around. i was able to set my web site following tutorials I think I will be able to do this as well.

Thank you once again.

Regards

thomas

**Carim** · 03-12-2007, 07:00 AM

Hi Thomas,

If you do not mind ...
The MOST important thing to know, when using Excel, is the following :
Each piece of data is to be stored in its own cell ...

e.g. in cell C6, typing : £1.50 each
prevents you from typing in cell D6 : =B6*C6 which will produce the result you need i.e. £75 ....

**Carim** · 03-12-2007, 07:12 AM

Hi again,

See attached example ...
Change quantity or price to see end result adjust itself ...
Welcome to Excel ...

**thomas68** · 03-12-2007, 07:25 AM

Hi,
The example is perfect, now,
when I type in say A6 and I want to know the best price from the suppliers ( which are in the main Price List)what kind of formula or magic trick should I do to have in G6 a drop down menu of only the suppliers that sell that particular item?

Thank you for your patience.
May God Bless you abundantly

Thomas

**Carim** · 03-12-2007, 07:36 AM

Regarding your price list, the same logic applies ...
only prices in the different without comments or text ...

Regarding prices do you want the lowest price to automatically appear ...?
With the list of suppliers, how are you going to guess the lowest price ...?

**Carim** · 03-12-2007, 07:55 AM

Hi again,

Attached is your worksheet with a formula for lowest price ...
Hope this will be a good starting point ...

**thomas68** · 03-14-2007, 01:06 PM

Hi Carim,
Sorry for the late reply, I have been a bit busy.
Your zip example is ....WOWWWWW
I am trying to find out if I can copy and paste the formula only once all over the list of the ingredients. Regarding your questions, yes,I would like the lowes price to appear automatically, and for the second question.......I don't know if there is a way. But I must say.......thank you very very very much for all your help.

it means that whenever I want to write down a menù I just have to copy that formula.

Thanks thanks and thanks again.

If you have any suggestion please feel free to let me know. It would be just fantastic.

regards

thomas

**Carim** · 03-14-2007, 01:09 PM

Quite glad it is helping you out ...

If you design your project step-by-step, I am sure you will succeed ...
Should you need further assistance, do not hesitate to post back ... there are plenty of talented people who can give you a hand ...

Thanks for your feedback ...