# Inventory using drop-down lists

1. ## Inventory using drop-down lists

Hello all,

I am trying to build a simple inventory form for the company I work for. What we need is a spreadsheet that will keep a running total of inventory between 3 different locations. There will be several products to keep track of. The "location" tabs need to report the current number in inventory to the "inventory" tab. We are using drop-down lists on the "location" tabs to choose the products. What I need help with is getting the "inventory" tab to calculate the number based on the product selected in the drop-down.

I hope this makes sense. I have attached a sample of what I am trying to achieve.

2. ## Re: Inventory using drop-down lists

This would form the basis of your formula...
=SUMIF(Alliance!\$E:\$E,Inventory!\$A2,Alliance!\$F:\$F)
You would then just need to adjust the sheet name for each location (do this just for the 1st cell, then copy it down

I "could" set this up using INDIRECT so that you dont need to change the sheet ref each time, but if you have a LOT of locations and a LOT of products, that could slow your file down. I will give you the formula anyway, and you can try it - if it slows you down too much, use the 1st option...
=SUMIF(INDIRECT(B\$1&"!\$E:\$E"),Inventory!\$A2,INDIRECT(B\$1&"!\$F:\$F"))

3. ## Re: Inventory using drop-down lists

That is exactly what I needed! Thank you so much!

4. ## Re: Inventory using drop-down lists

Happy to help

Which option did you use?

5. ## Re: Inventory using drop-down lists

I'm looking for a way to notify the users whether quantity numbers should be positive or negative based on the transaction type. Would you happen to know a way to handle that?

Thanks!

6. ## Re: Inventory using drop-down lists

I used the first one. Worked perfectly.

7. ## Re: Inventory using drop-down lists

Originally Posted by m_buskirk
I'm looking for a way to notify the users whether quantity numbers should be positive or negative based on the transaction type. Would you happen to know a way to handle that?

Thanks!
What would some expected answers look like?

8. ## Re: Inventory using drop-down lists

I want the spreadsheet to specify that quantity needs to be positive or negative based on the selected transaction type.

Ex: Sale = negative number; Return = positive number

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