Inventory and Invoice System

Hi Guys and Girls,

I just joined this forum and well i hear that the members here are brighter then the sun, therefore, i cannot go wrong.

Here is my question.

I have created a nice Inventory and Invoice System.

If someone is interested i can post it here, there is no personal data there yet

MY EXCEL SHEET IF YOU ARE INTERESTED THE WORDED LAYOUT

Now i named my sheets
Sheet 1 = STEP 1_Customer Registration
Sheet 2 = STEP 2_Filter Registration
Sheet 3 = STEP 3_Invoice
Sheet 4 = STEP 4_Statement
Sheet 5 = Cell Modules

Step 1 is has a if function that gives a customer an ID and gets all their info
as in company last and first name apt and and and.

Step 2 Here we register the serial number of the product and can also choose the
customer ID from step one therefore the product will be registered to the customer.
(However not all of our products have serial numbers therefore this one is only for products with serial numbers)

Step 3 the invoice. Here is were we can choose the customer ID and the customers fields will be filled out a quick quesiton of is the customers address is the same as the shipping if yes then it will copy the address also into the shipping address section. Also who the sales agent was
On the same sheet, the second page will be the shipping order page with no cost info but only a shipping form (whats inside the package sheet)
lastly in this sheet there is a label page with the shipping address of the client.

STEP 4 The statement section.
At this sheet nothing should be touched.
It takes the sales agents name from Sheet 3 (The Invoice)
and has an Index and match function for his or her commission.
In this sheet there is a thank you for making this sale happen
the commission amount for this sale and detailed info about how much
he or she got for which sale. Agreed terms.

Cell modules
Well here is the list of parts we have
How much they cost
How much commission there is
sales agents
and so on.

MY ACTUAL QUESTION

Now if you have read the above words on what i made you will understand this if not i will try to summarize.
I got a sheet which is an invoice.
i have if statements there as well as lists
at times on top of each other.
Meaning that if i selected something from the drop down box the IF statement is gone.
(Drop down box here has products) The IF then statement on top of it is for
if you select a serial number from the list then the product is automatically selected.
Now then if i would save this invoice it would all be gone therefore
i would like to save this invoice as a separate excel sheet.
Now lets see.

Customer ID is 12001 and Company name is XYZ Co.
I would like the excel file to have the following name
Company name + Customer ID.xlsx

IF
The file already exists
then it should add the sheet to that file.Basically like in invoice storage for that particular customer. Sheet naming could be Customer ID + "-" +1 (i.e.: 12001-1 , 12001-2 etc.)

Now after that sheet is saved there, i need the template invoice sheet (sheet 3) to be clear again with all if statements functional and so on, kind of like a reset button.
or maybe make it not.

Advancing this feature i would like to create a sheet in the same MAIN Workbook called
STEP 5 Invoice History.
Where i can see all references from different workbook sheets, lets say within a specific folder range like customers
in customers there would be several different xlsx files. each customer with one file
each file containing a sheet with there invoice, if the customer has more then one invoice the file has more then one sheet. sheets should be labeled customer id "-" +1 like already described.

Is something like this possible with Excel and Macros.
I think it is, i mean there are some amazing things you can make.

I thank you for your help.
Any time or help is appreciated

FILES
Please right click and save

Buttons.xlsx
Inventory and Invoice System.xlsx

I would never attempt to build such a system using multiple workbooks. I'm sure that you are finding what a difficult task it is to maintain.

I have an invoicing system that in Excel that creates invoices, statements etc & maintains everything in one workbook. Copy invoices can be produced at the click of a button. This would be difficult to achieve in multiple workbooks & sheets

I uploaded the file of what i created.
Also a button system with no macros yet.
Just the layout, to motivate me. (Buttons.xlsx)

Now i am not sure how difficult it would be to maintain.
It would maintain itself i think.
The Serial number list would be added at each sale.
Therefore there would be no pre entered list only once the part is sold the
serial number is entered.

The workbook (the main one i just uploaded)
Would then save each invoice separately since their are different clients
and them all being in one workbook would be so ... cluttered.
How many sheets can one workbook hold.
But i would also take that option,
having them all in one workbook.
But then they need to be at the least sorted correctly and possibly
saved with the current values.
Cause prices might change and the invoices should not, the invoice should also after saving not change the date meaning the function =NOW() should not be there it should be the date it was saved or created. You know.


Thank you for your input so far.
Also i was thinking
of something like this in a save button

Please Login or Register  to view this content.

Here adding a IF statement
If file name Customer ID + Company name exists then
add sheet to file else create xlsx file

I think will find that the basic idea that you have will becoming unwieldy as the data builds up. You definitely need a database approach either MS Access or using Excel as a Database