Posts by etaf

  • Using VLOOKUP to Get Data From Another workbook

    Re: Using VLOOKUP to Get Data From Another workbook


    both workbooks need to be open


    then use
    =VLOOKUP(A1,[Book1]Sheet1!$C$1:$F$300,2,FALSE)


    to pick up the other data you change the 2 to 3,4,


    Which assumes same directory
    the data is is a workbook called book1
    on sheet1
    cell range is row 1 to 300


    Where are the two files to be saved ?


    to refer to a different directory
    'C:\Reports\[Budget.xlsx]Annual'!C10:C25

  • Auto populate cells to the right of a dropdown box

    Re: Auto populate cells to the right of a dropdown box


    you just need to format those cells J24 onwards as a date - its returning the date , but displaying as a number - so a simple format will solve that
    the age is correct - I simply changed to a number in the table -so a quick way to get rid of all the "real" data, was just to put, 1,2,3 etc - have a look and change to the real dob and age , as this is a public forum , you should not put any personal data or confidential data online as its part of data protection act etc


    I have renamed the sheet - so it could have been called sheet2 - i just thought TeamData was better
    then all you needed to do was add
    sheet2! infront of the range
    =IF($C24="","",VLOOKUP($C24,Sheet2!$N$1:$Y$38,2,FALSE))
    as above


    as i moved to a new table - all the namedranges have #ref now and so I added a named range called Surname so the dropdown list would also extend


    but the range that is used was fixed to $N$1:$Y$38
    so if you add a new entry in row 39
    you would need to update all the formulas in the vlookup to now show     $N$1:$Y$39

    in 2010 you can over come that issue , by using a table so I selected the area     $N$1:$Y$38 and applied a table to it and rather than have the default table1 or table2 I choose to rename the table to something meaning full and called it TeamDataTBL
    Now instead of having to put in to a formulas Sheet2!$N$1:$Y$38 I can just put the name TeamDataTBL ( like named ranges) only now when you add to the bottom of the table it will automatically update and be included in the range named TeamDataTBL and so the vlookup continues to work and include the additional rows

    hope that helps -if not ask some more questions

    see attached


    i hope I have not confused further

  • Auto populate cells to the right of a dropdown box

    Re: Auto populate cells to the right of a dropdown box


    Noticed cytop has removed attachements


    I have EDITED - I have removed DoB, Age, email, phone for dummydata and removed the sheet2


    you can use the sheet name infront of the range


    say you added to sheet 3
    then
    =IF($C24="","",VLOOKUP($C24,Sheet3!$N$1:$Y$38,2,FALSE))


    Now i have moved to a new sheet and renamed the sheet


    i have also made the data in that sheet into a table and refered to the table name - which now allows you to add new members to the data and the lookups will automatically update to include the new range
    BUT that may only work in version 2010


    what version do you have and run ?
    I may have helped tofar in the example




    Older versions 2003 - use List and can do the same - so i can talk you through



  • Auto populate cells to the right of a dropdown box

    Re: Auto populate cells to the right of a dropdown box


    vlookup should do that for you


    you are not using sheet 2 at all - you could have the database elesewhere


    put in E24
    =IF($C24="","",VLOOKUP($C24,$N$1:$Y$38,2,FALSE))


    change the ,2, to the column you want to look up , as you move across the row ,


    see attached

  • Decreasing Stock Values

    Re: Decreasing Stock Values


    not understanding the spreadsheet - Stocklevel appears in a number of places - where would you populate the current stock level - is this ROW 18


    you also have a heading for Parts Required
    [TABLE="width: 200"]

    [tr]


    [td]

    38

    [/td]


    [td]

    44

    [/td]


    [td]

    48

    [/td]


    [td]

    51

    [/td]


    [td]

    58

    [/td]


    [/tr]


    [/TABLE]
    But in the Orders and Stock levels you seem to repeat the heading
    [TABLE="width: 280"]

    [tr]


    [TD="class: xl66, width: 40, align: right"]38[/TD]
    [TD="class: xl66, width: 40, align: right"]48[/TD]
    [TD="class: xl66, width: 40, align: right"]48[/TD]
    [TD="class: xl66, width: 40, align: right"]38[/TD]
    [TD="class: xl66, width: 40, align: right"]48[/TD]
    [TD="class: xl66, width: 40, align: right"]38[/TD]
    [TD="class: xl66, width: 40, align: right"]58
    [/TD]

    [/tr]


    [/TABLE]


    in stock levels for example on
    cell B18
    use the orders section (as that heading seems to match )


    B17 - B11


    and say
    IF( B17-B11 <= Minimum stock level number , "reorder", "OK")

  • comparing cells and return number

    Re: comparing cells and return number


    i have added
    = IF( OR($F6="",$G6="",J6="",K6=""),0,
    to the first part of the formula and it will need a bracket ) at the end


    = IF( OR($F6="",$G6="",J6="",K6=""),0,IF(AND($F6=$G6,J6=$F6,K6=$G6),20, IF(AND($F6=$G6,J6=K6),15, IF(AND(J6=$F6,K6=$G6),10,IF(AND(OR(J6=$F6,K6=$G6),OR(AND($F6>$G6,J6>K6),AND($F6<$G6,J6<K6))),4,IF(OR(AND($F6>$G6,J6>K6),AND($F6<$G6,J6<K6)),3,IF(OR(J6=$F6,K6=$G6),1,0)))))))


    it will set the points to zero 0
    if any one of the cells - team score results or players score prediction is blank

  • comparing cells and return number

    Re: comparing cells and return number


    ok so all the player does is put a score for home and visitor


    then you base points on the results


    quite a lengthy algorithm here


    1. If the bet is completely correct (4-3) -> Return 10 (points).
    this is a separate rule - if they predict a tie with the correct goals ???
    In case of tie (like 3-3, -> Return 20 (points)

    2. If the bet has a correct winner, with correct goals for eather team (4-x / x-3) -> Return 4 (points)

    3. If the bet has a correct winner (home/visitor) -> Return 3 (points)

    4. If the bet has incorrect winner but amount of goals for eather team correct -> Return 1 (point)

    5. If the bet is completely wrong -> Return 0 (points)

    So I think this will work - BUT I HAVE NOT checked for all permutations


    =IF(AND($F6=$G6,J6=$F6,K6=$G6),20,IF(AND(J6=$F6,K6=$G6),10,IF(AND(OR(J6=$F6,K6=$G6),OR(AND($F6>$G6,J6>K6),AND($F6<$G6,J6<K6))),4,IF(OR(AND($F6>$G6,J6>K6),AND($F6<$G6,J6<K6)),3,IF(OR(J6=$F6,K6=$G6),1,0)))))


    see attached and the area highlighted in green has the formula for all your sample results and teams
    if you change any cells in F6:G10
    and the teams J6:K10, M6:N10 and P6:Q10 will change my results in
    L20:R24


    see if it works OK

  • comparing cells and return number

    Re: comparing cells and return number


    you can do with a nested if and also a vlookup set


    but a sample sheet on how its laid out may help here - if you could attach


    as there appears to be quite a few elements - and not sure how they are laid out

  • Create orders log and monthly report for all orders

    Re: Create orders log and monthly report for all orders


    is "the next delivery date" on the orders sheet , the same as the "delivery date" on the log sheet?


    if you slightly restructure the Order sheet - and use table - you could create a pivot table which can be refreshed to provide the log just by clicking on the ! for each pivot


    same for the monthly report


    see the attached with two sheets added for the pivot table

  • Need Excel to return first and last value from a specific range

    Re: Need Excel to return first and last value from a specific range


    Q2 and Q3 contains the names of the Worksheets - so should be populated with all the sheets - I have just done for January and February



    =SUMPRODUCT(SUMIF(INDIRECT("'"&$Q$2:$Q$3&"'!$A$2:$A$100"),$B3,INDIRECT("'"&$Q$2:$Q$3&"'!D$2:D$100")))


    then it will do a 3D look across all the sheets for the week number


    as the departments are in specific columns - then the formula just needs to be changed for each column to sum


    Then you will need to change the formula for each department
    and also need to unmerge the week numbers


    see attached -

  • Conditional formatting to show when data cell contents are used on other sheets

    Re: Conditional formatting to show when data cell contents are used on other sheets


    you could use a vlookup or count
    to get the true / false return i have wrapped in a not and iferror


    this will check both the stories and the comic strip


    =NOT(AND(ISERROR(VLOOKUP(A3,Stories!$C$1:$C$100,1,FALSE)), ISERROR(VLOOKUP(A3,'Comic Strip'!$C$1:$C$100,1,FALSE))))
    or
    =NOT(AND(ISNA(VLOOKUP(A3,Stories!$C$1:$C$100,1,FALSE)), ISNA(VLOOKUP(A3,'Comic Strip'!$C$1:$C$100,1,FALSE))))


    for the settings used on both sheets


    just to check the rules
    on the summary sheet stories and comics are the columns fixed
    so that a comic strip character will always be on column D in the stories sheet and on the comic sheet - if so that should be easy to change the lookup array


    see the attached spreadsheet - I have only formatted the settings sheet