Search results for query: *

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

Hi, I'm trying to create a formula that calculates the YTD value for April (start of our fiscal year) to the month selected in a validation list. I have multiple columns per month as shown below: Row 6 March March March March March March Row 7 Volume Volume ASP ASP Value Value Row 8...

Hi Skip, I think you may have found the error. The range Molecule_Name refers to a named range that in my first attempt at this. I have then defined the Excel table on top of the named range. I'll be able to test and confirm tomorrow. That said I will still rewrite using VBA:) Thanks, K

Hi Skip, That's what I was just thinking, this would be simple enough in VBA but has turned into a bit of a monster using formula (and I'm not too good at advanced formulas anyway). When using userform comboboxes in VBA I use a looping .ADDITEM method (i.e. add unique items from a range). Do...

Hi Skip, I forgot to say, the dynamic method I'm using is just Excel tables (i.e. a user adds to the bottom of the table j0 it's reflected in the VL straight away)

Hi Skip, I'll try to be as thorough as possible. 1.) My list of drug names is setup in column R3:R254 of an Excel table in the worksheet named 'lists'. 2.) My list of drug strengths is setup in column S3:S254 of an Excel table in the worksheet named 'lists'. 3.) In column U3:U254 of an Excel...