I have a table that is made up as follows:
Column A has multiple vehicle manufacturers identified (example FCA, FORD and General Motors, and they will be repeated many times).
Column B has a program (meaning a alphanumeric designation of a vehicle built by the manufacturer identified in Column A) (example DS or KL for FCA, P552 or U554 for Ford).
Column C has the actual vehicle production nameplate (how the vehicle is sold to the public (example 1500 or Cherokee for FCA, F-150, F-150 Supercrew, and Navigator, Navigator L for Ford).
Column D has the assembly plant where the vehicle is actually built (example Saltillo Truck, Warren Truck, Belvidere, Toledo North for FCA and Dearborn Truck, Kansas City #2, Kentucky Truck for Ford).
Columns E and F have annual total of each identified vehicle will be built.
Columns G through R have how many will be built each month in the current year.
I need to do the do the following with the data from the above identified table in a new tab:
1. Use Dropdown that references Column A.
2. Use Dropdown in next cell that identifies the programs in Column B that are tied to the manufacturer in Column A.
3. Use Dropdown in next cell that identifies the vehicles in Column C that are tied to the programs in column B.
4. Use Dropdown in next cell that identifies the assembly plant in Column D that are tied to the vehicles in Column C.
5. The sheet would then populate the builds for that specific vehicle based on columns E through R.
Lastly, I want to be able to have a start and end date. Example, I have added the following data in the First 4 drop down lists:
Dropdown 1: Ford
Dropdown 2: P552
Dropdown 3: F-150
Dropdown 4: Dearborn Truck
The sheet has now automatically populated the builds for this as follows:
CY2016 92,925
CY2017 72,286
Jan 2017 4,961
Feb 2017 5,607
Mar 2017 5,967
Apr 2017 4,864
May 2017 6,901
Jun 2017 5,867
Jul 2017 4,472
Aug 2017 8,281
Sep 2017 7,987
Oct 2017 6,645
Nov 2017 5,623
Dec 2017 5,111
But, my specific customer does not start supplying until Aug 2017, so I want to add the start date of Aug 2017 and End Date of Dec 2017. The sheet will automatically zero out everything prior to Aug 2017. This would then automatically recalculate the CY 2017 number from 72,286 to 33,647. Also, because my customer does not start supplying until Aug 2017, it would also zero out the CY2016 number.
I hope all this makes sense. I have attached the actual table that contains a small portion of the Vehicle Data I am looking to work with.
Bookmarks