Posts by Max1616

    Re: Formula to search for date and extract average data to the beginning of year

    There may be an easier way to do this, but this is the best I can come up with to make it truly dynamic.

    I will only show you how to come up with the Average formula for the dynamic date range of (First month of the year - latest month listed in your table).

    First off here is the formula you can use:
    A3=AVERAGE(INDIRECT("r" & ROW(A3)&"c" & MATCH(DATE(YEAR(TODAY()),1,1),$2:$2,0) &":r"&ROW(A3)&"c" &MATCH(MAX($B$2:$Z$2),$B$2:$Z$2,0)+1,FALSE))

    Essentially what I am doing is using the INDIRECT formula to pull out the rows and columns we want to look at. Simplified it will read like this:
    Where X and Y are the row and column references for the start date and end date of your range. The trick here is to dynamically pull your rows and columns and insert them into this formula. Let me break down each part of the formula:

    1. The row reference: This is the easiest section of the formula because it's just looking at the row the formula lives in:

    2. The First month of the year column: Here we just want to find the date in your headers which references the first month of the current year. We can use a simple match formula for this (This segment of the formula can be updated to show the last month of the previous year if you wanted to do that):

    3. Last we want to find the most recent month in your headers. We will use another match formula, taking the greatest value of all your headers (Excluding column A). You can extend the bolded part out further if you plan to have more headers in the future):

    I hope this makes sense.

    If you delete the month of July in your attached spreadsheet then you receive these results:
    A3=AVERAGE(INDIRECT("r" & ROW(A3)&"c" & MATCH(DATE(YEAR(TODAY()),1,1),$2:$2,0) &":r"&ROW(A3)&"c" &MATCH(MAX($B$2:$Z$2),$B$2:$Z$2,0)+1,FALSE)) = 96.83
    A4=AVERAGE(INDIRECT("r" & ROW(A4)&"c" & MATCH(DATE(YEAR(TODAY()),1,1),$2:$2,0) &":r"&ROW(A4)&"c" &MATCH(MAX($B$2:$Z$2),$B$2:$Z$2,0)+1,FALSE)) = 94.83
    A5=AVERAGE(INDIRECT("r" & ROW(A5)&"c" & MATCH(DATE(YEAR(TODAY()),1,1),$2:$2,0) &":r"&ROW(A5)&"c" &MATCH(MAX($B$2:$Z$2),$B$2:$Z$2,0)+1,FALSE)) = 95.17

    Which lines up exactly with your formula for the Avg of June.


    Re: Lock row after pressing save

    Step one: Unlock all of your cells (The cells default to locked when a workbook is created) Format Cells->Protection->Uncheck Locked Box
    Step two: Place this code in the worksheet module of the sheet you would like to protect:

    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Cells(Target.Row, 1000) = "x"
    End Sub

    Step Three: Place this code in the workbook module:

    Step Four: Update these two lines of code in the code you pasted into the workbook module in step three:

    Set ws = Sheets("Sheet1") 'Specify sheet name here in the quotations
    pw = "Test" 'Specify password here in the quotations

    Step Five: Save your workbook.
    Step Six: Start editing records, when saved (and only when saved) will your rows then become locked if edited.

    *Note, this will also lock your header row. If you do not want it to lock your header row change this line of code:

    For i = 1 To lrow


    For i = 2 To lrow

    I hope this solution works for you!


    Re: Create if condition between two dates

    If you are only excluding Sundays then try this:


    A2 = In
    B2 = Out

    The Logic here is: (If In = Out -1 Then "OK", If not then(If the In-day is Saturday then (If In = Out -2 then "OK", if not then "")))


    Re: Find matching value on left

    I went to Seattle with my GF, it was a lot of fun!

    The custID=custID+0 is actually adjusting what data type excel assigns to the value of custID.

    Essentially if you customer's id is a number in your table, then excel is reading it as a data type 1 (numerical), but your custID variable in your code is automatically being assigned a data type of 2 (Text). When comparing these two values, they need to be the same type in order for lookup functions to work properly. So what I'm doing here is adding a 0, forcing excel to treat this variable as a numeric type, this would obviously error out on a alphanumeric string so that is why the 'is error resume next' was placed above it.

    This is the first time I've ever encountered this issue with VBA, usually I see this type of thing when you are using index/match formulas within excel. If you do ever get an error with a lookup formula, and it should be working properly, you can use the =Type() function to check the data types of your cells and make sure they are the same type. adding a 0 or multiplying by 1 will force excel to change a data type of a value to numeric, and using =Text(cell,"#") will change it to a text type:

    =Vlookup(A1+0,D1:Z25,13,False) - Changes the lookupvalue (A1) to type 1 (Numeric)
    =Vlookup(Text(A1,"#"),D1:Z25,13,False) - Changes the lookupvalue (A1) to type 2 (Text)

    Here's a pretty in depth explanation if you want to learn more.…t-ii-data-types-in-excel/


    Re: Find matching value on left

    Sorry for the delay in response, I was on vacation. Give this a shot:

    Re: Message Box and Selection Change

    I think I'm confused as to what you are trying to accomplish. First, you want to use this sub so it doesn't fire every time you select a new cell:

    Second, wouldn't it make more sense to put this validation at the start of your macro?

    Re: Help with Vlookup VBA

    I'm not sure what your lookup references are but you can use something like this, which will give you the value:

    Range("J2") = WorksheetFunction.VLookup(Range("A1"), Sheets("Worked Accounts").Range("B1:K10"), 10, False)

    If you want the formula then use this:

    Range("J2") = "=Vlookup(A1,'Worked Accounts'!B1:K10,10,False"

    If you give me the formula without the R1C1 references I can give you a better suited formula.

    Re: Paste the row with value >0 in output sheet

    Here you go:

    Make sure you update the sheet names and the start column and row, as noted in the code. Defaults are as follows:

    Input sheet: "Sheet1"
    Output sheet: "Sheet2"

    Start Row of Input data (Excluding headers): 2
    Start Column of Input data: 1 (A)

    Re: Find matching value on the left

    Try this:

    I commented out the msgbox that shows what the Ticket No. is. Remove the comment to see the msgbox.


    Re: Nested Loops Quit after one successful round

    I think this might be because you activated a new wb (FinanceData) at the end of your loop, so when it moves to the next item in the loop your code is focusing on (FinanceData) and not (UtilData).

    Try this:

    Re: match multiple criteria on 2 sheets to get TRUE

    NBVC, Your formula is reading: If the target date exists in sheet 2 and the target name exists in sheet two then return "True". This doesn't check to see if the targets exist in the same row in sheet two. Now you can add another portion of the formula to check to make sure they are in the same row:


    But the issue with this: If there are multiple instance of the same name or same date, then the match will always return the very first instance of the value. So a record could be a "True" match but the formula will read as false if there the same date/name is entered higher in the list.

    The only way (I can come up with) to solve this problem is by using an array formula that checks for the existence of each date/name in every row.

    That being said, I agree, it takes up a lot of memory usage and a macro would be more efficient.

    I hope this all makes sense.


    Re: match multiple criteria on 2 sheets to get TRUE

    The formula you used from me was not entered in as an array formula. Double click into the first cell and click control+shift+enter. Once you have done this the formula should look like this:

    Then drag it down and it will work. The formula has to be entered as an array formula though!

    Re: match multiple criteria on 2 sheets to get TRUE

    Sorry for the delay, I was out nursing a hangover and didn't come into work where I normally help on this forum.

    This is definitely doable. You need to use an array formula for it though. For your specific example you need to use this:


    *This is an array formula. When you are typing in the formula, once you are ready to enter it press control+shift+enter

    Here is the template you can use if your data set changes regularly:{=IF(SUM(IF(FirstNameInDataSet=Sheet2!Column:ColumnOfNameRange,IF(FirstDateInDataSet=Sheet2!Column:ColumnOfDateRange,1,0)))>0,"True","False")}

    Let me know if this works.


    Re: Finding the Nth incident of a text string using match

    Lets say your data set is in column A, your nth number is in cell F5, and your value (bill) is in cell F6.


    So in your particular data set, if you have F5= 2 and F6 = "Bill", your result will be 4

    *This is an array formula, make sure when you are done type the formula to press cntrl+shift+enter.


    Re: Trying to match multiple criteria on 2 sheets to get value of "true"

    Does row 1 on sheet 1 correspond with row 1 on sheet 2? If so you can use this:


    (I'm assuming your employee name is in sheet1 A and sheet2 H, per your formula.)

    If you provide your workbook with an example of what you want to see it would be easier to help solve your issue.


    Re: Determine last occurrence of item in a table

    You can do something easy like this to create a column that sums up the kg for that specific customer on that specific day:


    This works too if you want a formula:
    *place in I2, then drag down


    With either of these you can now look at that record to see that customers total Kgs for that given customer/day combination.The formula would be the fastest way to accomplish this without running a loop which could take a lot of time with 700k + rows.