Automatically Pull Data from Master Sheet

I have a spread sheet with 5 sheets, sheet (SH)1 is the master sheet, sheets 2, 3, 4 & 5 are different routes, on the master sheet i have 4 columns, col1 route number, col2 is contract number, col3 is customer name and col4 is address, i want to be able to input info in to each row and the info is automatically droped in to the route sheet that corresponds to the number in the route column. is this possible in excel.

I hope i am making sence, looking forward to your reply

One thought on “Automatically Pull Data from Master Sheet

  1. Kat

    Hello

    You need to enter two formula and then repeat across the sheets.

    In cell A2 in sheet 2 type the following formula:

    =IF(ROWS(A$2:A2)<=COUNTIF('SH(1)'!$A$2:$A$10000,2),2,"")

    Then on the same sheet, but in cell B2 type this formula:

    =IF(ROWS(A$2:A2)<=COUNTIF('SH(1)'!$A$2:$A$10000,2),INDEX('SH(1)'!B$2:B$10000,SMALL(IF('SH(1)'!$A$2:$A$10000=$A2,ROW('SH(1)'!$A$2:$A$10000)-ROW('SH(1)'!$A$2)+1),ROWS(B$2:B2))),"")

    To the make the second formula work you need to hold and shift and enter – this makes it an array formula.

    Now drag the second formula across your four columns and down as many rows as you want – it can be more rows then there are entries!

    Now repeat this process across the other route sheets you have, but ensure you change a couple of reference for each…

    =IF(ROWS(A$2:A2)<=COUNTIF('SH(1)'!$A$2:$A$10000,2),2,"")
    Near the end of this formula where it says ,2),2,"") replace the two instances of 2 with the next route number, so for example 3. The above formula now becomes:

    =IF(ROWS(A$2:A2)<=COUNTIF('SH(1)'!$A$2:$A$10000,3),3,"")

    =IF(ROWS(A$2:A2)<=COUNTIF('SH(1)'!$A$2:$A$10000,2),INDEX('SH(1)'!B$2:B$10000,SMALL(IF('SH(1)'!$A$2:$A$10000=$A2,ROW('SH(1)'!$A$2:$A$10000)-ROW('SH(1)'!$A$2)+1),ROWS(B$2:B2))),"")

    Again with the second formula you need to change the route reference. Where it says )<=COUNTIF('SH(1)'!$A$2:$A$10000,2), change the reference to 2 to the next route reference, for example 3. So the above formula becomes:

    =IF(ROWS(A$2:A2)<=COUNTIF('SH(1)'!$A$2:$A$10000,3),INDEX('SH(1)'!B$2:B$10000,SMALL(IF('SH(1)'!$A$2:$A$10000=$A2,ROW('SH(1)'!$A$2:$A$10000)-ROW('SH(1)'!$A$2)+1),ROWS(B$2:B2))),"")

    If you want to see a workbook detailing this then let me know I can email one!

    I hope it helps!

Leave a Reply

Your email address will not be published. Required fields are marked *