Posts by MZING81

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Environ vba change to fit Onedrive

    To whom it may concern,


    I built some code years back for workbooks shared between coworkers. Unfortunately our Dept., wasn’t really warned of the unintended consequences of updating everything to run off of OneDrive. So now we’re getting vba errors on the file paths.


    How would I update the code below to work with the One drive Environment?

    Workbooks.Open filename:=(Environ(“userprofile”) &

    Thanks I appreciate any assistance that you can offer!!

  • 2 way lookup formula with greater than and less than

    Hey Everyone,


    I have a two way lookup formula that needs a little tweeking.


    =IF(I3<=$Z$1&I3>=$W$1,VLOOKUP(A3,'PREVIOUS MASTER1'!$A$1:$N$1200,MATCH(H3,'PREVIOUS MASTER1'!$H$1:$N$1,0)+7,FALSE),"")


    $Z$1 = 6/01/2014
    $Y$1 = SERIAL # OF 6/01/2014


    $V$1 = 11/1/2013
    $W$1 = SERIAL # OF 11/1/2013


    $T$1 = 6/1/2013
    $S$1 = SERIAL # OF 06/01/2013


    $H$1:$N$1 = Sunday, Monday etc.....


    The problem is that it pulls the data correctly, put if the date doesn't the criteria it still pulls a date from the array, instead of ignoring it.


    This was split from a larger formula that almost worked I thought if could at least get the one portion to work that maybe i can just have 3 formula columns instead, here's the main formula


    =IF(I3>=$Y$1,VLOOKUP(A3,MASTER!$A$1:$N$1200,MATCH(H7382,MASTER!$A$1:$N$1,0),FALSE),IF(I3<=$Z$1&I3>=$V$1,VLOOKUP(A3,'PREVIOUS MASTER1'!$A$1:$N$1200,MATCH(H7382,'PREVIOUS MASTER1'!$H$1:$N$1,0)+7,FALSE),IF(I3>=$S$1&I3<=$W$1,VLOOKUP(A3,'PREVIOUS MASTER2'!$A$1:$N$1200,MATCH(H7382,'PREVIOUS MASTER2'!$H$1:$N$1,0)+7,FALSE),"")))



    ANY HELPED WOULD BE GREATLY APPRECIATED.

  • index match formula help

    Hi Everyone


    I have a formula that needs tweeking.


    =INDEX($P$2:$BO$2,MATCH(120,INDEX(P4:BO4,0),0))


    The formula works fine as long as there is an exact match to the 120, and tried using approximate but it has been inaccurate. I want the formula to look to the cell with the next greatest number above 120 if it can find 120, and give the corresponding data in the indexINDEX($P$2:$BO$2.


    How to I convert this formula to account for greater than? Do i need to turn it into an Offset?


    Thanks
    MZING81

  • If formula to extract data ignoring duplicates

    Re: If formula to extract data ignoring duplicates


    My apologies I didn't realize that cross-posting would be such a huge issue. I almost figured out the issue. I can pull it off with two criteria, just not with one.



    [COLOR="#0000CD"]MODERATOR EDIT


    Please do not quote entire posts. When quoting follow these guidelines


    1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
    2. Quote ONLY the specific part of the post that is relevant - - not the entire post.


    This will keep thread clutter to a minimum and make the discussion easier to follow. Quote deleted.
    Thanks[/COLOR]

  • If formula to extract data ignoring duplicates

    Hello Everyone,


    I have formula that works decently, I have it extracting across the row for about 15 columns. It extracts data from another sheet that has about 1200 rows, and some rows are duplicates.


    =IF(ISNUMBER(SEARCH($J$29,'Sheet1'!$S:$S)),'Sheet1'!A:A,"")


    But the problem is that it pulls completely duplicate rows which is what I'm trying to avoid. I want it to ignore duplicate rows only if it meets two match criteria's. Its for a staffing report and dont want it to extract the same person with the same exact shift twice. The person's name is in column H and the shift is column J. I kinda hit a wall, and cant figure it out. I guess I may need < or > sybols possibly but not sure


    Any help would be greatly appreciated.



    MZING81

  • Automate repsonse to Msg Box

    Hello Everyone,


    I have some Macro's that utilize text to columns to parse data. That function works great except for the fact that it asks me to replace the contents of the destination cells; which it always need to answer yes. I tried some code that will ignore the message box, but that ceases to allow the macro to work properly. How can create a macro that will automate a yes answer whenever I get the stated message box; so that the message box will not even appear.



    Thank you


    And I appreciate any help that you can offer


    MZING81

  • Macro to find last cell with formatting

    Re: Macro to find last cell with formatting


    Thank you this helped!!




    Quote from pike;644639

    hi MZING81


    You could use the findformat or just loop tofind the last cell formated with boarder

    Code
    for each cell In worksheets("Sheet17").range("a1:a100")
    If cell.Borders(xlEdgeBottom).LineStyle = xlContinuous Then cell.Select
Next cell

  • Macro to find last cell with formatting

    HELLO EVERYONE,


    I Need a macro to find last cell with formatting. I have macros to find the last cell in a column, and macro's to delete all empty rows across all sheets. But I cant write one or even find one that will help me find the last cell in a column with cell formating, such as a border.



    Any help will be greatly appreciated. This is not cross posted


    Thank you


    MZING81

  • IF statement to extract data

    Re: IF statement to extract data


    I have also tried this below

    =IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2",IF(ISNUMBER(SEARCH("*BLAH-3*",J5)),"BLAH-3",IF(ISNUMBER(SEARCH("*BLAH-4*",J5)),"BLAH-4",""))


    But that didnt work either.

  • IF statement to extract data

    forum.ozgrid.com/index.php?attachment/50326/Hi everyone,



    I'm working on a formula to find multiple occurences of different text strings within a single cell. The problem is with one formula will find one text string, but I dont know how to tell to find the others. I have another one that can find either or, but wont return a blank cell if it cant find anything.


    =IF(ISNUMBER(SEARCH("*BLAH-2*",J5)),"BLAH-2","")


    =IF(SUM(COUNTIF(G67,{"*BLAH-2*","*GOOD-3*"}))>0,"BLAH-2","GOOD-3")


    All that I need is what i can use to have look for an additional "text" another Blah-3 along with that Blah-2. Ampersands dont work and neither does a comma or an wild-card. All I need is it to say if I cant find Blah-2 then look for Blah-3 etc....


    Any assistance will greatly be appreciated


    MZING81

  • Macro across all sheets, copy and paste

    Re: Macro across all sheets, copy and paste


    Thanks I'll give it a shot!! Seems like that would work!!



  • Macro across all sheets, copy and paste

    Hello Everyone,


    This is cross posted at http://www.excelguru.ca/forums…ll-sheets&p=5745#post5745


    hopefully its not an issue.


    Okay so here's the problem this formula only functions properly for the first sheet, not the other 100+. I need it to copy the cell in d9 and paste it in s9 down the whole column, but only in rows with data, and skip the empty rows. Unfortunately it does this in all the sheets, but at the same number of rows as in the first sheet, even though the row numbers with data vary from sheet to sheet.




    any help will be greatly appreciated


    MZING81

  • If function for dynamic drop down list

    Re: If function for dynamic drop down list


    forum.ozgrid.com/index.php?attachment/48300/

    Quote from NBVC;626698

    Hard to follow without a sample workbook. Can you post one, along with expectations?


    Here's a sample, it should have start and times based on criteria selected from the data validation lists. Also I would like to add a seclection for ALL criteria's, and to calculate the number of indivuals on the right side beside and below the times.

  • If function for dynamic drop down list

    [INDENT]Hello everyone,


    Thanks for all the help that I have receievd since being a memeber. I have a 5 data validation lists on one sheet. They each have specific criteria referencing another sheet, but it only a small portion functions. I have an if formula for an hourly staffing report, that refereneces each indivual day in the week, but only works on sunday.



    =IFERROR(IF('SCHEDULE WEEK ONE'!$J5="","",IF(AND(ISNUMBER(SEARCH($P$15,'SCHEDULE WEEK ONE'!$L5)),SEARCH($P$19,'SCHEDULE WEEK ONE'!$C5),SEARCH($P$23,'SCHEDULE WEEK ONE'!$E5),SEARCH($P$27,'SCHEDULE WEEK ONE'!$F5),SEARCH($P$31,'SCHEDULE WEEK ONE'!$G5)),(RIGHT('SCHEDULE WEEK ONE'!$J5,1))+0,"")),"")



    I want it to pull a start shift time and end shift time to match the criteria. I have two columns that are one for start time, and end time and another thats combined. the one shown is combined. This one doesnt not pull the end shift time! Hopefully I explained this properly. Also should I use something other than data validation list? [/INDENT]

  • How to use a time frame with a sumproduct formula.

    Hello Everyone,


    And thank you for all the help I've been getting from the forum's. I have formula, I'm trying to calculate the number of employees that working within a specific time frame of an hour. It's trying to calculate all the male employees present at 00:00 hours. And eventually hour each hour of the day, also with other attributes. Though the data it's using for hours is a shift time like 04:00-12:00 etc... So I think that's where I'm having the issue.


    Code
    =SUMPRODUCT(WEEKLYREPORT!$D$4:$D$1500,--(WEEKLYREPORT!$I$4:$I$1500=NOTES!$D$24),--(WEEKLYREPORT$J$4:$J$1500='DAY ONE'!C3),--(NOTES!$D$2:$D$90="M"))


    The first range is the column of gender, the second is a work location, the third is shift time with the single cell reference of the hour, 0:00, and the last range ,Notes, is the reference for attributes "M" that reference is to sum all the M's present in the first range.


    I used a formula like similar but with out any time info and worked perfectly.



    Thanks I appreciate any help that you can offer


    MZING81