WordPress.com



LOST HOLDSTo look for current holds:=SEARCH("(0 days",H2)To look for holds other than frozen ones (lost holds report); must then check dates manually=(COUNTIF(H2,"*days)*")-COUNTIF(H2,"*(255 days)*"))=(COUNTIF(H2:ZZ2,"*days)*")-COUNTIF(H2:ZZ2,"*(255 days)*")) <this doesn’t work any differently>Check for more than one hold on a line:=SEARCH(";",H2)To look for matches – multiple holds on multiple items: (Sort first by Title and Hold)=AND(EXACT(D2,D1),EXACT(H2,H1))IN TRANSITConvert barcode# to 8 digits:=CONCATENATE(MID(F2,7,4)," ",MID(F2,11,4))Shrink MESSAGE field (after checking for multiples):=MID(H2,5,99)Combine Call# and Vol#:=CONCATENATE(B2," ",C2)Transit message indicates this is for a Hold=EXACT(" ",MID(E2,LEN(E2)-1,1))NOVELIST SELECT w/3M QuickConnect[Put in Column E]=MID(C2,3,9999)=AND(EXACT(B2,B1),EXACT(LEFT(A2,9),MID(A1,4,9)))=AND(EXACT(B2,B3),EXACT(LEFT(A2,9),MID(A3,4,9)))NOVELIST SELECT (OLD)[Insert as Column A]Select only Material Types that Novelist Select wants:=OR(EXACT(B2,"a"),EXACT(B2,"b"),EXACT(B2,"d"),EXACT(B2,"f"),EXACT(B2,"i"),EXACT(B2,"l"),EXACT(B2,"o"),EXACT(B2,"q"))Grab everything before the first space:=LEFT(B2,FIND(" ",B2)-1)ROUTING REPORT (for Serials)Bib title plus checkin record#:=CONCATENATE(LEFT(B2,30)," - ",C2,";")Propagate Title, selectively:=IF(CELL("contents",C3)="",CELL("contents",B3),CELL("contents",D2))=IF(LEFT(B3,1)=" ",CELL("contents",C2),CELL("contents",B3))Blank successive/repeated fields (e.g. Name or Title):=IF(EXACT(B2,B1),"",CELL("contents",B2))IPLARItem Location Code indicates “magazine” (..p..):=EXACT("p",MID(I2,3,1))Search the “VLOOKUP2” sheet (cells A2:B478) for the Bib Rec# (in Col. C); return the 2nd field:VLOOKUP(A2,$C$2:$C$100,1,FALSE) VLOOKUP(C2,VLOOKUP2!$A$2:$B$478,2,FALSE)LIBRARY JOURNALInsert as Column E: =AND(OR(EXACT(LEFT(C2,6),"FICTIO"),EXACT(LEFT(C2,6),"NONFIC"),EXACT(LEFT(C2,6),"LARGE ")),D2>3)Insert as Column B: =AND(OR(EXACT(LEFT(E2,6),"FICTIO"),EXACT(LEFT(E2,6),"NONFIC"),EXACT(LEFT(E2,6),"LARGE ")),F2>3)GENERALCount the number of characters in a cell:=LEN(B2)Count the number of non-blank cells: =COUNTA(h2:zz2)To count number of cells with a value in them:=COUNTIF(C2:ZZ2,"*days)*")Convert an Excel date to a text value (esp. for pivot table summarizing by Year/Month)=TEXT(B2,"yy-mmm") yields 12-May=TEXT(B2,"yyyy-mm") yields 2012-05Percentages – Use $c$r to keep cell#s static when copying percentage formulas (from JMM):=(D3/$D$6)GENERAL (cont’d)If C2 is empty, copy B2 into Cell D2, else copy the one above it.=IF(CELL("contents",C2)="",CELL("contents",B2),CELL("contents",D1))Check “Placed Dt” of a hold: For a specific date (e.g. April 26,2011): =EXACT("P=04-26-11",MID(I2,25,10))For a month/year (e.g. Dec , 2010): =AND(EXACT("P=12",MID(I2,25,4)),(EXACT("-10",MID(I2,32,3))))Check NNB date (MM/YY):=AND(EXACT("NNB=06-",MID(H2,37,7)),(EXACT("-11",MID(H2,46,3))))Check the case of the second letter in patron names:=CODE(MID(B2,2,1))Count the number of “good” status values attached to a bib:=COUNTA(D2:ZZ2)-COUNTIF(D2:ZZ2,"n")-COUNTIF(D2:ZZ2,"z")-COUNTIF(D2:ZZ2,"$")If the last character in cell C2 is a blank space, print “SPACE” otherwise print “OK”:=IF(EXACT(" ",RIGHT(C2,1)),"SPACE","OK")If the last character in cell C2 is a blank space, trim it by 1 character:=IF(EXACT(" ",RIGHT(C2,1)),LEFT(C2,SUM(LEN(C2)-1)),C2) ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download