Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. knifey

    median formula help needed

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

    Formula help! <> SEARCH result within SUMPRODUCT

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

    Formula help! <> SEARCH result within SUMPRODUCT

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

    VBA - How to speed up inserting and deleting sheets?

    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).
  5. knifey

    VBA - How to speed up inserting and deleting sheets?

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

    Excel INDEX MATCH MATCH on column title containing month and year?

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

    Excel INDEX MATCH MATCH on column title containing month and year?

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

    Excel INDEX MATCH MATCH on column title containing month and year?

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

    Excel INDEX MATCH MATCH on column title containing month and year?

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

    Excel INDEX MATCH MATCH on column title containing month and year?

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

    Excel INDEX MATCH MATCH on column title containing month and year?

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

    Excel YTD formula to display YTD value based on the month selected in a validation list??

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

    Excel YTD formula to display YTD value based on the month selected in a validation list??

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

    Excel YTD formula to display YTD value based on the month selected in a validation list??

    Bump, anyone?
  15. knifey

    Excel YTD formula to display YTD value based on the month selected in a validation list??

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

    Excel YTD formula to display YTD value based on the month selected in a validation list??

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

    Excel dynamic validation lists dependant on criteria given in another validation liust - please help

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

    Excel dynamic validation lists dependant on criteria given in another validation liust - please help

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

    Excel dynamic validation lists dependant on criteria given in another validation liust - please help

    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)
  20. knifey

    Excel dynamic validation lists dependant on criteria given in another validation liust - please help

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

Part and Inventory Search

Back
Top