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.

    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!!

    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),"")))


    Hi Everyone

    I have a formula that needs tweeking.


    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?


    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.


    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.

    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.


    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.


    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


    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

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


    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


    Re: IF statement to extract data

    I have also tried this below


    But that didnt work either. 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.



    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


    Re: Macro across all sheets, copy and paste

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

    Hello Everyone,

    This is cross posted at…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


    Re: If function for dynamic drop down list

    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.

    [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.


    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]

    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.

    =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