Microsoft Excel Formula to auto-populate Sub-table from Master Table based on Status Change.

Question

An Excel support person tried to help me trouble shoot this problem via Quick Assist screen share and indicated that it was an important issue that should be looked at by a higher development support level and directed me here to raise a ticket. I run this file on two different computers that reference the file on OneDrive. My OS is Windows 10 Pro 64bit and running Excel through Microsoft 365.

A Microsoft Excel workbook contains a master inventory table of record rows. Each new artwork created adds a new record along with the various corresponding attributes. I would like to trigger an entry in a sub-table when two attributes (columns) in the record (row) "Gallery Status" and "ConsgndGallery" are relatively changed to specific data values [CNSIGND, DECONSGN] and [RUCO, STUDIOSF]. The sub-table(s) will be a historical record of artworks consigned to a gallery with the dates and other pertinent information until the artwork is either sold or de-consigned and returned. So each consignment record for a gallery would correspond to a single artwork during the consignment.

Desired Result:

The purpose of this sub-table is to track the history of artwork consigned to a gallery by: Date Consigned,  Inv#, Title, Medium, V-Size, H-Size, Retail price, Net to artist, Sold Date, Paid Date, and/or Returned Date.

The data in a row should be populated and locked once the trigger condition has been met. Each time the trigger condition for a record attribute is met, the data value should be copied to the appropriate cell until the record condition reaches a closure state. When a record entry is closed and this painting goes back to that gallery on consignment then a new record row must be added.

1.      An artwork record should be added to a corresponding consignment table when Gallery Status changes to CONSGND and a CnsgndGallery value has been added. The record to be added includes the basic attributes of the artwork record: Date Consigned,  Inv#, Title, Medium, V-Size, H-Size, Retail price, Net to artist.

2.      The consignment record for the artwork will change if there is a sale, which is triggered when Gallery Status changes to SOLD and the ___Sold Date ___value has been added. This condition would add the attribute to the consignment record.

3.      There would also be a corresponding attribute added when the Paid Date value is added which would then be added to the consignment record. The addition of this attribute would close that consignment record.

4.      Alternatively, if the artwork is de-consigned, which is triggered when Gallery Status changes to DECONSGN and the Returned Date value is added which would then be added to the consignment record. The addition of this attribute would close that consignment record.

There is also the possibility that the same gallery may re-consign the same artwork at a later date which requires the addition of a new consignment record in that gallery table.