What I'm trying to do is build this lookup/index/match or whatever formula that will look at the warehouse, then pull the part and then display the quantity.
I'm a little confused regarding multiple lookups. I can make it find 1 value, but not another at the same time.
Please help, See attached Spreadsheet.
Hi,
If you only plan to do one look up then DGET() may suit your needs:
If you plan to do it many times then this array formula (CTRL+SHIFT+ENTER, not just ENTER) is an option:
Please Login or Register to view this content.
The &"" are necessary because your inventory parts/warehouse are numbers stored as text.Please Login or Register to view this content.
I guess I should have uploaded the actual file. I just created that one as an example.
See the attached files now please.
Anyone have anything on this?
Unless I'm missing something, the INDEX() array formula solution should be fine (needs slight adaptation of course)? Another option you might consider would be to pull the data into the sheet using a Pivot Table and then using GETPIVOTDATA() to pull the correct item into your column. This would be more efficient but it might mean that you have to refresh the pivot table.
If you want the total count of each part number from all wearhouses then
Sort your data on Item and apply this formula in E2 (array formula) and copied down.
This will add total at last entry of each part number.
If this is not you want show the results you want in sample sheetPlease Login or Register to view this content.
Thanks
Regards
Mark the thread as solved if you are satisfied with the answer.
In your first post under the thread tools.
Mahju
try this method and let us know
I have to build a XXXX_Inventory Upload Template_Upload.xlsx spreadsheet for each warehouse and convert it into a CVS to import into a ERP program.
Current I only have the warehouse count data for the 2300 branch and it's 4 warehouses.
What I want to do is change the warehouse number in the XXXX_Inventory Upload Template_Upload.xlsx spreadsheet and have it reflect the current count number from the spreadsheet (Inventory Counts_Upload.xlsx) for each part.
I have over 150 warehouses and over 1200 parts. Doing this manually is not the option
Charlie,
That's not working right.
I tried changing it to part number 1464 and location 2301 and got 0. It should be 11.
i tried it and it worked ok for me did you disturb the array formula? if so confirm the array by "control+shift and enter"
I tried typing in the numbers instead of using the drop downs.
However I don't think this solves my real problem
I need that =SUM((A2:A1048576=H3)*(C2:C1048576=G3)*(D2:D1048576)) to repeat all the way down Column AZ in 2300_Inventory Upload Template_Upload.xlsx (attached) while refering to that Inventory Counts Upload.xlsx file.
I'm not sure if this is what you need ...let me know
Ok I'm confused now. I'm not even really sure what you've got going on there.
Is there no way to perform the lookup into the other file from the field in Column AZ?
I think It might be at the point of creating a concatenated column in the parts count file and searching for a index/match on that.
Re: Lookup Quantity of Part by Warehouse
Thanks for your explanation
1. you have numbers formatted as text. Use them as number otherwise match
may not be found.
2. If it is not possible to change them into numbers use two extra columns and
use value function to change them (Suppose Col E and F)
now use the following formula in cell AZ2 in template workbook copied down (Array formula)
Inventory is the file containing your data Change as necessaryPlease Login or Register to view this content.
Now as you change the number 2300, 2301 etc stock will change
First open both files while entering formula
Regards
Last edited by mahju; 03-11-2011 at 11:16 AM.
Possible by this trick
Originally Posted by fasterthanyours
Some of my Part Numbers are alpha numeric, but it doesn't matter for those. Still works.
Mahja, So initially your new formula didn't work, but I played around with it and figured it out.
In Column AZ:
=SUMPRODUCT(('[Inventory Counts.xlsx]2300'!$A$2:$A$100000=A9)*('[Inventory Counts.xlsx]2300'!$C$2:$C$100000=AY9),'[Inventory Counts.xlsx]2300'!$D$2:$D$100000)
It's unknown if converting Column A and C from text to number made a difference. I may test it later.
Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks