Posts by widgetwonka

Re: library book location formula

hmm...how 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: Creating 15 Min Interval Data - Excel 2003

Cell B2:

=MATCH(TIME(VALUE(MID(A2,FIND("(",A2)+1,2)),VALUE(MID(A2,FIND(":",A2)+1,2)),0),$C$2:$C$36,1)

Cell D2:

=COUNTIF($B$2:$B$54,ROW()-1)

Adjust the COUNTIF range as needed. Drag the formulas down.

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):

Private Sub Worksheet_Change(ByVal Target As Range)


    ActiveWorkbook.Worksheets("Ranking").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Ranking").AutoFilter.Sort.SortFields.Add Key:= _
        Range("C2:C300"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Ranking").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


End Sub

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:
=A11

Formula for I11:I2000 (whatever the last row of data is):
=IF(LEN(H12)>0,I11,A12)

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.

Cheers

WidgetWonka
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

Carrie,

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:

=(17-HOUR(A14)-1-(17-HOUR(B14)-1)+((NETWORKDAYS(A14,B14)-1)*8)+(60-MINUTE(A14))/60-((60-MINUTE(B14))/60))/24

Use this custom format to view it as hours:Minutes

[h]:mm:ss

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.

WidgetWonka
Puuuuurrrreeee Imagination

Re: Look up using special characters and return matching value

You can preceed wildcard characters with a ~ in the find tool to tell Excel to search for the actual character.

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.

Cheers.

forum.ozgrid.com/index.php?attachment/38445/

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: Change every group of rows to columns

How are the addresses and phones identified as such?

Re: Transferring dates using "IF" function?

Just have it exclude zeros:

=IF(MASTER!B4=0,"",IF(MONTH(MASTER!B4)=1,MASTER!B4,""))

Re: add a space, two spaces after the comma in a string of text

This only works for the first comma. Multiple instances can be handled, but requires a more complex formula:

=LEFT(D21,FIND(",",D21))&" "&MID(D21,FIND(",",D21)+2,1)&" "&RIGHT(D21,LEN(D21)-FIND(", ",D21)-2)

The string doe, jk001254445 is cell D21 in this example.

Re: Insert sum formula into same cell on all sheets

If your sheets are truly named SHEET_2, SHEET_3, etc, you can use this formula. Just place it in the row that corresponds to the sheet number.

=SUM(INDIRECT("SHEET_"&ROW()&"!D13:D150"))

Re: SUMPRODUCT blank cell exclusion.

Try this array formula:

{=SQRT(SUM((AV5:CD5*AV15:CD15-$V$7)^2))}

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:

=IF(MONTH(MASTER!B4)=1,MASTER!B4,"")

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