Posts by blade671

    I am trying to transfer information from Excel into ONE word document. Also, I would like the fields to be updated when excel is updated. Pretty much the word document will be print out. So I know how to use mail merge but I want to move lists of information to one pade instead of having multiple pages. Also, if there is more information added(like if there is extra rows) to the excel doc would it automatically add another row. How would I do that? if you guys need more clarification just ask. Thank you.

    Re: Comparing people to one another for stat help.


    Quote from Kenneth Hobson;780528

    Were you needing VBA for that? An =CountIf() for the win and loss columns should suffice. I would probably use VBA to fill the pick list so I could sort and remove duplicates.


    You might also look into Filter or AdvancedFilter.



    Thanks


    See the above post for more in depth views.

    Re: Comparing people to one another for stat help.


    Honestly, VBA is not something i am good at using but i should start learning. So I also have split tables for every individual player to show their entire season. So for year 2012 I have 8 Tables with 16 games (Rows) of information. It looks like this:


    [TABLE="width: 479"]

    [tr]


    [TD="colspan: 7"]Regular Season Weeks 1-14[/TD]

    [td][/td]


    [/tr]


    [tr]


    [td]

    Week

    [/td]


    [td]

    Name

    [/td]


    [td]

    Score

    [/td]


    [td]

    Opponents Score

    [/td]


    [td]

    Oppenent

    [/td]


    [td]

    Point Difference

    [/td]


    [td]

    Win

    [/td]


    [td]

    Loss

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]127.06[/TD]
    [TD="align: right"]94.1 [/TD]

    [td]

    James

    [/td]


    [TD="align: right"]32.96[/TD]
    [TD="align: right"]1[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]110.76[/TD]
    [TD="align: right"]108.58 [/TD]

    [td]

    Julius

    [/td]


    [TD="align: right"]2.18[/TD]
    [TD="align: right"]1[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]101.86[/TD]
    [TD="align: right"]109.68[/TD]

    [td]

    Carlos

    [/td]


    [TD="align: right"]-7.82[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]89.06[/TD]
    [TD="align: right"]90.88 [/TD]

    [td]

    Frankie

    [/td]


    [TD="align: right"]-1.82[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]81.6[/TD]
    [TD="align: right"]130.2 [/TD]

    [td]

    Taylor

    [/td]


    [TD="align: right"]-48.6[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]130.96[/TD]
    [TD="align: right"]84.6 [/TD]

    [td]

    Daniel

    [/td]


    [TD="align: right"]46.36[/TD]
    [TD="align: right"]1[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]90.9[/TD]
    [TD="align: right"]103.42 [/TD]

    [td]

    Justin

    [/td]


    [TD="align: right"]-12.52[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td]

    8

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]106.68[/TD]
    [TD="align: right"]46.36 [/TD]

    [td]

    James

    [/td]


    [TD="align: right"]60.32[/TD]
    [TD="align: right"]1[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [td]

    9

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]114.18[/TD]
    [TD="align: right"]108.44[/TD]

    [td]

    Julius

    [/td]


    [TD="align: right"]5.74[/TD]
    [TD="align: right"]1[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [td]

    10

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]159.84[/TD]
    [TD="align: right"]90.6 [/TD]

    [td]

    Carlos

    [/td]


    [TD="align: right"]69.24[/TD]
    [TD="align: right"]1[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [td]

    11

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]85.48[/TD]
    [TD="align: right"]103.8 [/TD]

    [td]

    Frankie

    [/td]


    [TD="align: right"]-18.32[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td]

    12

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]97.82[/TD]
    [TD="align: right"]112.96 [/TD]

    [td]

    Taylor

    [/td]


    [TD="align: right"]-15.14[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td]

    13

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]109.38[/TD]
    [TD="align: right"]110.94 [/TD]

    [td]

    Daniel

    [/td]


    [TD="align: right"]-1.56[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td]

    14

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]159.48[/TD]
    [TD="align: right"]140.18 [/TD]

    [td]

    Justin

    [/td]


    [TD="align: right"]19.3[/TD]
    [TD="align: right"]1[/TD]
    [TD="align: right"]0[/TD]

    [/tr]


    [tr]


    [TD="colspan: 5"]Playoffs[/TD]

    [td]

    Win/Loss

    [/td]


    [TD="align: right"]7[/TD]
    [TD="align: right"]7[/TD]

    [/tr]


    [tr]


    [td]

    15

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]103.64[/TD]
    [TD="align: right"]109.62[/TD]

    [td]

    Julius

    [/td]


    [TD="align: right"]-5.98[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td]

    16

    [/td]


    [td]

    Jared

    [/td]


    [TD="align: right"]110.4[/TD]
    [TD="align: right"]140.42[/TD]

    [td]

    Justin

    [/td]


    [TD="align: right"]-30.02[/TD]
    [TD="align: right"]0[/TD]
    [TD="align: right"]1[/TD]

    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [td]

    Win/Loss

    [/td]


    [TD="align: right"]0[/TD]
    [TD="align: right"]2[/TD]

    [/tr]


    [/TABLE]


    So there are currently going to be 8 Tables 2012, 10 in 2013, 12 in 2014, 12 in 2015, 14 in 2016. So should i do it this way or should i have one full table with all the information? And if i used =countif() how would i create the formula to include all the information plus use a drop down list for both the opponent and player?

    Hey guys,


    I am generating a stat listing for my fantasy football league. We have about five years worth of data. Pretty much once I input all the data I am trying to create drop down list that if when they drop down it will show the record of each player comparing it to the other. I wont to know the how to show the record they have against another player. So for example. if there are 16 games and 8 players. I want to know the record of what player A did against Player B. Pretty much there win/loss record comparison. So Player A has played all 16 games but has only challenged player B 3 times. What is there record against each other.


    [TABLE="width: 500"]

    [tr]


    [td]

    Name

    [/td]


    [td]

    Win

    [/td]


    [td]

    Loss

    [/td]


    [td]

    Opponent

    [/td]


    [/tr]


    [tr]


    [td]

    Player A

    [/td]


    [td]

    2

    [/td]


    [td]

    1

    [/td]


    [td]

    Player B

    [/td]


    [/tr]


    [tr]


    [td][/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


    I obviously want this to work with a drop down list. And there is about 5 years worth of data as I said before. So I would want it to compare all the data over those 5 years. I am working on each list and showing each win/loss for every week for all 5 years. So can anyone help.

    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?

    I am using the following formula.


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


    I am using a dropdown box for Column D. In the drop down list there are multiple text in their such as ADMIN, MAINT ADMIN, ARO and so on. However, when I apply the text to the formula I get the #NAME? error. But if I use this formula like this:


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


    It does exactly what I want it to do. So how do I allow the excel to accept the first formula with the text instead of the number? What do I need to do to allow the criteria to look up text?

    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


    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


    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?

    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.