Creating multiple references relating to a single reference in excel

Question

Hi,

Below screen shots are of the work order system which contains some parent data (yellow cells) and some child data (grey cells). I want to transfer the data into a normal excel sheet such that the parent data gets repeated with occurance of every child data.

Is this possible in excel? Can somebody help me please?

Answer 1

Refer below images:

Image of Sheet1 (Source Data):

Image of Sheet2:

All formulas to be entered in Sheet2.

Enter below formula as an array formula (CTRL+SHIFT+ENTER) in cell D2:

=IFERROR(INDEX(Sheet1!$A$15:$A$100,SMALL(IF(Sheet1!$A$15:$C$100<>"",ROW(Sheet1!$A$15:$C$100)-ROW(Sheet1!$A$15)+1),ROWS(Sheet1!$A$15:$A15))),"")

Enter below formula in cell E2:

=IFERROR(INDEX(Sheet1!$D$15:$D$100,MATCH($D2,Sheet1!$A$15:$A$100,0)),"")

Enter below formula in cell F2:

=IFERROR(INDEX(Sheet1!$Q$15:$Q$100,MATCH($D2,Sheet1!$A$15:$A$100,0)),"")

Enter below formula in cell G2:

=IFERROR(INDEX(Sheet1!$W$15:$W$100,MATCH($D2,Sheet1!$A$15:$A$100,0)),"")

Enter below formula in cell H2:

=IFERROR(INDEX(Sheet1!$Z$15:$Z$100,MATCH($D2,Sheet1!$A$15:$A$100,0)),"")

Enter below formula in cell A2:

=IF($D2<>"",Sheet1!$Y$5,"")

Enter below formula in cell B2:

=IF($D2<>"",Sheet1!$I$6,"")

Enter below formula in cell C2:

=IF($D2<>"",Sheet1!$Y$8,"")

All these formulas are to be copied down.

You may download the excel file from below link wherein this has been illustrated:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/Index_Small_13.xlsx

Regards,

Amit Tandon

Dear Mr. Amit

Thank you for the response. However, the thing is that I want to use the entry sheet as a form. when I change the form number and other subsequent details, the earlier entered details should not disappear. I know this requires a vba code and I am working on it. But somehow, I am unable to find success as for every line item, to shift from entry sheet to history sheet, I need to write special instructions in the code and hence this makes it a cumbersome process.

Please guide me further if possible.

regards

Answer 2

Refer below images:

Image of Sheet1 (Source Data):

Image of Sheet2:

All formulas to be entered in Sheet2.

Enter below formula as an array formula (CTRL+SHIFT+ENTER) in cell D2:

=IFERROR(INDEX(Sheet1!$A$15:$A$100,SMALL(IF(Sheet1!$A$15:$C$100<>"",ROW(Sheet1!$A$15:$C$100)-ROW(Sheet1!$A$15)+1),ROWS(Sheet1!$A$15:$A15))),"")

Enter below formula in cell E2:

=IFERROR(INDEX(Sheet1!$D$15:$D$100,MATCH($D2,Sheet1!$A$15:$A$100,0)),"")

Enter below formula in cell F2:

=IFERROR(INDEX(Sheet1!$Q$15:$Q$100,MATCH($D2,Sheet1!$A$15:$A$100,0)),"")

Enter below formula in cell G2:

=IFERROR(INDEX(Sheet1!$W$15:$W$100,MATCH($D2,Sheet1!$A$15:$A$100,0)),"")

Enter below formula in cell H2:

=IFERROR(INDEX(Sheet1!$Z$15:$Z$100,MATCH($D2,Sheet1!$A$15:$A$100,0)),"")

Enter below formula in cell A2:

=IF($D2<>"",Sheet1!$Y$5,"")

Enter below formula in cell B2:

=IF($D2<>"",Sheet1!$I$6,"")

Enter below formula in cell C2:

=IF($D2<>"",Sheet1!$Y$8,"")

All these formulas are to be copied down.

You may download the excel file from below link wherein this has been illustrated:

http://globaliconnect.com/excel/Microsoft/DownloadFiles/Index_Small_13.xlsx

Regards,

Amit Tandon

Answer 3

Hi,

You might want to do this in a relational database like Access where you can establish one to many relationships.  I don't think you can do this in Excel.  Just a suggestion.

Regards,

John