Auto populate when changing from quoted to sold?

niko1216 - Jun 7, 2018 at 02:05 PM
 Blocked Profile - Jun 8, 2018 at 03:43 PM

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.


4 responses

Blocked Profile
Jun 7, 2018 at 05:26 PM
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!
Whats a database. do you have an example of one that I can use?

I love the idea!!! Just not sure how to create one.


Blocked Profile
Jun 8, 2018 at 11:10 AM
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?
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:

10----------1/2 ton Truck----------Petrol
11----------1/2 ton Truck----------Deisel

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

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 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!

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!