Share via

Adding/Creating records based on a dynamic range and a static table

Anonymous
2020-07-29T15:13:16+00:00

I need to add records to a dynamic range based on a static table.

The dynamic range begins in cell B5; Static table begins in G5.

For each instance that an order for a part with a sub part appears in the dynamic range there needs to be a record created for that sub part.  There are 2 ways I could see this working. 1) In cell K5 the new records are put in line with the dynamic range (more likely if they were added to the bottom. 2) In cell L17 a new table is created with just the new records to which I can used PivotTable and PivotChart Wizard to combine the sources. 

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-07-30T11:27:58+00:00

    Dear A_Tut

    I would like to help you but unfortunately, I don't follow the logic here.

    Would you kindly give us more and clearer details of your goals?

    Please, note:

    1- The "Desired Output" tables don't include any dates for the month of June from Dynamic Range table when these are the newest records, is that correct?

    2- What is the relationship between Part and Sub Part in the Output tables?

    3- Could you give us details of the criteria ad logic you are using to fill out each row in the Output tables?

    Awaiting for your answer

    Regards

    Jeovany

    0 comments
  2. Anonymous
    2020-07-30T12:47:43+00:00

    Jeovany,

    In response to your questions:

    1. I was only giving examples of the outputs from the first 3 records in the dynamic range. The dates for June are the newest so as new data is added the dynamic table will get taller with the newest records at the bottom.
    2. Think of Sub Parts as components that are significant enough to be identified in the manufacturing process. They are not mutually exclusive. A Part either has a Sub Part or it doesn't. If it does it will always have the same one(s).
    3. I manually created the output tables. However, I believe I want to use something along the lines of the formula below.

    =IFERROR(INDEX($H$6:$H$11,SMALL(IF(ISNUMBER(MATCH($G$6:$G$11,D6,0)),MATCH(ROW($G$6:$G$11),ROWS($G$6:$G$11)),""),ROWS(A1:A1))),"")

    The issue is I need to produce up to 3 different values for every record based on the value in column D. Then using the same record replace the value in column D with the values from the above formula.

    Hope this helped clarify my wants.

    Respectfully,

    A_Tut

    0 comments