# formula to calculate inventory, vlookup?

1. ## formula to calculate inventory, vlookup?

Maybe vlookup is what I am looking for?

I have sheets with multiple columns, and 1000+ rows.

Column B is a SKU number. Column F is quantity on hand.

In a separate sheet (Sheet2) i want to look up each sku, and calculate the quantity on hand for every location that has that SKU.

Would be even BETTER, if I could get excel to list data from an extra column (column A is location)

example:

Sheet2 should look like

45568(sku)
(location) | (quantity)
(location) | (quantity)
(location) | (quantity)
_____________
Total quantity on hand

and I want to repeat this for every sku. sheet two would likely take up two columbs per sku, showing location in first, on hand in the second, then calculate a total

There u go

3. ## Re: formula to calculate inventory, vlookup?

I will look into this when I get home.

4. ## Re: formula to calculate inventory, vlookup?

I am unsure what to look for in your sample sheet, can you elaborate

5. ## Re: formula to calculate inventory, vlookup?

Still hoping someone can help with this.
I am attaching an example of what I need. Sheet1 will have data, and sheet2 I want to calculate totals by SKU. Like I said, I want excel to show me a list of locations with the SKU I choose, then show the amount on hand per location, then tally a total.

example.xlsx

6. ## Re: formula to calculate inventory, vlookup?

loc on hand
loc on hand

TOTAL ###
what does that mean?

7. ## Re: formula to calculate inventory, vlookup?

thats the data that I want to have shown. open sheet1 for the data ill be using.

8. ## Re: formula to calculate inventory, vlookup?

but it refers to nothing they are all the same

9. ## Re: formula to calculate inventory, vlookup?

See if the attached Pivot Table will give you what you want?

10. ## Re: formula to calculate inventory, vlookup?

See if this does as you need.
Select from the drop-down in Sheet2 A1

11. ## Re: formula to calculate inventory, vlookup?

ahh! thank you both!

Marcol, that is beautiful. Now I need to figure out how to apply that to my spreadsheets at work. I will investigate!

12. ## Re: formula to calculate inventory, vlookup?

before I mark this thread as SOLVED, can someone help me be able to implement the work that Marcol did? is it a pivot table? where do I need to start?

13. ## Re: formula to calculate inventory, vlookup?

It isn't a pivot table.

First you need a List of Unique SKUs, I used Remove Duplicates on a copy of your list in Sheet1 Column B
From that you can get the matching descriptions, and use that for the drop-down Validation List.

All this can be simplified by using dynamic named ranges to handle any variable amount of SKUs.

1/. Does each location have the same amount of SKUs?
2/. Can you have new and/or SKUs deleted?

14. ## Re: formula to calculate inventory, vlookup?

Not each location has the same amount of SKUS
I was able to work out the table on the right from your sheet2, but the dropdown list is giving me trouble

15. ## Re: formula to calculate inventory, vlookup?

Okay look back later tonight, I'll prepare a workbook for you, got to go for now.

16. ## Re: formula to calculate inventory, vlookup?

1/. First thing I would do is make your data continuous, don't have blank rows seperating your inventories for each location.
This makes analysing your data much easier. (See Sheet "StockTake").

2/. Add a new sheet for the list of unique SKUs and there descriptions, say Sheet "Lookups", this can be hidden

3/. Use Names to define the various ranges dynamically.(See the Names Manager.)
These will automatically adjust as your stocktake changes in size and/or values.

Name:= "SkuList"
Refers to:=
Formula:
`Please Login or Register  to view this content.`

Name:= "Unique_Skus"
Refers to:=
Formula:
`Please Login or Register  to view this content.`

Name:= "Description"
Refers to:=
Formula:
`Please Login or Register  to view this content.`

4/. In Sheet "Lookups" A2 this array formula, confirm with Ctrl+Shift+Enter before dragging down.
This returns all the SKUs found in your stocktake regardless of which location they fall in.
Formula:
`Please Login or Register  to view this content.`

And in B2, to get the SKU description, this standard formula
Formula:
`Please Login or Register  to view this content.`

5/. Use the name "Description" for your Data Validation list in Sheet "Report" A2
And in B2 to get the selected items' SKU
Formula:
`Please Login or Register  to view this content.`

6/. In A5 this CSE array formula, confirm with Ctrl+Shift+Enter before dragging down.
Formula:
`Please Login or Register  to view this content.`

Similarly in B5
Formula:
`Please Login or Register  to view this content.`

Referring to the SKU rather than the description should minimise the possibility of errors due to spelling mistakes.

7/. Finally in B3, dynamically sum B5 down to last QOH found.

This is bit of a ramble, see how you get on with it.

17. ## Re: formula to calculate inventory, vlookup?

Thanks again Margol for taking the time. I guess my example should have been more fitting to what I am going to use this formula for.
QOH is typically the F column. and each sheet that I work with has thousands of rows. Like I said, for the most part, each store will have the same SKUs, but some locations will have more or less depending on which are deactivated per location. I guess I just need guidance on how to format the dropdown and table that it produces.

Thank you!!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1