I want to have a Google Sheet that looks like this, here is an example of my table https://docs.google.com/spreadsheets/d/1gkoP8PPD0K--0Sce6mvCjik5LYKpWXpRjN3qfJxd0Dc/edit?usp=sharing link to it
I need that when I insert a link from Copart and IAAI auctions, examples of links https://www.copart.com/lot/54701095/salvage-2023-tesla-model-y-pa-harrisburg https://www.iaai.com/VehicleDetail/42486336~US, in the file, the following rows are automatically filled according to the columns date and auction time - in Kyiv time, lot number - the number indicated on the page, auction commission - calculated according to auction conditions considering the previous column bid at auction, along line A, vehicle size - add a dropdown list with such size types as - passenger car, crossover, SUV, van, accordingly, a surcharge for freight (sea delivery) will be added for passenger car 0 dollars, for crossover +150 dollars, for SUV and van +300$, fuel type - in a dropdown list should be such fuel types as gasoline, diesel, electric, hybrid, these data will later be used to calculate vehicle customs clearance, also when choosing electric or hybrid fuel type, add +100 dollars to the sea delivery price (if possible, also extract this information from the auction link), manufacturing year - also automatically extract information about the vehicle's manufacturing year, this information is available on the auction, engine size - entered manually, for example, 2.0 means engine size is 2 liters or 82 if electric fuel type is selected, it means 82 kW, these data are later needed to calculate the customs clearance price, vehicle location - simply specify from the link the vehicle's location on the auction, port of loading - simply specify the loading port, shipping cost to Klaipeda - take from the attached Excel file included in this message how much inland shipping in America will cost and add the freight at the following prices: New Jersey (NJ) - 650$, Savannah (GA) - 700$, Houston (TX) - 800$, Los Angeles (CA) - 1,200$, Chicago (IL) - 850$, Miami (FL) - 750$, Seattle (WA) - 1,350$ (excluding electric and hybrid), also add surcharges previously mentioned for electric, hybrid, and vehicle size, customs clearance cost - calculate the customs clearance cost as follows: if electric vehicle, then 1 kW * 1.2 dollars, so if 82 kW * 1.2 dollars = 98.4 dollars, then add 50 dollars for electric vehicle customs clearance. If it is gasoline, diesel, or hybrid engine, the calculation is as follows: customs cost = excise + duty + VAT. Excise is calculated as engine volume * number of years of the vehicle * coefficient (for gasoline and hybrid engines up to 3 liters - 60, above 3 liters - 120; for diesel up to 3 liters - 90, above 3 liters - 180). Duty is calculated by taking the auction bid column and auction commission, add 1600 dollars, and multiply all by 10% VAT - calculated by taking the auction bid column and auction commission, add 1600 dollars, then add excise and duty, and multiply all by 20%.
