Posts by pangolin

    Re: Find a ship date in one table that is >= a date in another table for a batch numb

    this is one approach

    =IFERROR(INDEX(tblShipStatus[ShipDate],SUMPRODUCT(ROW(tblShipStatus[JobNum])*(tblShipStatus[JobNum]=Table_DMR_ShipStatus[[#This Row],[Batch]])*(tblShipStatus[ShipDate]>=Table_DMR_ShipStatus[[#This Row],[DMR Date]])*(tblShipStatus[PartsQuantity]=Table_DMR_ShipStatus[[#This Row],[Shipped Quantity]]))-1),"")

    however this formula would need to be tweaked if there are multiple shipped quantity with same values on different dates (currently it seems that is not happening)

    Re: Generate values in a table based on dropdown selection (complex data)

    from what I have understood create a List using Data Validation (Data Validation>>Settings>>List wher you select cols B1 to F1)

    Second...suppose the list has been created in Cell B28 then

    in Cell H3 you put this formula and copy paste down


    Re: Unzip Files using VBA

    thanx for your replies....Holycow that worked fantastic...many thanx

    I got confused in using the "Dir" command and hence getting a run time error when I was providing this as a value

    I have also removed the line for creation of new folder

    the new code is here for benefit of everybody

    Hi Friends...

    probably this question has been asked earlier but I am not able to find a fix for the same

    this code for unzipping files works great excpet that it requires the user to select the file...I wanted the same to be automated

    specifically can someone tell me how exactly to change this line

    fileName = Application.GetOpenFilename(filefilter:="Zip Files (*.zip), *.zip", MultiSelect:=False)

    since if I supply the filename and path as a strng then I get runtime error

    help appreciated


    Re: Count cells adjacent to same value until sum of those cells reaches certain value

    I am not saying this is the only way and I hope some Member comes out with a combined formula but one approach can be create a handler column in this case in Col I

    where you put this formula

    =SUM(($E$2:E2)*(($D$2:D2)=D2)) and copy down

    then on Col G you can put this formula


    this will give you the necessary values...(pl check again)

    Note both these formulae are array formula and need to be conformed by Ctrl+Shift+Enter

    Re: perform number format if command button is clicked

    why dont you try couching it within commandbutton_click

    for eg

    Private Sub CommandButton1_Click()
    '' Place your code here
    End Sub

    then whenever you click the commandbutton the code will get executed

    the commnadbutton_click will be autoimatically available when you go into the UserForm in VBA and click the commandbutton

    Re: Future year asset replacement costs modelling

    in your example I havent understood why you havent included the value of 951 for 2024-25 in your per what I have understood from your logic that value should also be included

    having said that see this formula


    put it in Row 5 and copy down

    this works after including the value for year 2025 as above

    see the attached file....the formula is highlighted therein

    Re: Spread a total across specific months

    this is one method


    although for the last month it leaves a higher remainder......check if thats ok or else I will tweak the formula likewise

    Re: Finding irregularities in a pattern

    not sure if this is really what you want but see if this helps


    it starts from Cell C3 and cehcks if alphabets a to g come in the correct sequence, if yes it will give REG else IRREG

    one can even use it to conidtionally format

    Re: Excel &quot;sudoku&quot;



    Not sure if you have heard of SOLVER utility within excel???

    using that I got the following values for your problem

    A1>>91 B1>>275 C1>>134 B2>>250 A3>>9 B3>>275 C3>>66

    remaining cells zero...try it

    Re: Optimization Constraints

    do you really expect soimeone to reply to this kind of post

    upload a file with this data and the results you expect if you want someone to seriously consider your post

    Re: index and match question

    you can use this formula


    note this is an array formula and needs to be conformed by Ctrl+Shift+Enter

    also for some combimnations the value does not exist and this will return an NA error hence couch this formula within IFERROR

    Re: Counting with multiple entries in multiple columns

    you can use this formula


    note this is an array formula and needs to be confirmed by Ctrl+Shift+Enter

    Re: Populate other cells based on specific criteria

    use this formula


    note this is an array based formula and needs to be conformed by Ctrl+Shift+Enter

    also note that do not use this formula in Column G as it will replace the manual entries in that column....therefore use it in another column lets say H