Trying to pull specific data from sheet1 to other sheets if it meets criteria

  • First time on this website. I am trying to generate a excel program that only pulls specific information from one sheet to another if the criteria is met. I work in the Navy and have made an excel sheet for my command. I am trying to put certain people in specific divisions to their corresponding sheets(division). For example. Sheet1 is my entire command sheet. Sheet2 is 400 division. Sheet 3 is 500 and so on. However I only want to pull only specific info and not the entire row.


    ex1.


    Sheet1(All Personnel)


    [TABLE="width: 500"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [td]

    D

    [/td]


    [td]

    E

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    LAST

    [/td]


    [td]

    FIRST

    [/td]


    [td]

    RANK

    [/td]


    [td]

    PROGRESS

    [/td]


    [td]

    DIVSION

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    SMITH

    [/td]


    [td]

    JOHN

    [/td]


    [td]

    E5

    [/td]


    [td]

    COMPLETE

    [/td]


    [td]

    400

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    DOE

    [/td]


    [td]

    JOHN

    [/td]


    [td]

    E9

    [/td]


    [td]

    IW

    [/td]


    [td]

    500

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    SMITH

    [/td]


    [td]

    DOE

    [/td]


    [td]

    O1

    [/td]


    [td]

    ROUTING

    [/td]


    [td]

    600

    [/td]


    [/tr]


    [/TABLE]



    AND SO ON...


    THIS INFO ABOVE IS THE ONLY INFO I WANT TO DISTRIBUTE TO THE CORRECT SHEETS(DIVISION). I do not want the whole row. Just the columns A - E. From Column F and on there are other info but not all the sheets contain the same info. Column E has a dropdown list for those division.


    My other sheets are titled as followed.


    Sheet2(400)
    Sheet3(500)
    Sheet4(600)


    And so on.


    Also, VBA is not widely used in my command and I do not believe it would work if the developer tab is not activated.
    I appreciate your advice. Thank you.

  • Re: Trying to pull specific data from sheet1 to other sheets if it meets criteria


    I sincerely appreciate your service in the Navy.


    I do not know of a formula that would do it without leaving empty rows. That is not to say that it doesn't exist. A macro could do it without empty rows, but you already said you did not want to use those. You could have it transfer all the rows and then use a filter to hide the wrong divisions. Then if you wanted to do formulas for the list on each page you would have to make sure that there is an if or countif verifying that the division in the row is correct before including it in your function, which I would suggest using array functions.


    I am sorry that I cannot be of more help with this problem.

  • Re: Trying to pull specific data from sheet1 to other sheets if it meets criteria


    Quote from crushdrinker06;770473

    I sincerely appreciate your service in the Navy.


    I do not know of a formula that would do it without leaving empty rows. That is not to say that it doesn't exist. A macro could do it without empty rows, but you already said you did not want to use those. You could have it transfer all the rows and then use a filter to hide the wrong divisions. Then if you wanted to do formulas for the list on each page you would have to make sure that there is an if or countif verifying that the division in the row is correct before including it in your function, which I would suggest using array functions.


    I am sorry that I cannot be of more help with this problem.


    Do you have an equation I could tryout?

  • Re: Trying to pull specific data from sheet1 to other sheets if it meets criteria


    Code
    1. =IF(Sheet1!$E2=400,Sheet1!A2,"")


    This would be put into A2 on each division sheet and the filled to the D column and filled down. With this style you would have to change the 400 to the appropriate division each time and if there is ever a change in the name of the division.


    You could also change the 400 to reference a certain cell which can be on the division page or on a separate page entirely. This would allow for easy change of division name at any time.
    I have also attached a file showing both options. I hope this helps.
    ozgrid.com/forum/core/index.php?attachment/69066/


    Also once you get things the way you want them simply apply a filter and uncheck the (blanks) option. This will hide any rows that are not in that division.

  • Re: Trying to pull specific data from sheet1 to other sheets if it meets criteria


    Ok, so the formula is working out great for me. However, the blanks are annoying. Sheet1(Command) has 200 rows while sheet2(division) and on have only 50. So should I just extend the sheet down to 200 in the divisions or is there conditional formatting or a formula to prevent blanks above and below the bulk of the information?

  • Re: Trying to pull specific data from sheet1 to other sheets if it meets criteria


    I don't know of any conditional formatting that would do this, but as stated you should be able to filter each page individually unchecking the blanks. This would hide all the blank rows. As far as

    Quote

    Sheet1(Command) has 200 rows while sheet2(division) and on have only 50. So should I just extend the sheet down to 200 in the divisions


    I believe you are asking how far down to fill the formula. If that is what you are asking then in each sheet you would need to fill it down however many rows there are in your first sheet. If you are asking something else please explain since I am missing it.

  • Re: Trying to pull specific data from sheet1 to other sheets if it meets criteria


    Quote from crushdrinker06;770558

    I don't know of any conditional formatting that would do this, but as stated you should be able to filter each page individually unchecking the blanks. This would hide all the blank rows. As far as


    I believe you are asking how far down to fill the formula. If that is what you are asking then in each sheet you would need to fill it down however many rows there are in your first sheet. If you are asking something else please explain since I am missing it.


    Thank you for your help. What I ended up doing is letting all sheet2(division) and on match the same row amounts and sheet1(command) so that there is not disparity between sheets. Then I use the filter to take out the blank cells and only show the cells that meet the criteria for the division in the sheet. So this is working out but its a lot of little fixes which is fine. Because once its established and set up it should be easy to maintain. So now another problem I have made for myself. How do you allow multiple sheets with the same exact columns update the sheet1(command). So example:


    Sheet1(Command)


    [TABLE="width: 500"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Division

    [/td]


    [td]

    Status

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    400

    [/td]


    [td]

    IW

    [/td]


    [/tr]


    [tr]


    [td]

    Bill

    [/td]


    [td]

    500

    [/td]


    [td]

    Routing

    [/td]


    [/tr]


    [tr]


    [td]

    Steve

    [/td]


    [td]

    600

    [/td]


    [td]

    Complete

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    600

    [/td]


    [td]

    No Progress

    [/td]


    [/tr]


    [/TABLE]


    Sheet2(400 Division)


    [TABLE="width: 500"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Division

    [/td]


    [td]

    Status

    [/td]


    [/tr]


    [tr]


    [td]

    John

    [/td]


    [td]

    400

    [/td]


    [td]

    IW

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]




    Sheet3(500 Division)


    [TABLE="width: 500"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Division

    [/td]


    [td]

    Status

    [/td]


    [/tr]


    [tr]


    [td]

    Bill

    [/td]


    [td]

    500

    [/td]


    [td]

    Routing

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]




    Sheet4(600 Division)


    [TABLE="width: 500"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Division

    [/td]


    [td]

    Status

    [/td]


    [/tr]


    [tr]


    [td]

    Steve

    [/td]


    [td]

    600

    [/td]


    [td]

    Complete

    [/td]


    [/tr]


    [tr]


    [td]

    Ted

    [/td]


    [td]

    600

    [/td]


    [td]

    No Progress

    [/td]


    [/tr]


    [/TABLE]



    The other sheets are working copies for the representative of that division. The Rep is suppose to update the status for the Command(sheet1) but I do not want them to touch sheet1 cause people are stupid or sometimes they will be lazy. So how do I make it to where when the Rep updates their sheet(division) it automatically updates sheet1(command). For Example lets say the 600 Division rep(Sheet4) updates Ted from No progress to IW. what formula do I use to allow the automatic updates to sheet1(command) once they change the status. Also, status has a dropdown so there are specific list already in place on division sheets.

  • Re: Trying to pull specific data from sheet1 to other sheets if it meets criteria


    You can only reference one way. In other words If A1 has a formula =A2 then A2 cannot have A1 in a formula. This still applies when dealing with different sheets. So allow me to ask this. Are you okay with only inputting into the division sheets. If so I might be able to make the Command sheet update based on the other sheets. This would only apply to any cells on the command sheet that would be pulled from the division sheets. The only way I know of that would allow communication both ways is macros.


    I have attached a workbook showing how you could update the command sheet based on the other sheets, but with this style you would need to add new people.
    ozgrid.com/forum/core/index.php?attachment/69087/


    Also You can add another sheet which would be able to update the division sheets. Then the command sheet could be updated by the division sheet.

  • Re: Trying to pull specific data from sheet1 to other sheets if it meets criteria


    Quote from crushdrinker06;770662

    You can only reference one way. In other words If A1 has a formula =A2 then A2 cannot have A1 in a formula. This still applies when dealing with different sheets. So allow me to ask this. Are you okay with only inputting into the division sheets. If so I might be able to make the Command sheet update based on the other sheets. This would only apply to any cells on the command sheet that would be pulled from the division sheets. The only way I know of that would allow communication both ways is macros.


    I have attached a workbook showing how you could update the command sheet based on the other sheets, but with this style you would need to add new people.
    ozgrid.com/forum/core/index.php?attachment/69087/


    Also You can add another sheet which would be able to update the division sheets. Then the command sheet could be updated by the division sheet.


    So here is the formula I have.


    =IF('MANAGER ONLY'!$D$3:$D$200="CCC",'MANAGER ONLY'!A$3:A$200,"")


    It is working out great but is there a way to get rid of blanks using formula with this or will I just have to suck it up and use the filter method?

  • Re: Trying to pull specific data from sheet1 to other sheets if it meets criteria


    I am guesing that that formula is going into the division sheets. the only way that I know of to use formulas and avoid use of macros was shown in my last post. If you do not like that then I am afraid you will have to

    Quote

    suck it up and use the filter method

  • Re: Trying to pull specific data from sheet1 to other sheets if it meets criteria


    Quote from crushdrinker06;770885

    I am guesing that that formula is going into the division sheets. the only way that I know of to use formulas and avoid use of macros was shown in my last post. If you do not like that then I am afraid you will have to


    TOUCHE my friend. Thank you.