Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Adding and Subtracting quantity to an inventory list

Hello,

I am trying to create a list of inventory that I can add and subtract quantities to reflect our stock.

I did a search and I believe this thread is pretty much what I want:
http://www.excelforum.com/excel-prog...-function.html

The problem with that is that the document is in Spanish and I have forgotten everything but maybe a few numbers from the two years that I have taken x many years ago.

Background:
I know absolutely nothing about code!

What I would Like:
- to have either something on the top of the list or a userform to input the quantity that I am adding or subtracting.
- to be able to select the "bulb number" from a dropdown list, then type in a quantity that I am either adding or subtracting to the current quantity, select whether I am adding or subtracting, and then a "go" button.
- as a bonus, having a history list in another sheet would be great, with a date/time stamp of the change to inventory.

I would also like it to be simple enough for me to add new products to the list later on.

I also plan to be able to click on the "bulb number" and pull up a .pdf spec sheet of the bulb, but I should be able to do that...

I'm attaching what I have so far, which is not much:
IR BULB LIST.xlsx

Please Advise!
And Thank You!


Post your answer or comment

comments powered by Disqus
Hi everyone,
I have an invoice as one sheet and an inventory list as another sheet in the
same workbook. I would like to be able to select items from the inventory
list and have that info. (about 3 columns from 8) sent directly to the
invoice in order to generate a bill, however, the items are not necessarily
consecutive in the list. Is there a way to do this?
Thanks for any help!!

I'm using Excel 2003.

Is is possible to create a worksheet form where the user enters data, saves
the data and the data is then saved to the last row in an Excel list?
Once the data is saved by the end user, I would like the form to refresh so
that the end user can continue entering additional data.

Does anyone know of any documentation or examples I could review regarding
the above as I have never done this before?

TIA.

I need help with adding and subtracting times. Our vacation is based on 1.54 hrs / week and I would like to keep track of this. How can I do this? When I take a 8.0 hr vacation day and then add back my 1.54 hrs received.

Hi all -

Im trying to write some to code to filter data and copy results to an ongoing list of unknown length

I guess I'm not not quite setting the next cell for data

Errors here

	VB:
	
 wsDump 
    .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Activate 
End With 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks
-marc


	VB:
	
 pt_FilterSushi() 
     'Get_Rows is UDF
     'Globals
     'wsBook
     'wsData
     'wsFormulas
     'wsDump
    Dim wsDump As Worksheet 
     
    Dim Rng As Range 
    Dim rngCopyTo As Range 
    Dim rngCopyFrom As Range 
     
    Application.ScreenUpdating = False 
    Application.Calculation = xlCalculationManual 
    Application.DisplayAlerts = False 
     
    Set wbBook = ThisWorkbook 
     
    With wbBook 
        Set wsData = .Worksheets("Data") 
        Set wsFormulas = .Worksheets("Formulas") 
        Set wsDump = .Worksheets("Dump") 
    End With 
     
    With wsData 
        Set Rng = .Range("A1:H" & Get_Rows) 
    End With 
     
    With Rng 
        .AutoFilter Field:=8, Criteria1:=103 
    End With 
     
    With wsData 
        Set rngCopyFrom = .Range("A1:H" & Get_Rows).SpecialCells(xlCellTypeVisible) 
    End With 
     
    With wsDump 
        .Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Activate 
    End With 
     
    With wsDump 
        Set rngCopyTo = ActiveCell 
    End With 
     
    rngCopyFrom.Copy rngCopyTo 
     
    With Rng 
        .AutoFilter 
    End With 
     
     'Reset / Cleanup
    Set wbBook = Nothing 
    Set wsData = Nothing 
    Set wsTotals = Nothing 
    Set Rng = Nothing 
    Set rngCopyTo = Nothing 
    Set rgnCopyFrom = Nothing 
     
    Application.ScreenUpdating = True 
    Application.Calculation = xlCalculationAutomatic 
    Application.DisplayAlerts = True 
     
     
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I need to add a blinking red, yellow and green light to an excel spreadsheet.
I've inserted pictures from clip art. Is there anyway to animate the
pictures to make them blink in the spreadsheet?

If anyone has any sugestions on how I can create an exel spread sheet to add
and subtract inventory please let me know.

Dear All,

I am new working with excel and I need help to create an inventory list wich updates the curent inventory and year to date total products manufactured. I have an Excel 2007
Assume the following:
I have an inventory list of 100 items listed in column C. Each item varies in quantity starting as a balance forward (original inventory).
Example: Item number 5, there are 56 pieces in stock (balance forward) which is listed in E5 and for item number 6, there are 48 pieces in stock (balance forward) which is listed in E6 and so on to the end of the list...

In column C5 I have the following item description: Soap Bar Lime 6oz
In column E5 I have the existing previous inventory quantity listed in number of pieces eg. 56
In column F5 I enter/add the new products manufactured in number of pieces eg. 44
In column G5 I enter/subtract the quantity sold in number of pieces eg. 22
In column H5 I need to get the updated quantity of inventory which in this case would be 78

In column J, I need to get the year to date products manufactured for each product which was entered in column F row... and added each time a new product was manufactured eg. 326 (pieces of soap)
I need to keep these 2 running balances on column H (curent inventory) and in column J (year to date manufactured) while I can put new/fresh data in columns F and G.
So after I make a new entry in any cell in columns F and G and I hit enter, the cell should clear out and leave room for my next entry.
In column K, I need to get the year to date sold fo each row eg. cell K5 is 248 (pieces of soap) .

I am attaching the worksheet which is not working yet... Any help would be appreciated.

Thank You

I am new working with excel 2007 and I need help to set up an inventory list.
Assume the following:
I have an inventory list in column D of 100 items with varying quantities starting as balance forward.
Example: For item 1, there are 20 pieces in stock (balance forward) and for item 2, there are 31 pieces in stock (balance forward) and so on...

Column E5 has the balance forward in it (existing previous inventory)
Column F5 has the add amount in it (new goods manufactured)
Column G5 has the delete amount in it (goods sold)
Column H5 has the resulting balance in it. (result of E+F-G=H)
Column J5 has the year to date goods manufactured (added from F5)

I need a formula/code that copies the result of adding and deleting inventory (E5 & F5) to a new balance forward (H5) and then clearing the contents of the add and delete cells (F5 & G5) so every new entry in columns F5 & G5 is a continuation of the previous. This way I can input new goods manufactured in F5 and also I can input products sold in G5 and keep inventory total in H5.
On top of this, I need a column eg.: Column J5 that keeps a yearly total of the goods manufactured column in column F5 and it totals the quantity produced per year. This column I need to reset at the beginning of each year.

Thank you in advance

Here is the issue. I am trying to beef up my company's bid sheet. Basically I just want for the user to be able to select from a list of materials and add them to the bid sheet. Initially, I tried using data validation to create drop down lists but am not crazy about the limited functionality of the lists (i.e. you can't scroll with the mouse wheel once you bring down the drop down list and you can't begin typing the name of what you want and it automatically take you to that item, etc).

So, I was going to try to use combo boxes since they have these added features. I figure the best way to do this is have one combo box at the top to select an item, then create a button to add that item to the material listing.

I have attached an example worksheet:

Basically, I would like to have an "Add Material" Section added above the table on the "Price Sheet" tab which has a combo box that is linked to the Material Description on the "Materials" tab. Once the user selects the item they desire, I would like to have a button linked to a macro to take the selected item and adds it to the Material list in the bid table on the "Price sheet Tab" in the field in column D - Material Description. Preferably I would like to add this row to the bottom of the list that is there (so in this instance, added after row 6)

I do have a few preferences for the formating of some of the columns for each new record added. I want to keep the Qty column (column B) to be left blank, but I would like for the formulas in columns C, F, & H to to persist in the same columns once the new material is added. Additionally, I would like the @ and = in columns E & G to be there as well. I left one row of information so you will have visibility to the formulas, etc. of how I want the new field to be added.

Is this possible to do using Macros? Or am I pushing the limits of excel and should be using Access for this type of use? Trying to avoid having to purchase upgraded Microsoft Office Suites for the company if I can.

Excel Help3.xlsm

(Excel 2007)
Thank you in advance for any help..I'm new to this and am trying to learn as much as I can.

What I'm trying to create is a way to populate a quick inventory list with my bar-code scanner.

I've already learned that in order to have excel move to the next row each time a 12-digit SKU number is scanned, I'm going to have to utilize a userform text box.

My problem:
I don't even know how to begin coding the text box to accept a 12digit number, enter it into the next available blank cell (I'd like it to populate vertically) and then ready itself to accept the next available 12digit number.

My desired end-result:
My scanner is attached to a lengthy USB-Extension, so I can roam my store and quickly scan the inventory on display. For that reason, running back to my laptop and striking "Enter" each time makes the whole solution pretty useless.
Once finished, I'm assuming I could use excels count feature to tell me the quantity of the items I have on hand.

Thanks again for any insight.

-Bob

Maybe someone can help me. I have been trying to work on this for some
time and I can't figure it out. I want to create an inventory list
which tracks the number of items in inventory, items sold, and a total
of items sold. For instance,
I want my list to have the following headings (or something similar) as
shown below in cells C2:E2.

Inventory Quantity Sold Total Quantity Sold
6

In cells C3:E3 I would like to enter the starting inventory (say 6
chairs). Then everytime a chair sells I would like to be able to enter
in cell D3 the number sold and have it total it in cell E3 under the
heading Total Quantity sold. I guess the tough part is to have cell D3
act independently so everytime another chair sells I can input it under
cell D3 and in cell E3 it will remember all previous chairs sold as
well. Can anyone help me with this or is this formula not possible?

--
snoopy
------------------------------------------------------------------------
snoopy's Profile: http://www.excelforum.com/member.php...o&userid=16840
View this thread: http://www.excelforum.com/showthread...hreadid=484064

I have a list to which I have autofiltered. I want to add data to an adjacent
column so that only the visible rows have the data added (??) For example,
when I filter a column, it goes from 150 rows to, say, 50 rows. I then want
to past in an adjecent column 50 rows of data,which correspond with the 50
visible rows. Unfortunately, it copies as if there is no filter and places
the new column values to both the visible and hidden rows.

How can I ensure that the pasted column go into only the visible rows??

I hope this makes sense, and thanks in advance for your help

regards

Phil

Would be great if some one could help. I have an inventory list which
would have over a 100 different products on it. These products are
distributed to different departments in my business. Sheet 1 I type in
the department name then part number and how many items, then vlookup
brings up the info. But I can not work out how to subtract the amount I
just entered from the inventory list which is in sheet 2. The same
product can go to many departments. Have tried this but will only work
on one line. =IF(Sheet1!A1:A100=100,(Sheet2!C1-(Sheet1!D1100)))
"100" being the part number. Thanks in advance.

Hi guys, is there a way I can link an acknowledgement form and my inventory list together so that when a user acknowledges (for example when I save the acknowledgement form) that he has loaned an item it will take the item's name in a specific cell and go to my seperate inventory list and search for the item's name in a column and when found, updates that row's "Owner" column to his name that is on the acknowledgement form. Both are excel spreadsheets.
Any help would be very much appreciated. Thank you!

Hello,

First of all, I have very very little experience with Excel, and English is not my first language, so excuse me if I use the wrong terminology.

Could someone tell me how to make a sheet with the option to add or subtract from a number. For example I have "120", I enter "45" in a field, and the quantity changes to "165".

There would be a whole list of numbers, and I would need a field to add or subract from each one. Is this possible? If not, what other option do I have in Excel (so I won't have to calculate and change all the numbers myself)?

Thanks

Maybe someone can help me. I have been trying to work on this for some time and I can't figure it out. I want to create an inventory list which tracks the number of items in inventory, items sold, and a total of items sold. For instance,
I want my list to have the following headings (or something similar) as shown below in cells C2:E2.

Inventory Quantity Sold Total Quantity Sold
6

In cells C3:E3 I would like to enter the starting inventory (say 6 chairs). Then everytime a chair sells I would like to be able to enter in cell D3 the number sold and have it total it in cell E3 under the heading Total Quantity sold. I guess the tough part is to have cell D3 act independently so everytime another chair sells I can input it under cell D3 and in cell E3 it will remember all previous chairs sold as well. Can anyone help me with this or is this formula not possible?

Would be great if some one could help. I have an inventory list which
would have over a 100 different products on it. These products are
distributed to different departments in my business. Sheet 1 I type in
the department name then part number and how many items, then vlookup
brings up the info. But I can not work out how to subtract the amount I
just entered from the inventory list which is in sheet 2. The same
product can go to many departments. Have tried this but will only work
on one line. =IF(Sheet1!A1:A100=100,(Sheet2!C1-(Sheet1!D1:D100)))
"100" being the part number. Thanks in advance.

Hello,

I´m trying to build an inventory form where once i click a capture button, it subtracts or adds to an inventory table. Im having trouble figuring out how to use the for function.. here`s the code and a brief explanation of where i know i`m failing but having figured out. Please pardon my writing if it sucks, I`m mexican.

Here is what i pretend to achieve with this button
1) subtract or add to an inventory list, depending if im receiving a product or releasing one..
Therefore i have ,per say ,4 comboboxes with the product listing to pick, and 4 other comboboxes on the side to select the quantity of each product i select. The first problem arises when I only select one product, the 2nd combobox product list is left empty. As you will see in my code, im always looking for the combobox list in a list i have in my worksheet and then subtracting or adding depending the case. When my code is running and i only pick 1 product rather than 2,3,4 or 5 possibilites i have on my userbox, i get an error because it tries to make an operation with no values.. I hope I explained myself ok.. Im very new with VB so sorry if i suck.

2)The 2nd part of my code tries to carry this history to another worksheet. ( was working fine but now it`s not

3)next I want to clear the user form but ,I`m not getting that, I was yesterday but don`t know what happened.

4) I`m also trying to carry the history to 2 different sheets by using a multipage in my userform. one page for arriving parts , and another for leaving parts.. any advice on how to do this?

Here`s the code.


Dim r As Range
Dim i As Range
Dim RowCount As Long

Set r = Range("A2", Range("A65536").End(xlUp))


' Aqui resto o sumo a inventario de acuerdo al 1er elemento.
For Each i In r
If i = ComboBox1.Value And OptionButton1 = True Then
i.Offset(0, 1) = i.Offset(0, 1) + CBcant1.Value
Else
If i = ComboBox1.Value And OptionButton2 = True Then
i.Offset(0, 1) = i.Offset(0, 1) - CBcant1.Value

End If
End If

Next i

' Aqui resto o sumo a inventario de acuerdo al 2do elemento.
For Each i In r
If i = ComboBox2.Value And OptionButton1 = True Then
i.Offset(0, 1) = i.Offset(0, 1) + CBcant2.Value
Else
If i = ComboBox2.Value And OptionButton2 = True Then
i.Offset(0, 1) = i.Offset(0, 1) - CBcant2.Value

End If
End If

Next i

' Aqui resto o sumo a inventario de acuerdo al 3er elemento.
For Each i In r
If i = ComboBox3.Value And OptionButton1 = True Then
i.Offset(0, 1) = i.Offset(0, 1) + CBcant3.Value
Else
If i = ComboBox3.Value And OptionButton2 = True Then
i.Offset(0, 1) = i.Offset(0, 1) - CBcant3.Value

End If
End If

Next i

' Aqui resto o sumo a inventario de acuerdo al 4to elemento.
For Each i In r
If i = ComboBox4.Value And OptionButton1 = True Then
i.Offset(0, 1) = i.Offset(0, 1) + CBcant4.Value
Else
If i = ComboBox4.Value And OptionButton2 = True Then
i.Offset(0, 1) = i.Offset(0, 1) - CBcant4.Value

End If
End If

Next i

' Aqui resto o sumo a inventario de acuerdo al 5to elemento.
For Each i In r
If i = ComboBox5.Value And OptionButton1 = True Then
i.Offset(0, 1) = i.Offset(0, 1) + CBcant5.Value
Else
If i = ComboBox5.Value And OptionButton2 = True Then
i.Offset(0, 1) = i.Offset(0, 1) - CBcant5.Value

End If
End If

Next i

' Aqui me aseguro que se llenen los campos que quiero
If Me.TextBox6.Value = "" Then
MsgBox "Favor de Llenar campo de Solicitante.", vbExclamation, "Sistema de Almacèn Rocaacero"
Me.TextBox6.SetFocus
Else
Cells(49, 5).Value = Cells(49, 5).Value + 1
Exit Sub
End If

If Me.ComboBox6.Value = "" Then
MsgBox "Favor de Llenar campo de Almacenista.", vbExclamation, "Sistema de Almacèn Rocaacero"
Me.ComboBox6.SetFocus
Exit Sub
End If
 
 
' Historial
 
RowCount = Worksheets("Hoja3").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Hoja3").Range("A1")
.Offset(RowCount, 0).Value = Me.TextBox6.Value
.Offset(RowCount, 1).Value = Me.ComboBox1.Value
.Offset(RowCount, 2).Value = Me.TextBox12.Value
If OptionButton1 = True Then
.Offset(RowCount, 3).Value = "entrada"
Else
If OptionButton2 = True Then
.Offset(RowCount, 3).Value = "salida"
End If
End If
 
If Me.CBcant2.Value = "" Then
Exit Sub
Else
RowCount = RowCount + 1
.Offset(RowCount, 0).Value = Me.TextBox6.Value
.Offset(RowCount, 1).Value = Me.ComboBox2.Value
.Offset(RowCount, 2).Value = Me.TextBox12.Value
If OptionButton1 = True Then
.Offset(RowCount, 3).Value = "entrada"
Else
If OptionButton2 = True Then
.Offset(RowCount, 3).Value = "salida"
End If
End If
 
If Me.CBcant3.Value = "" Then
Exit Sub
Else
RowCount = RowCount + 1
.Offset(RowCount, 0).Value = Me.TextBox6.Value
.Offset(RowCount, 1).Value = Me.ComboBox3.Value
.Offset(RowCount, 2).Value = Me.TextBox12.Value
If OptionButton1 = True Then
.Offset(RowCount, 3).Value = "entrada"
Else
If OptionButton2 = True Then
.Offset(RowCount, 3).Value = "salida"
End If
End If
End If
 
 
If Me.CBcant4.Value = "" Then
Exit Sub
Else
RowCount = RowCount + 1
.Offset(RowCount, 0).Value = Me.TextBox6.Value
.Offset(RowCount, 1).Value = Me.ComboBox4.Value
.Offset(RowCount, 2).Value = Me.TextBox12.Value
If OptionButton1 = True Then
.Offset(RowCount, 3).Value = "entrada"
Else
If OptionButton2 = True Then
.Offset(RowCount, 3).Value = "salida"
End If
End If
End If
 
If Me.CBcant5.Value = "" Then
Exit Sub
Else
RowCount = RowCount + 1
.Offset(RowCount, 0).Value = Me.TextBox6.Value
.Offset(RowCount, 1).Value = Me.ComboBox5.Value
.Offset(RowCount, 2).Value = Me.TextBox12.Value
If OptionButton1 = True Then
.Offset(RowCount, 3).Value = "entrada"
Else
If OptionButton2 = True Then
.Offset(RowCount, 3).Value = "salida"
End If
End If
End If
 
' Clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
Else
If TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
End If
Next ctl
End If
End With
End Sub


Having an issue when taking a negative number and minusing a positive number. I assume there is a formula. I have attached a copy of the form. Example... Cas, had a total for the month -$642.55, last week, she was in the positive by 195. the total should be
(-$642.55) - $195 =( -$447.55). Instead it is adding the two (even thought $195 is a positive number) and totaling to (-$837.55). Need a formula so that when starting with a negative number and subtracting a positive number returns a lower negative number. (as shown in illustration)

In the illustration, I manually inputed the number. Would love a formula to handle this for me!

Thanks for any help??

Hi,

I have an enormous inventory list on excel containing fields including date of purchase, item name, classification (as display, accessory, hardware, software, multimedia etc. etc.) and price. Each item is copied several times in the sheet in new row every time depending on quantity. For example if I have 100 Logitech keyboards in stock, there will be 100 ROWS showing it in stock at different places in the sheet.

NOW.. I want to allocate a unique SERIAL NUMBER for every item in every row. Like for Mouse, for eg, CAMOU00001, CAMOU00002…… and for keyboard, for eg, CAKYB00001, CAKYB00002……

The Problem is that everything is present in so huge quantity that I want to build up a formula that can just see the classification of the row, then find the last serial number for the same classification and add +1 to it.. it should be left blank If the classification in that row is blank. it will help me by just pasting the formula and saving time.

Can anyone help me out building up such a formula for allocating ALPHA-NUMERIC SERIAL NUMBERS to my inventory lists?

I have done an import external data from an access/sql-database in Excel.
I've added a comment-column to this data. When I do a refresh of the data,
the sort-order of this external data is screwed up and the comment-lines
don't correspond any longer to the rows they belong to.
Is there a solution for this?

I have done an import external data from an access/sql-database in Excel.
I've added a comment-column to this data. When I do a refresh of the data,
the sort-order of this external data is screwed up and the comment-lines
don't correspond any longer to the rows they belong to.
Is there a solution for this?

Hi

I am not an excel whiz, so I will need layman's terms if possible. I may have found a solution to my problem under another thread, but I couldn't understand it. I have only briefly worked with macros, a very long time ago. Here goes...

I have an inventory list that has not been updated for sales of the inventory, only for purchases. I have sales lists, generally by month, in separate spreadsheets. I am looking for a simple, efficient way of either eliminating or at least matching up the data in the sales list to the inventory list in order to remove sold items from inventory (on paper). I am hoping the result will be a fairly accurate inventory list, and related value, so that a physical inventory count won't be necessary at this time. Obviously I could copy all of the sales info into one spreadsheet, sort by inventory number, then manually delete all sold items from the inventory listing. I am REALLY hoping there is a better option.

It is midnight here, and I am fairly certain I have bags under my eyes. I appreciate any help I can get.

Thanks!!

I am using Excel for inventory control and I would like to have a feature where, if you click on shelf (represented by an autoshape) in the map it takes you to the page and cell that contains the inventory listing for shelf. I would also like the reverse to work as well, click a cell and go to the shelf.

Some details...
The map and inventory are currently in the same workbook (I can make seperate books, but would prefer not to).
I can add a new column to the inventory list for the link if it's easier.
The map is built with autoshapes.
Most of the autoshapes are grouped.
I DO NOT want to link the text of the cell to the autoshape.
This might be something for a new thread but it would be nice if the autoshape shelf had a temporary visual change (such as a new border or colour) when you are taken to it from the inventory listing. This visual change would have to revert to the original look when the autoshape is no longer selected.


No luck finding an answer? You could always try Google.