For this tutorial, I’m going to design a new format for our accounting records.
We have the Purchase and Sales, report
As you can see there are two Data entry accounts. (Purchases and Sales).
We’re going to consolidate these two using the Data Entry Form, this will be a more complicated tutorial compared to my previous posts.
But I’ll go easy on you, step by step, no shortcuts, just learning.
- Add a worksheet to your workbook, rename it to “Data Entry Form”.
- On cell B3 type, “Date:”, while on cell B4 type, “Transaction:”
- Under transaction, cell B5 type “Docs no.”
- Next, cell C7 to F7, type “Items”, “Quantity(kg)”, “Unit Cost”, “Total Cost” respectively.
- Take a look at the image below
The entries have been given
Add borders, to make it look like a form.
You may notice of headings “Unit Cost” and “Total Cost” under the Purchase transaction, which are unchangeable.
If you’re going to change the Transaction into “Sales”, the two headings will remain unchanged, which is irrelevant for our system. Purchase is for the cost, while Sale is for the selling price not the Unit Cost.
Question, how are we going to change this from static to dynamic?
Just use the formula instead of constant i.e., “Function IF”, when a target change, the other target will probably change. See this LINK for how to use IF
- For cell E7 —> “=IF(C4=”Purchase”,”Unit Cost”,IF(C4=”Sales”,”Unit Price”,”Please enter correct transaction”))”
- If cell C4 is Purchase then cell E7 will be Unit Cost
- If cell C4 is Sales ,the cell E7 is Unit Price
- For cell F7 –> “=IF(C4=”Purchase”,”Total Cost”,IF(C4=”Sales”,”Total Sales”,”Please enter correct transaction”))”
- If cell C4 is Purchase therefore cell F7 is Total Cost
- If cell C4 is Sales therefore cell F7 is Total Sales
- For incorrect inputs cell E7 and F7 value will be “Please enter correct transaction”
- Try it and See for yourself
- Create another worksheet, rename it to “Database”
- From that sheet cell A2 to I2, type:
- Docs no.
- Quantity (kg)
- Unit Cost
- Total Cost
- Unit Sales
- Total Sales
Right click the CommandButton, click properties (be sure the design mode is enabled)
Choose your backcolor (the color of your button), and change the name of the caption if you want to (e.g., “Post it”)
For reporting, add a worksheet, then rename it to “Report”.
Our file is now formatted to deal with our accounting records. Next step would be working with our program.