Recent content by knifey

Hi, I have an averageif formula that I'm trying to adapt (unsuccessfully) to give me the median instead. Can anyone advise where I'm going wrong or a better way of doing this? AverageIf works: =AVERAGEIF(OFFSET(Source!$A$1,1,MATCH("Incident...

Hi Skip, Fantastic that was just what I was looking for. =SUMPRODUCT(1*((MONTH('Stats Tracker'!$H$2:$H2000)=MONTH(I15))*(YEAR('Stats Tracker'!$H$2:$H2000)=YEAR(I15)))-((MONTH('Stats Tracker'!$H$2:$H2000)=MONTH(I15))*(YEAR('Stats Tracker'!$H$2:$H2000)=YEAR(I15)))*(ISNUMBER(SEARCH("*Late...

Hi, I have a sumproduct formula that matches a reporting month and year in the format mmm-yy (cell I15) to a column of dates (colH). It then counts all rows where column N contains 'Late Start' as a substring. =SUMPRODUCT(1* (MONTH('Stats Tracker'!$H$2:$H2000)=MONTH(I15)) * (YEAR('Stats...

Hi Skip, I've gone for the obvious solution of removing the temp sheet by sorting by date first, then filtering! Cheers, K P.S. Nice catch strongm [hammer] (the loop skips many rows that are not needed).

Hi, I have a long complex macro that is acting very strange and was after some advice. This macro does: 1.) Loops through 3000 or so rows (which will grow). 2.) On each row it needs to filter for a code in another sheet, copy filtered list, paste to a new sheet, sort by earliest date first...

Hi Skip, Unfortunately no. The 17 sources will each be sending in a single sheet workbook per week. Each workbook will be named with the w/e date that the sales figures cover. This isn't a problem as I can use some VBA in conjunction with a month/year validation list that the user selects when...

Hi Skip, Thanks for clarifying. I will be using match type 1, although it makes no difference in this situation as I don't have a range of dates to deal with (e.g. 1/4/14-30/4/14). All I have is a total count for each month. The actuals data will be imported on a weekly basis using VBA from 17...

Hi Skip, Match type 1 is giving exactly the same results as 0. What is the difference? Surely I would want an exact match to month and year? Thanks, K

Thanks Skip, Working perfectly, you've saved my bacon again. I've made all the changes, added named ranges and the formula now looks like this: =INDEX(ActualsDataRange,MATCH(Master!$H2,ActualsMAMapName,0),MATCH(Master!AF$1,ActualsMonths,0)) Thanks again, K

Hi Skip, I've tried changing the match type to 1 and this is now returning a result. Unfortunately it's the wrong result! I have attached the file so you can get a better idea of what I'm trying to do. You can see that column BO of the master sheet (Mar-15) is matching to column AL of the...

Hi, I have a master worksheet matrix consisting of lookup values in column H and months in the format 'Apr-13', 'May-13', etc. in cells AF1:BE1 What I need to do is pull the 'actuals' sales figures in to the master worksheet. The actuals worksheet is in exactly the same format as the master...

Hi Skip, Thanks for this amazing piece of code, it works great! I can't believe I didn't see the extra eee. Cheers, K

Hi Skip, I've tried the solution from your last post but I can't get it to work. It's coming up with a #NAME? error in MONTH(SelecteedMO). Strange, but the YEAR(SelecteedMO) part works fine. Please can you take a quick look if you have time. I know I'm almost there...

Bump, anyone?

Hi Skip, I have attached a sample of my workbook. My formulas attempts so far are in column CI and my validation list is in A1. Row 10 onwards contains the various figures that I need ytd values for based on cell A1. Here is a link to a working version of what I am trying to do (found on...