• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Search results

  1. Busymanjohn

    Return Next Instance

    Hi, looking for some help on the following ,,,, I have 2 reports, which shows me data on when stock arrives and when it moves out ( dates ) and I currently use VLOOKUP to look at the part and location ( concatenated ) to show me when stock moves out and subsequently calculate how many days that...
  2. Busymanjohn

    Product flow in and out

    Hi guys, I have a query with some data from a colleague and I can't seem to find a solution. There is a sample file attached. Basically, there is a set of data that shows product being transferred into an area and a few days or so later that material being transferred back out ( positive qty...
  3. Busymanjohn

    Lookup last value using 2 criteria

    Hi Guys, I am having problems with a formula, I am trying to lookup a range of cells and return the last value in that range based on 2 criteria ,,, I can return the last value based on 1 criteria using LOOKUP(2,1/(B3:B16=F6),C3:C16), but how do I insert the 2nd criteria ,,,, also looked at...
  4. Busymanjohn

    Insert rows when data set is extended

    Hi All, I am looking for a solution to a small problem ( and I am sure I have come across the solution before but cannot find I ). I have a colleague who has a data set in sheet 1 with a pull through of the data set in sheet 2. The problem is that the pull through in sheet 2 is set to 5 rows of...
  5. Busymanjohn

    Occurrence to equal 1

    Hi all, I have a file that shows over 13,000 lines of data, the data is to be used to work out storage space, but that is irrelevant at this point. Some of the data shows that some part numbers have an occurrence of 4, 7, 10 etc etc .... what I want to do is split these rows out ,,,, so if a...
  6. Busymanjohn

    INDEX SMALL with >

    Hi all, I am having some trouble with getting the required results from a multiple VLOOKUP ( INDEX, SMALL ) when I input a > sign rather than an = sign ..... i.e. my formula is INDEX(page!$B$1:$U$2671,SMALL(IF(page!$B$1:$B$2671=3,ROW(page!$B$1:$B$2671)),ROW(page!1:1))*1,2) page is the sheet...
  7. Busymanjohn

    COUNT with multiple Criteria

    Little conundrum .... maybe i ahve been staring at this for too long ,,,, sample file attached .... I need the results in cells H2:H5 to count the number of Names that appear in Col C that match the date in Col B ( example date in H1 ) and the Code type in Col D ,,,, results should be as per...
  8. Busymanjohn

    Sizing Warehouse Requirements

    I have a poser for the guru's and ninja's ..... I am looking to create a model in Excel that will allow me to work out the capacity ( warehouse space ) need for a set of products. I have the dimensions of the products themselves and can work out if they will fit on a shelf or a pallet ( in a...
  9. Busymanjohn

    Excel Wedding Planner

    Hi All, I have written to Chandoo separately on this, but also wanted to check within the Forum Guru's ..... I purchased a copy of the excellent Excel Wedding Planner from Chandoo and Kevin back in 2011 ,,,, long story short, I cannot find it ( I think it's stored on an old corrupt laptop ) ...
  10. Busymanjohn

    Frequency function

    Hi all, I am using the following array formula =SUM(IF(FREQUENCY(IF($B$7:$B$500=B7,IF($I$7:$I$500=M$6,MATCH($D$7:$D$500,$D$7:$D$500,0))),ROW($B$7:$B$500)-ROW($C$7)+1)>0,$C$7:$C$500)) it works fine and returns the right info, but I would like to add one more criteria ,,, for data in Col H ...
  11. Busymanjohn

    Simple copy macro not working

    Hi, I have a simple copy / paste macro recorded ( as below ), but I keep getting a runtime 1004 error and when I look for an explanation into the error I see that this error is common with password protected files .... i've removed the password and i still get the error ,, i'm not very good with...
  12. Busymanjohn

    Match function not what I need

    Hi, I am looking for a solution to a small problem. I have a range of cells ( Q14:AE14 ) that contain values varying from 1 to 5, in cell AF14 I want to return the cell that has a value =>3 ....... e.g. if the value 4 appears in R14, the result in AF14 would be 2 .... make sense?
  13. Busymanjohn

    Customised Drop Down List

    Hi, I am looking to be able to have a drop down list that has the ability to allow the user to have more than one choice ,, i.e. a drop down list of say employee names but if someone wanted to just show those that begin with A for example it will show every employee name that begins with A. Is...
  14. Busymanjohn

    Cell linkage

    Hi guys, I have a worksheet names FSM and a second one named FSM Paste, in FSM I have cell A1 the text FSM Paste, in the same worksheet I want a formula in cell B1 which refers to A1 and returns the value from the FSM Paste sheet and say cell B2 in that sheet. In other terms, in FSM sheet, cell...
  15. Busymanjohn

    Manual Calc v's Auto Calc

    Hi guys, I think I know the answer to this, but thought it worth raising. If I receive an Excel file form another person and they have been using manual calc, when I open it it sets all other workboks I have open to manual calc as well, is there a way to stop this happening? Version 2003!
  16. Busymanjohn

    IF a cell contains largest value

    Hi guys, I have a table of data, 12 x 12 that contain numbers, what I want to do in column to the right of the data is have a formula which returns a 1, 2, 3 ( thru 12 )if a cell within the table contains the highest value, second highest value etc ... so if Col C had a 10 cells that are zero...
  17. Busymanjohn

    VBA Code - Message Box

    Hi guys, looking for some help, I have a file which contains some VBA code that shows a Message Box when a number does not appear in a list, I would like to have a Message box appear when the number does appear in the list ,, so basically have a two boxes, one to say, "Does not appear" and...
  18. Busymanjohn

    Date Format incorrect - Excel 2003

    Hi guys, I have tried various things to no avail and get get the date format ( some cells only ) in a range of data. The xls file is an extract from Brioand normally have no issues with re-formatting the dates form US format to UK format, but today some of the cells would not change, they stay...
  19. Busymanjohn

    Reset Drop Down List

    Hi guys, I have a couple of drop down lists ( Combo Boxes ) in a sheet in cells C16 and E16. I am looking for a code that will reset the drop down lists to <Select> which is the default choice in each list. The idea is to attach the code to Form Button for the users to click on. Any ideas?
  20. Busymanjohn

    If a range of cells has more than one value

    Hi, This should be easy, I have a range of data ,, say BO10:BO21, that contain results of an IF sumproduct, which returns a value of zero depending on the result, what I would like to do in another cell, say, BO6, is have a formula that returns a value of 1 if the range BO10:BO21 contains values...
  21. Busymanjohn

    Having some trouble

    Hi guys, This should be easy, but maybe I have been staring at the data too long and I am not seeing what I should be doing ... I have a large list of data, col A has country names, col B has codes, col C has order types, etc etc. In a separate sheet I would like to be able to input a country...
  22. Busymanjohn

    Return which cell has a number

    Hi guys, Poser for you, I have a range of data cells(BB5:BM5), so a total of 12 cells, only one of the cells in that range contains a number which is >=4, how do I return in another cell (BN5),which number from those cells contains a number >=4 ,,,, so e.g. in the range BB5:BM5, BK5...
  23. Busymanjohn


    Hey guys, this shouldn't be too difficult. I have two drop down lists, both are a list of order types, one for Manual (B9) and one for Remote (C9). I also have a country name in another cell (B6). I want to do a VLOOKUP based on the country ( B6 )and one of the order types ( B9 or C9 ). My...
  24. Busymanjohn

    Protecting Graphics in a cell

    Hi guys, I have been looking for a way to protect certain graphics within a worksheet, but failing. I have a sheet with cells populated with various data and I am protecting only certain cells within the sheet, however, some of those cells I am protecting also contain graphics or images and I...
  25. Busymanjohn

    Country Flags

    Here's a question that should generate some discussion. In Excel 2003, is there a way to have a country flag show in say cell B1 depending on the country name in say cell A1. So if A1 said Croatia, B1 would should the country flag.