#1
|
|||
|
|||
Inventory-Trying to add numbers to a total based on the numbers above.
I'm trying to make an inventory spreadsheet that scans for numbers in the spreadsheet and then adds the amount of the said item to a total value in another sheet. There is a warehouse and products can be everywhere, but I do not know exactly which products will be where.
Numbers are written into said spreadsheet as 1001(ID);60(amount). Then underneath that I am pulling the numbers before the semicolon, and after the semicolon. These numbers are below in ID and AMOUNT sections in the spreadsheet. I just need a way to scan for all the numbers and then pull the number below it into a total value that when all the values for that product are pulled, will display the total amount. (Sorry for the convoluted and messy explanation).Keep in mind that the numbers may change and there will be around 800 different items. Sorry, I understand that this may be very easy, but I am very inexperienced when it comes to any sort of VBA code like this requires (or I believe requires) Add amount here is where I want the totals to be added, but I moved them 1 to the right to show this. The data should be entered correctly and what I want it to do is scan/search for a number, and based on the number, add it to the corresponding total value. Thanks, Daniel |
#2
|
|||
|
|||
Maybe like shown in the attached (Sheet SAMPLE).
|
#3
|
|||
|
|||
Thank you so much! I really appreciate it!
|
#4
|
|||
|
|||
A VBA option.
No need for anything on the SAMPLE sheet other than the original ID;AMOUNT range. The ID list and TOTALS are recalculated and written to columns M:N each time the command button is clicked. |
#5
|
|||
|
|||
Thanks, but some of the numbers are off when I add to the amount. They add but don't seem to be next to their corresponding ID value.
It seems to not work if it detects a space in one of the cells. Is there any way to fix this error? |
#6
|
|||
|
|||
You might want to mention what you're eluding to and/or who your response is for.
|
#7
|
|||
|
|||
Sorry NoSparks, what I am eluding to is the 1st sample sheet with the formulas. It seems to error when it detects a space or a box without a semicolon. (I am changing the selection area to fit what I need it for.) I am trying to make it "look" like a warehouse with title boxes that say A1C4 (Aisle 1 Column 4).
|
#8
|
|||
|
|||
My VBA suggestion eliminates the need of having any formulas on the 1st sample sheet, or anything other than the ID;Amount data, so I guess it isn't my suggestion you're asking about.
|
#9
|
|||
|
|||
I'm sorry. You're correct.
I just am not sure how to edit the VBA to make it to scan the area that I need it to as well as put the value in another cell. (I'm kinda a noob when it comes to VBA.) |
#10
|
|||
|
|||
That Aisle part was not indicated in your sample.
To accommodate this in my code change this line Code:
If arr(i, ii) <> "" Then Code:
If InStr(arr(i, ii), ";") <> 0 Then And what does your ID;Amount data really look like ? |
#11
|
|||
|
|||
Your version makes it so that it gives me the totals for all the numbers in the cell in a list form. I want it so that it give me the total based on the number in a specific cell like xor's function version. (Get input (1001) get total from (1001;60 and 1001;10 and 1800;60) and say the total for 1001 (70)).
Can you edit xor's for me to just ignore anything that doesn't have a semicolon? Again thanks for all your help with this. |
#12
|
|||
|
|||
The macro totals the amounts for the unique ID's on the sample sheet and puts them into a sorted list.
My line of thought being simple formulas on your ITEMTOTALS sheet to look at the 2 column list rather than looking at what you are attempting to use. I admit not understanding what your trying to achieve. Good luck with your project. |
#13
|
|||
|
|||
xor can you edit one of the functions to make it so that it looks at only numbers with semicolons?
|
#14
|
|||
|
|||
Thanks, I got it figured out.
|
Tags |
inventory, sum, vba |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Need Excel to recognize week numbers based on specific dates | Ktyler | Excel | 5 | 02-06-2017 11:08 AM |
Open a document based on a template, the page numbers in the TOC won't update | billy8b8 | Word | 2 | 10-04-2015 06:06 AM |
Search a cell that contains words and numbers and convert the numbers to metric | Carchee | Excel Programming | 36 | 10-08-2014 03:16 PM |
How do I refer to page numbers, when the numbers change as I prepare the document? | StevenD | Word | 5 | 11-29-2012 12:52 AM |
Create index based on bullet numbers | EZRider | Word | 0 | 07-10-2009 09:21 AM |