New Rows in One Linked Worksheet Creating New Rows in Another

[JohnBrooksBiddle]

Hi there, relatively new Excel user here in need of a creative solution.

As we are trying to bring our car dealership into the 21st century, we are trying to digitize our stockbooks. We plan to have one master Stock Book worksheet and then a few other worksheets for specific departments that pull info from select cells in the master (the first two images I attached).

Here is what are trying to do:

In the Recon Inventory, I have the external worksheet link already set up, as you can see in the formulas bar of the first image. Whenever I go to make a new entry in the master Stock Book, I right-click at the A2 row, and click "Insert rows" to make as many new rows as we have cars bought that week (four cars bought means I make 4 new rows at the top). I then manually enter in all the details by hand. This is because my boss wants the most recent entries to always be at the top. Now, ideally, whenever I create new rows in the master, I would like new rows to be automatically created and filled out in the same spot towards the top in the Recon inventory sheet, with a few caveats:

• Only the Stock#, year, make, model, and odometer need to be pulled from the master into the Recon every time a row is created.
• The cell under "Status" can always just say "in progress", since it will just be altered by hand on the rare occasion it's a trade-in (or I might create another dropdown menu).
• The cells under Columns G, J, and M should be blank in the newly generated rows should be blank, as they will be manually filled in later.
• The dropdowns I created in H, I, J, and L: you can see they are a bit wonky, with the H column IF formula referencing the blank cell next to it as the only way I could think to make it say "No" for the purposes of conditional formatting (it says "No" by default as soon as it is copy-pasted, and "No" means the cell is red due to conditional formatting, then the employee can click "Yes" from the dropdown and if they do the cell becomes green)
  • With these four columns, every time I create a new row in the master spreadsheet, I would like the H, I, K, and L values for the new rows to always just say "No", "No", "Incomplete", and "Incomplete", respectively every time the new row is created after being triggered by a new row entry in the master, with the dropdown menu being carried over so employees can update the Recon sheet as time goes on.

This is something that may be able to be completed with a Macro, but I don't know the best way to set that up. Maybe I just set up a Macro to run through this whole process for one row, and then I run the Macro as many times as I have new cars to entry (4 new cars means I run the Macro four times, creating 4 new rows in the Recon sheet) but I don't know if Macros can be carried across different workbooks like that, as these are two separate Sharepoint workbooks because my boss doesn't want them to be multiple sheets in the same work, unless this is the only way to accomplish that.

What do you guys think?