Populate records depending on entry in another sheet

Hi Sambo kid

Thanks for the speedy reply. I have attached a sample document with some cells highlighted to try and explain better. Sheet 2 is the checklist that I have created. As you can see I have highlighted some cells.

The concept is to have the text highlighted in green populate the report tab as highlighted, so as going through the checklist a simple 1 is entered and the text as green cell and then it automatically populates the site report and can be saved as a PDF and it becomes a report for the customer. The same applies to the other highlighted cells and corresponding cells in the report tab.

I have followed the instructions for attaching the document so hopefully it will come through now as well.

Thanks again for your help.

DC

Please see the attached.
I used Array formulas to get this solution (meaning the formulas must be entered using Shift+Ctrl+Enter instead of just enter.
In B15, I entered this formula:
=IFERROR(INDEX(Checklist!A:A,SMALL(IF(Checklist!F:F=1,ROW(Checklist!F:F)-ROW(INDEX(Checklist!F:F,1,1))+1),ROW()-14)),"")
Once you enter the above formula with Shift+Ctrl+Enter, you should see brackets {} around the formula
Cells C15 and D15 are:
=IFERROR(INDEX(Checklist!B:B,SMALL(IF(Checklist!F:F=1,ROW(Checklist!F:F)-ROW(INDEX(Checklist!F:F,1,1))+1),ROW()-14)),"")
=IFERROR(INDEX(Checklist!H:H,SMALL(IF(Checklist!F:F=1,ROW(Checklist!F:F)-ROW(INDEX(Checklist!F:F,1,1))+1),ROW()-14)),"")

You can then copy these formulas down as far as you need.

Hi Gregb11

That is wonderful what you have done. Far more sophisticated that I could ever conceive. How on earth did you work that out. Very impressive.

If I add these formulas to the final checklist and report in the same format will they pull the information from the same cells even thought here will bemany times more cells in the columns? Be good to understand the formula a bit in case I need to amend or adjust some time.

Thanks very much for your help with this it is much appreciated.

Kind regards

David

I'm not sure I understand what you are asking, but, the formulas will work no matter how much data you have on the sheet "Checklist". HOWEVER, you will need to copy the formula down far enough (since I wrote the formulas to display nothing if there is nothing to display, you can copy down the formula much farther than you think you would need).

One thing to keep in mind that on the Report Sheet, if you change where you have the first row of data (row 15 in your file), the formulas will have to change slightly. At the end of each formula there is this:
ROW()-14)

The number 14 represents the row PREVIOUS to your data, so if you were to insert a row above that (so say you data now starts in row 16, you would change the number 14 to 15 in those 3 formulas).

Hi Greg11

I have written the formulas into the cells and it doesn't seem to be pulling the text through for all of the cells. I thought it would pull all the text through for each row within F column that had a 1 inserted as it does not seem to be pulling all the text through. Maybe I have set something up wrong. Would you mind having a look at the attached spreadsheet again and see if you can get it to work properly. This is still only a trial so when I get the final checklist etc sorted I will set it up the same as this spreadsheet and hoping I can then just use the formulas the same.

A couple of things:
1. For the formula in column D, you were missing an exclamation point - the correct one is here (with the exclamation point that was missing in RED
=IFERROR(INDEX(Checklist!H:H,SMALL(IF(Checklist!F:F=1,ROW(Checklist!F:F)-ROW(INDEX(Checklist!F:F,1,1))+1),ROW()-14)),"")
2. You need to copy the formulas down as far as needed (for as many rows that have a 1 in column F (Like I said, you can copy the formula down farther than that because I wrote it such that it will show nothing if there is no data to show.
I have attached the file.
Let me know if you have any other questions.