Database inventory question
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Database inventory question

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hello all,
    I have been asked by my company to build an Excel spreadsheet to track inventory as it ages. Most of our users have little or no training on Excel (neither do I), so the ideal template will require only data entry. I have succeeded in building a working model, but I have run into a couple of problems that I just cannot get my head around.
    First, there are a couple of simple formulas on the main inventory page. In Column C and Column F, I have formulas that I would like to apply to each new item entered into the database, but if there is no record in the column, not have it reflect a value. In other words, how do I get the formula to apply to new records without applying Fill Down and trying to guess the range? Right now, my formula in C, which refers to the function of Today, returns a value of 37000+ if the there is no value in Column A, the date received in inventory.
    Next question. When an item is sold, I would like to have the sales manager be able to enter a value (say an "S") in a column on the inventory sheet that would remove the entire row from that sheet and transfer the data (not the formulas) exactly to the next sheet and remove it from the prior one. Right now, they are using cut and paste, and it is proving difficult for them.
    I'd also like to add that there are some **** clever people on this board. I tried the search feature and succeeded only in feeling pretty dumb.
    Thanks in advance from The Land of the Blind.

  2. #2
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can you answer a few questions please. The answers will help simplify the code and eliminate guesswork, plus if you are not familiar with VBA it will save you and your co-workers some effort or confusion in hopefully not needing to modify it down the road, if we can nail it correctly from the start.

    (1) What are the names on the sheet tabs of the "main inventory" worksheet, and the "next sheet". Or better yet, take a moment and press Alt+F11, and in the left pane entitled "Project - VBAProject", locate your workbook name, and in the Microsoft Excel Objects folder of your workbook, see what the Sheet object number is that corresponds to your two worksheets' tab names. Example, you'd see something like
    Sheet1(Main Inventory) and/or
    Sheet2(Next Sheet).
    What are those sheet numbers for your worksheets? If this is not clear, the sheet tab names will suffice.

    (2) What is the range of columns for the data in your main inventory sheet? A:F or something more?

    (3) What row does your data start in, on the main inventory sheet? Maybe row 2, with row 1 as column header names?

    (4) For your first question, if for example your data is from rows 2:30, and then in A31 you enter a value, is it that, upon entering that value, that you want the formulas in row 30 to be copied into row 31, but not before a value is entered into A31?

    (5) For your second question, which column is it that an "S" will be entered, that should trigger the cut and paste?

    (6) When you say you want the "entire row" removed from the inventory sheet after an S is entered, do you mean from A:IV, or just from A:F if you have information from G:IV that you don't want to delete?

    Sorry to bombard you with all these questions, but it'll save us some time (and web space) if we get it right from the start.

    Any other useful info you want to throw in would be appreciated.

  3. #3
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Many thanks for your quick reply. Answers to your questions are as follows:
    1. The Sheet tab names are "Inventory" and "Sold Inventory"
    2. Column range is A:J
    3. On the Inventory Sheet, the data begins in row 5, with row 4 being the column header names. The rows above contain only the the function TODAY, in A2, which is to be used in one of the formulas.
    4. That is exactly what I am looking for.
    5. As far as where the "S" would go, given that columns A through J contain data, I would guess that could go in column K?
    6. All that needs to be pasted is the data in A:J.

    As far as other information, the spreadsheet contains four worksheets total. The first, called the Inventory Tool, tracks the movement of inventory through 15 day increments, and shows the result of formulas only. The second and third are the inventory and sold inventory worksheets. The inventory worksheet contain only two formulas. In Column B, there is a formula that returns a numerical value for the subtraction of the date the part was taken into inventory from the absolute TODAY function referenced above. The other formula, in column J, is a simple subtractionof cost from sell price, to show the profit. The Sold Inventory worksheet contains no formulas, as the data has already been calculated. Consequently, the column headers are identical. The final worksheet, entitled Formulas, contains various COUNTIF and SUMIF functions which, based on the number returned in Inventory B and Sold Inventory B, allocates the # of units and the profit according to time in stock to the first page.
    Tom, I thank you again for your assistance. An out of town emergency will necessitate my absence until 3/6/02, but I look forward to your response when I return.

  4. #4
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    11,136
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, here's one way to do that.

    First, for your Inventory sheet, right click on that sheet tab, left click on View Code, and paste this in:

    '''''''''''''''''''''''''''''''''''''''''

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 11 And Target.Row > 4 Then
    If Target.Value = "S" Then
    Target.Activate
    Run "CutAndPaste"
    End If
    ElseIf Target.Column = 1 And Target.Row > 5 Then
    Target.Activate
    Application.ScreenUpdating = False
    Range(ActiveCell.Offset(-1, 1), ActiveCell.Offset(-1, 9)).Copy
    ActiveCell.Offset(0, 1).PasteSpecial (xlPasteFormulas)
    Application.CutCopyMode = False
    ActiveCell.Select
    Application.ScreenUpdating = True
    Else
    Exit Sub
    End If
    End Sub

    ''''''''''''''''''''''''''''''''''''''''

    Then, while you are still in that Visual Basic Editor screen, click on the Insert menu option (found at the top of the screen), select "Module", and paste this in:

    '''''''''''''''''''''''''''''''''''''''''''

    Sub CutAndPaste()

    Application.ScreenUpdating = False

    Sheets("Inventory").Range(ActiveCell.Offset(0, -1), ActiveCell.Offset(, -10)).Copy
    Sheets("Sold Inventory").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)

    ActiveCell.EntireRow.Delete Shift:=xlUp

    Application.ScreenUpdating = True

    End Sub

    '''''''''''''''''''''''''''''''''''''

    Hit Alt+Q (or the X in the upper right corner) to exit the VBE, and you are back onto your worksheet and good to go.

    Now, when a value is entered in column A beginning in row 6, the formulas from the row above will be copied to that current active row, among columns B:J.

    When an "S" (case sensitive) is entered into column K beginning in row 5, that row will be deleted and the values from A:J of that row will be copied to the next available row in the Sold Inventory sheet.

    One suggestion, save your current workbook as a guinea pig name and test this code with that workbook, just to be sure the code does what you want, before any of your data is altered from this code.

    If I missed something, let me know.

    Good luck.

  5. #5
    New Member
    Join Date
    Mar 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    First of all, many thanks for your response. I am frankly very impressed. The program works beautifully, and is very easy to use, which is exactly what I (and my co-workers) need. The only hiccup seems to be when the information is copied from one row to the next, it copies not only the formulas, but all information - the stock #, manufacturer, etc. This is certainly not a problem, as the manager entering data can simply overwrite it, but I was wondering if there was a way that only the formulas in columns B and J might be copied, leaving the others blank.

    Again, Tom, I thank you for your timely and excellent help with this.

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com