<HR style="COLOR: #ffffff; BACKGROUND-COLOR: #ffffff" SIZE=1> <!-- / icon and title --><!-- message -->
I am trying to link 2 excel Workbooks together and I am wondering if using the SUMIF Function is the best way to do this. I will try to explain and illustrate my position.
First I have a workbook called Master where I import my companies inventory and prices. It contains ALL our inventory. Looks like this:
(Column A) (Column B)
(Item Code
(Item Price
(ROW1) THUMBRID $5.00
(ROW2) THUMTUBE $2.00
(ROW3) ZPIN $0.30
Now this master sheet is changing all the time as we import new products and prices all the time. So, this is a "list" where I can sort it by Item Code alphabetically.
Now, I am trying to make another workbook with a list of what each of our kits contain and there prices. I will call this ThumbPiano. Like this:
Column A: COLUMN B:
THUMBRID $5.00
THUMTUBE $2.00
We have many different kits, that only take a couple of products, and our inventory list is at like 3,400. I want to be able to go into the Master workbook and update an Item price and have it automatically update it in whatever workbook contains those items. But how can I do this if I add an item into the workseet. Then the cells move and whatever data was in that cell before is not there now.
So, that is why I chose the SUMIF function. Maybe there is a better one? Here's what I tried...assuming the two lists before, and the code Zpin is extra in the Inventory list because we do not use it in the thumb piano hardware pack.
I tried:
=SUMIF([Master.xls]Sheet1!$A$1:$A$3,A1:A2,[Master.xls]Sheet1!$B$1:$B$3)
It works, but then when I save it and close out of it and then re-open the ThumbPiano wokbook it asks me if I want to update the sheet. I click yes, and then all the cell says "#VALUE!" "A value used in the formula is of the wrong data type." Please let me know what is going wrong. I know there has to be a way to do this. I just wonder if the SUMIF formula is not the way to link 2 workbooks together...but if it isn't please keep in mind that I have this looking through 3,400 parts and I will be adding to them all the time, so it needs to search through all those parts and display the correct price....
I have been told to use the VLOOKUP Function, but I cannot figure this out. If someone wouldn't mind...I'd like to email both these workbooks to you and have you do one of the links for me and email them back to me so I can see what you did. I learn best that way, and honestly, I am just going out of my mind. I cant think anymore on how to do this!!! Please! I need this for work!! Thanks!!!
I am trying to link 2 excel Workbooks together and I am wondering if using the SUMIF Function is the best way to do this. I will try to explain and illustrate my position.
First I have a workbook called Master where I import my companies inventory and prices. It contains ALL our inventory. Looks like this:
(Column A) (Column B)
(Item Code
(Item Price(ROW1) THUMBRID $5.00
(ROW2) THUMTUBE $2.00
(ROW3) ZPIN $0.30
Now this master sheet is changing all the time as we import new products and prices all the time. So, this is a "list" where I can sort it by Item Code alphabetically.
Now, I am trying to make another workbook with a list of what each of our kits contain and there prices. I will call this ThumbPiano. Like this:
Column A: COLUMN B:
THUMBRID $5.00
THUMTUBE $2.00
We have many different kits, that only take a couple of products, and our inventory list is at like 3,400. I want to be able to go into the Master workbook and update an Item price and have it automatically update it in whatever workbook contains those items. But how can I do this if I add an item into the workseet. Then the cells move and whatever data was in that cell before is not there now.
So, that is why I chose the SUMIF function. Maybe there is a better one? Here's what I tried...assuming the two lists before, and the code Zpin is extra in the Inventory list because we do not use it in the thumb piano hardware pack.
I tried:
=SUMIF([Master.xls]Sheet1!$A$1:$A$3,A1:A2,[Master.xls]Sheet1!$B$1:$B$3)
It works, but then when I save it and close out of it and then re-open the ThumbPiano wokbook it asks me if I want to update the sheet. I click yes, and then all the cell says "#VALUE!" "A value used in the formula is of the wrong data type." Please let me know what is going wrong. I know there has to be a way to do this. I just wonder if the SUMIF formula is not the way to link 2 workbooks together...but if it isn't please keep in mind that I have this looking through 3,400 parts and I will be adding to them all the time, so it needs to search through all those parts and display the correct price....
I have been told to use the VLOOKUP Function, but I cannot figure this out. If someone wouldn't mind...I'd like to email both these workbooks to you and have you do one of the links for me and email them back to me so I can see what you did. I learn best that way, and honestly, I am just going out of my mind. I cant think anymore on how to do this!!! Please! I need this for work!! Thanks!!!
