Auto populate when changing from quoted to sold? Closed

Question

Hello, 

I am working on a work book in excel for work.  On one page, I am tracking quoted / sold cases when a case sell, I have it change colors.  On a separate sheet, I am listing the sold cases to add them up. 

Here are my parameters for both pages

Eff. Date    Name   producer   $$$  underwriter Status

Is there a way that when I change the status from, say quoted to sold, that it would automatically populate my sold case tracking sheet (insert a new row with the information) for me?

I hope this is not too vague of a question.

Please let me know.




System Configuration: Windows / Internet Explorer 11.0

ac3mark · Answer

Use a database, and report on the different cases!  Excel is for calculations, not for data storage and manipulation.  You can still do calculations on a Database!

ac3mark · Answer

Understand, you would need a button or something. A user has to initiate any sort of CHANGES to a sheet, a program cannot. You would need to place a button on the sheet, and press it after all rows have been changed. Almost like an "end of Data entry Process". Make sense?

ac3mark · Answer

OK, a database a set of tables linked by Index's (typically unique).  I will try to explain as short and sweet as I can.

So you are in a business that sells cars.  Your database may be comprised of four tables:

SalesPeople (table #1) with Columns of:  EmployeeID, Name, StartDate, Bonus

VehicleType (table #2) with columns of: VehicleType, Description, FuelType

Vehicles (table #3) with columns of: VehicleID, VehicleType, SalesPrice, VehicleStatus, VIN, COLOR, soldby

VehicleStatus (table#4) with columns of: StatusID, Description

Now the data on each table could be like:
Table-SalesPeople
EmployeeID--------------------Name--------------------Startdate--------------------Bonus
______________________________________________________________________
E100----------Billy----------7/1/2013----------5%
E200----------Joe----------2/14/2015----------4%

Table-VehicleType
VehicleType--------------------Description-----------------FuelType
______________________________________________________________________
10----------1/2 ton Truck----------Petrol
11----------1/2 ton Truck----------Deisel
12----------Sedan----------Petrol
13----------Coupe----------Petrol
14----------Coupe----------Deisel

Table-Vehicles
VehicleID----------VehicleType---------SalesPrice------------ VehicleStatus------------VIN-----------COLOR----soldby
9999---------- 14----------14000----------10 ----------1234----------Silver          
9900 ----------10 ---------- 30000----------99 ---------- 4321----------White----------E100
8899----------12 ---------- 8100----------100 ----------9876----------Red ----------E200


Table-VehicleStatus
StatusID-----------------------Description
1  ----------Recvd/not detailed
2   ----------  Detailed
10    ----------   ready for sale
 90 ----------Pending sale
99  ----------  Sold
100 ---------- Sold/delivered

So now, you would need to build entry forms, and link the TEXTBOXs to the appropriate tables and columns.  This allows you to open up a data entry form, and enter in and manipulate the date in the tables.  NOTE--You do not need entry forms, and you can manipulate the data directly in the tables.  This is not ideal, but it can be done this way!

Now that you have a way to move and add data, you can now write a report that says:
Give me all of the cars from the vehicle table that are red (vehicle color=RED), or...
Give me all of the cars from the vehicle table that are sold but not delivered(vehicle status=99), or...
Show me all of the cars in Vehicles that are't sold (soldby=NULL/BLANK), or...
Show me all of cars that Billy sold (Vehicles.soldby=E100 and Vehicle.Status = 99 or 100), or...
Show me all of the diesel coupes over 10000 (Vehicles.VehicleType=14 and SalesPrice>10000) would return vehicle 9999.

The data will never move from the table, it will just CHANGE status for reporting purposes.

I hope this helps, and I can assist with making the forms.  Now you need to pick a Database program and start to learn it.  Beleive me, it is easier than trying to learn VBA!  I suggest you start with a Free one like https://www.openoffice.org/product/base.html.  Get the version 4.0.1.  The latest and greatest didn't seem to run right on windows 10 (at least not on my machine!).

Let us know if you need a more detailed description, or if any of this doen't make sense, because we need to understand how these table are RELATED before we can move forward with a dynamic solution without having to redesign after you start using it!

Please note- most of these tables are value storage and will never need to be changed, or altered.  In the above example, once you enter in the Employee, the Vehicle Type, and the Status, the only table you will manipulate will be the Vehicles table!

ac3mark · Answer

ONe other thing you can do for speed to market and lower the learning curve, is enter in the different status on the current sheet, and FILTER for the status. The sheet will total only what the filter is showing. So you could filter for SOLD, and any other values that wish to narrow down!

Auto populate when changing from quoted to sold?

4 responses

Subscribe To Our Newsletter!