If then for list of 300 things

[mzrdmd]

New to the forum, first post, so forgive me if I don't have the etiquette quite right :-)

I am trying to do inventory control. I have the following entered into a spreadsheet:

A is the date
B is the room in which the product is to be used
C is the individual who requisitioned the item
D is the name of the item
E is how many of the item
F is a list of all products
G is a list of the cost of each unit of each product.

A B C D E F G
Date Room Who What How many Item Unit cost
12/3/12 ** bill Traps 4 A/W Tips 0.19
11/28/12 Dark Room bill Pan dup film 1 Alginate 11.09
11/28/12 Dark Room bill pan film 1 Amalgam Kit (Disposal ?) 146.00
11/13/12 Denture Karen Kromopan 1 Anterior triple trays 1.47
12/11/12 Denture Mike Kromopan 2 Articulating paper - xthin blue strips 1.56
9/19/12 Develop mike Duplicating film 1 box Base plate wax 0.50
9/21/12 Develop Mike Snippets 1 Bibs 0.06
10/18/12 JR/MS tim Monophase 1 Bibs - Rose 0.06
10/23/12 JR/MS bill Large gloves 1 Birex 4.13
10/30/12 JR/MS Dave JR gloves 1 Bite rims 0.72
11/5/12 JR/MS bill Optibond Solo 5 Bite sticks for panorex 0.50
11/5/12 JR/MS bill Etch 1 Bitewing tabs 0.02

Here is what I would like to do:
I want the item in D to have the value of that item from G automatically pop up beside it. I can insert another column between D and E for this. Then I want to multiply the value by the number of units, thus giving me an overall cost for that particular line.

Someone tried to help me on another forum, and I tried his formula, but can't get it to work. It was this: =IFERROR(VLOOKUP(A2, Sheet2!$A$2:$B$235, 6, FALSE), ""), but the columns were different, so the A2 and B235 will need to be changed, I'm guessing to F2 and G235, but I am the one asking for your help, so you tell me.

Once I am able to do this, I plan to break the data down into subsets by date, by room, by employee, etc., to generate many different reports, so typing all of this in by hand would be extensively time consuming and prone to data entry error, so if you can help me, you will have my utmost gratitude.

Thank you very much,

Mike Rice,
Johnstown, PA

[FDibbins]

Hi from Duncansville, Mike, welcome to the forum

Its always hard to see what data looks like when it gets copied here

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Aslo, if you have posted a similar question on another site, please include the link here

[mzrdmd]

Wow, that was fast !

Thanks for the instructions. I will try, but it seems to be over my head !

Mike

[mzrdmd]

Here is the link to the other thread that I posted on another site:

http://social.msdn.microsoft.com/For...c-b14b97f02a32

[mzrdmd]

Here is an example:

[mzrdmd]

I would like to have column G automatically calculated from the values in columns I and N, based upon what I type into columns D and E.

Thank you very much,

Mike

P.S.: Awesome instructions on how to upload the file ! :-)

[Wyliecody]

=LOOKUP(D5,I$2:I$14,N$2:N$14) try this, i did it in D5 for bibs because it was the first one I saw that matched. I assume you have a list of all the products you use similar to Columns i-p. if you do not you need to create one. if you do then the formula I provided should work for this issue.

[oeldere]

Or with VLookup fot the item bibs.

Please Login or Register  to view this content.

[Wyliecody]

forgot to mention you need to paste either formula in column F

[mzrdmd]

Thanks, everyone. Yes, I have a complete list of products as in columns i through q.
I think we have a winner ! Thank you VERY MUCH !!!!

Mike

[mzrdmd]

How do I mark this thread solved or closed ?

Thanks,

Mike