Repost - Copying data to 'transfer page' with multiple matches

[PippiLaRue]

This thread was closed last night due to an incorrect error on the moderators behalf. This is a SEPARATE question to my other post, albeit, for the same excel spreadsheet.

***********************
Hello,

I'm in the process of compiling a Non Conformance Register in Excel that can be exported to Word for a report that can be generated for clients. I have already built this in Microsoft Access, but my limited knowledge of coding has broken the database, and the last three weeks that I've spent looking at it has been to no avail, so I am trying something new, and possibly easier - at least for the rest of my company to use.

Now. I've got the worksheet linking the data to the transfer page properly - however, this only works if there is one line relative to the NCR. An NCR works like this:

1 - NCR
2 - Root Cause of NCR
3 - Corrective Action of NCR (immediate correction)
4 - Preventative Action of NCR (long term)

Any NCR may have a number of root causes, or one root cause, but a number of corrective or preventative actions assigned to it, and/or accountable & responsible people to fix it.

For this occassion only - If you look at NCR-0045 as my example (in the spreadsheet, 'Data'). I can easily bring back the first line, but I need to bring back ONLY the cells that are populated beneath it, and if empty - to leave blank.

Again, I can work out how to obtain the first line of information, I just can't work out how to obtain the secondary, third or fourth lines that correspond to the initial NCR number.

As this information is sensitive, all data available has been changed - so please excuse the ABC etc etc.

Any help would be greatly appreciated!

Pip

NCR-CODING TEST.xlsm

[arlu1201]

Is this solved?

If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.

[PippiLaRue]

No, not solved in the sense of the question/query itself. No one has been able to help me. What I've done is compile a pivot table which obviously extracts the data.
That said, I have another query in which I am needing help to refresh/update the pivot table based upon the drop down on the Control Page.

[Pete_UK]

Put this formula in cell AN2 of the Data sheet:

Please Login or Register  to view this content.

and copy it down to the bottom of your data (or beyond).

Then in the Transfer sheet you can have this formula in AN2:

Please Login or Register  to view this content.

Put this formula in A2 of the Transfer sheet:

Please Login or Register  to view this content.

then copy it across into B2:AM2. You will need to set up the appropriate format for dates etc., and then you can copy all the formulae on row 2 down as far as you think you need them.

Then you can just choose your NCR number in D7 of the Control sheet and the display on the Transfer sheet will automatically change.

Hope this helps.

Pete

[PippiLaRue]

Thanks Pete_UK!! Perfect!!