Making a "Database" In excel

[Higoten]

Hello, I'm currently working on a workbook in Microsoft excel, and i could use some help making it add information automatically.

I know that excel is not suppose to be used as a database, but it is important that I keep everything in an excel file.

What I am doing:

I am currently making a combination of a invoice, a packing slip, and a database all in one workbook.

The invoice worksheet uses a VLOOKUP table to fill in other cells based off of the PART#. It also requires the quantity to complete the PRODUCT(multiply) formula. This sheet can hold 20 entries.

The Packing slip worksheet takes information from the invoice worksheet and places it appropriately. It also requires manual input for the PO# and Invoice#. This sheet can also hold 20 entries.


What I am having problems with:

The section I am having a problem with is the Database section.

What I want it to do, is to take the information from the Invoice worksheet and Packing slip worksheet and compile it on one row for each part that was entered.

What I want to do.

(See attached workbook)

If you look at the Sales Database worksheet, you'll see 8 rows, all with a different header. If you look at the For database worksheet, you'll see the same 8 rows, only the twenty cells below it have links to the information which correspond with the row.

What we have been doing is taking the information in the For Database worksheet and copy > Paste special > Values into the Sales Database worksheet

I have been searching the internet for a way to make it so that the information that we have been copy and pasting into the database worksheet, automatically forward to the database worksheet on the bottom row (of last entered information, not the bottom of the worksheet)

I found out that this kind of thing is possible using VBA codes. but I have very little understanding of programing.

This code is my only lead onto getting something like this to work. But I need it to operate on Worksheet_Calculate instead of Worksheet_Change (becouse i have feilds that don't require any manual input, and worksheet_change seems to only update the row if manual data is inputted.

Private Sub Worksheet_Change(ByVal Target As Range)



Dim lReply As Long



    If Target.Cells.Count > 1 Then Exit Sub

        If Target.Address = "$D$1" Then

            If IsEmpty(Target) Then Exit Sub

                If Target.Address = "$D$1" Then

                            Range("MyNames").Cells(Range("MyNames").Rows.Count + 1, 1) = Target

                        End If

                End If

        End If

End Sub

This code may not work for what i need because i need it to check all twenty input fields, and exclude them if there is no information imputed.

Again, my knowledge of programing is extremely limited (I edited the above code to get rid of a feature i didn't want, but i got error messages, so i left the if/then statements alone and changed the command it does to something i did want. You may notice the redundancy with the target.address.)

I attached a workbook with filler information that uses the exact format as my actual workbook.

Steps to show "issue"
1) Open invoice worksheet (attached)
2) Type 200 into the first row under Quantity
3) Type 2 into the first row under Part #
4) Type 200 into the second row under quantity
5) Type 10 into the second row under Part#
6) Go to the Packing List worksheet
7) Type 201201 into the first row under PO#
8) Type 500 into the first row under Invoice#
9) Type 201205 into the second row under PO#

----The following steps are the steps i would like to have be automated---
10) Click the For Database worksheet
11) Highlight all of the data. Do not highlight the headers.
12) Copy
13) Go to the Sales Database worksheet
14) click the upper left most cell (A2 in this case).
15) Paste Special
16) Values.
17) OK

If anyone could check it out and let me know what i need to do to accomplish this, or fill it into my attached workbook, it would be greatly appreciated! (any suggestions for improvement would be great as well, I don't use excel often.)

Thanks in advance!

Higoten.