Posts by widgetwonka

    Re: library book location formula does the Dewey Decimal system work?

    A place to start:

    1. determine what letter code the book is in (either 1 or 2 left characters)
    2. determine the number values: use the SEARCH() function on "-" to determine the left and right side range and MID() and RIGHT()
    3. Use two lookups, one for the letter, then a dependent one based on the number start and end

    Is this a school assignment? If not, I can be less cryptic ;)

    Re: Sort ascending order

    First, Autofilter the Ranking tab A2:C1000 range (range only has to be as long as the data - so if it never changes, make it 32. If it is dynamic, you could re-create the autofilter everytime...)

    Then paste this in the Sheet1 section of the VBA project (press alt+F11 to access the VBA editor, project structure will be on the left):

    This macro will run everytime Sheet1 changes.

    Re: Time calculation is going wrong

    My guess is because you have macros in there, and no one wants to open it for fear of a virus. Do you need the macros to make the sheet work? If not, strip them out and re-send. I'll look at it then.

    Re: And, if, then statements in excel 2007

    Formula for cell I11:

    Formula for I11:I2000 (whatever the last row of data is):

    I used the beat column as the reference column. You could also use the INCIDENT column. That may be a better option if all of your incidents follow the same pattern. Then you could use =IF(ISNUMBER(A12),A12,I11).

    After you use the formula, copy column I and the paste --> special --> values. This will remove the formulas. The final piece of the puzzle is removing the date lines. To do so, auto filter the new data table. Then, filter to non-blanks in the Beat column. Copy the filtered range to a new sheet. The new table should have everything you wanted in your sample New data table.


    Puuurrreeee Imagination

    Re: Column Chart

    Can you clarify what you mean by overlap? Do you want all the figures on the same bar? That would be a stacked bar chart, and is available in chart type.

    Re: Time Difference in Excel using formulas


    Assuming that the log date is in Cell A1, and the close date is in Cell B1, this formula will give you the hours and minutes:


    Use this custom format to view it as hours:Minutes


    I could have simplified the formula by taking out the redundant terms, but this way is more instructive and easier to de-construct intuitively.

    You can also modify NETWORKDAYS(start date, end date, [holidays]) to exclude and days where the call center is closed. Used a named range to specify all holidays, then put the named range into the formula where it says [holidays].

    Hope this helps.

    Puuuuurrrreeee Imagination

    Re: Create new list in ascending order based on values in source list

    It is because MATCH is finding the first instance of 0. You can't use match to do this like you want to. Why not pivot the data and use the field sort to do this? Or, just use links and then sort the links in the order you like? You can use a worksheet change private sub to have it resort ever time you update the values.

    Re: Match index formula

    This is a tough problem.

    Your best bet is to use a database program like Access to do it. Your second best bet is to use VBA to do it. Your last option is a formula.

    Based on your request, I created a pivot table of the data. I did this to filter by Name. The I wrote a combination OFFSET, INDEX, MATCH query to find the next item in the list.

    I found that some of your data has spaces where there was no x. These should be eliminated by a find/replace for this solution to work.

    The attached workbook has my solution. Unfortunately, you will have to create a report for each person. Also, since there are 52 weeks, you will have to change the location of the output. I'll leave that to you if you should choose to pursue the formula route.


    Re: Transferring dates using "IF" function?

    There is a way to do that. The easiest way is with a VBA loop. But, to keep things simple, I would just sort by Date descending. That will push all the blanks to the bottom of the sheet. The formulas will still be there, but you can delete them if you want.

    Re: SUMPRODUCT blank cell exclusion.

    Try this array formula:


    If you are not familiar with arrays:
    enter the formula without the brackets on the end. Before exiting the cell, press ctrl + shift +enter. That will tell Excel that the formula you just entered is an array.

    I tested it, and it works with blank values.

    Re: Lookup all values

    Pivot the data. Add name and number as rows. The numbers will be in row fashion, not column, but it is a quick solution.
    If there is another idenitifier, like TYPE: Home, Cell, Work, make that a column, and you will have a unique matrix.

    Re: Transferring dates using "IF" function?

    Use the =MONTH() function to determine the date and route accordingly.

    On your spreadsheet this is accomplished by the following formula for January:


    That is going to leave a lot of blank lines though. It is beyond the scope to ask why you need a spreadsheet for every month, but you might want to explore a better data structure for you project.

    Re: Strange business:Macro works one moment, not the next, on apparently identical ta

    Have you stripped some code out of this? Where are the variables declared? Are cNum and cRow defined to be a range not in the current worksheet?

    Re: Create Named Ranges Using Loop

    Thanks AAE. I figured it out. It had nothing to do with the code. It had everything to do with me calling the wrong macro...

    Maybe someone else will find this code useful though...