Posts by xsmurf

  • Multiple returns with 2 criteria from another workbook

    Hi Carim,


    I will make a sample file in how the setup is now, with the formula's

    I just have to delete / change the sensitive information.


    For your questions

    1) Yes, the Production Reports sheet is the source for ALL the info.

    2) Yes, the Overview sheet is the main analysis sheet used for daily and weekly overview.

    I will put as much as possible info in this sheet.


    I have thought about Pivot tables, but not all users are familiar with excel, the setup that I have now is just the better one. Simple choose the first monday of the week and your overview will show all your info. Nice organised.

    Same goes for the advanced filter.


    If possible would like to keep the same setup, but if that is not possible, than it is what it is.

    Adjustments to make it more user friendly can always be done later.

    The information is more important.


    I do have to make the sample file in the weekend.

  • Multiple returns with 2 criteria from another workbook

    Carim or somebody else.

    Hi, I hope you are able to help me with a small problem I am having with the code you provided

    The code works perfect and made changes so it works perfect, but when I add a 3 criteria that looks at text, things go wrong.

    the code I am using now

    Code
    =IFERROR(INDEX('Production Reports'!$BL$1:$BL$2000,LARGE(IF(B15&C15&$AZ$3='Production Reports'!$B$1:$B$2000&'Production Reports'!$C$1:$C$2000&'Production Reports'!$BP$1:$BP$2000,ROW('Production Reports'!$C$1:$C$2000),0),SUM(('Production Reports'!$B$1:$B$2000=B15)*('Production Reports'!$C$1:$C$2000=C15)*('Production Reports'!$BP$1:$BP$2000=$AZ$3))-E15+1)),"0")

    In Column BL are my minutes and in Column BP is the category that those minutes belong too (I have 6 categories)

    When I have more than 1 result on a specific day it will retrieve the information perfectly, but only when the categories are the same

    if they are not the same all the info will be added to the first found entry.


    Also when the first entry has no info in column BL&BP but the second entry does, then all the info will be shown in the row of the first entry.



    Is there a way to change the code so it can look at text to ? extra columns and assign a number to a category will give the same results.


    Or is the formula not capable of doing 3 criteria?


    Hope somebody can help me

  • Multiple returns with 2 criteria from another workbook

    Carim Just a quick update:


    I have successfully implemented the solution, see code below, and it works perfect. All the information is displayed how it should.


    Code
    =IFERROR(INDEX('Database'!$T$1:$T$2002,LARGE(IF(F28&G28&$AW$8='Database'!$B$1:$B$2002&'Database'!$C$1:$C$2002&'Database'!$X$1:$X$2002,ROW('Database'!$C$1:$C$2002),0),SUM(('Database'!$B$1:$B$2002=F28)*('Database'!$C$1:$C$2002=G28)*('Database'!$X$1:$X$2002=$AW$8))-I28+1)),"")

    The retrieval of the information is working without any problems, however when I try to SUM the results it gives me a #value error.

    The code I am using, see below, works perfectly when I type in the value manually, but when used with the first code it gives me an #Value error.

    I tried to change the condition of the cells to Number / General, but nothing seems to help.

    Code
    =SUM(($AO$28:$AO$55+$AP$28:$AP$55+$AQ$28:$AQ$55)*(--($D$28:$D$55=D24)))/60


    Would you be able to assist ?

    Thank for any help you can give me.

  • Show duplicate through VBA, but exclude certain columns & rows within the selected range

    Hello,


    This forum has also helped me a lot over the years and I hope this will be the case now.

    I know there are a lot of ways to show duplicates values in excel, but I have not seen this yet so I don't know if this is possible.
    Let's say I have a range from C5 to X70, within that range I would like to see duplicates marked red.
    But within that range there a columns and rows that need to be excluded from turning red (so duplicates are aloud in these columns & rows)
    The code should only look for doubles in columns C / E / G / I / K / M / O
    The code should ignore doubles in row 55 / 56 / 57 / 58
    So if there is a name John in column C, but also a John in column K, both John's should highlight RED.
    A John in row 56 should NOT turn RED, because this is aloud.

    I want to use this with a Worksheet_Change event in VBA, and prefer not to use conditional formatting, because there will be a lot of copy & paste happing in this sheet.

    Can anybody help me out with my problem?

    A big thanks in advance for all the help that will be offered, I really appreciate it.

  • Updating data with userform, not working when having empty rows

    Hello,


    I use an userform to populate a sheet, and this works fine.

    I have added the option to edit / delete data from the data-sheet, and that works, however it updates the wrong rows when there are empty rows in the data-sheet.

    Every row of data has an serial number, their are no doubles.


    The code that I am using, just for updating (found on a forum and adapted to my needs)

    How can I change the code so it will ignore the empty rows (if there are any).

    Overwriting the wrong rows is not an option.


    Hope someone can help me, really appreciate all the help.

  • Multiple returns with 2 criteria from another workbook

    Hi,


    I hope to get some help here with a problem that I have.


    I have a workbook that retrieves information from different workbooks, this all works well.

    The main problem I have is when there is more then 1 result.


    Please see attached file with a sample.


    Just to clarify the sample is retrieving on a different sheet within the same workbook. The solution needs to be that it needs to retrieve that information from a different workbook.


    Hope you guys can help me out, I would really appreciated it.


    Thanks


    Production Reports_WIP.zip