Chapter4:SpreadsheetsinCalc
AddressingCells
Cellscanbeaddresseddirectlybymeansoftheircoordinatesintherow­and­columnmatrix.
Useabsoluteorrelativereferencing,orgivecellrangestheirownnames.
AddressesandReferences,AbsoluteandRelative
RelativeAddressing
ThecellincolumnA,row1isaddressedasA1.Addressarangeofadjacentcellsbyfirst
enteringthecoordinatesoftheupperleftcellofthearea,thenacolonfollowedbythe
coordinatesofthelowerrightcell.Forexample,thesquareformedbythefirstfourcellsin
theupperleftcornerisaddressedasA1:B2.Byaddressinganareainthisway,oneis
makingarelativereferencetoA1:B2.Relativeheremeansthatthereferencetothisareawill
beadjustedautomaticallywhentheformulaearecopied.
AbsoluteAddressing
Absolutereferencesaretheoppositeofrelativeaddressing.Adollarsignisplacedbefore
eachletterandnumberinanabsolutereference,e.g.,$A$1:$B$2.
Calccanconvertthecurrentreference,inwhichthecursorispositionedintheinputline,
fromrelativetoabsoluteandviceversabypressingShift+F4.Whenstartingwitha
relativeaddresssuchasA1,thefirsttimethiskeycombinationispressed,bothrowand
columnaresettoabsolutereferences($A$1).Thesecondtime,onlytherow(A$1),andthe
thirdtime,onlythecolumn($A1).Pressingthekeycombinationoncemore,bothcolumn
androwreferencesareswitchedbacktorelative(A1)
Calcshowsthereferencestoaformula.If,forexampletheformula
=SUM(A1:C5;D15:D 24)isclickedinacell,thetworeferencedareasinthesheetwillbe
highlightedincolour.Forexample,theformulacomponentA1:C5maybeinblueandthe
cellrangeinquestionborderedinthesameshadeofblue.Thenextformulacomponent
D15:D24canbemarkedinredinthesameway.
WhentoUseRelativeandAbsoluteReferences
Firstexaminewhathappenswithoutarelativereference.AssumeacalculationincellE1that
sumsthecellsinrangeA1:B2.TheformulatoenterintoE1wouldbe:=SUM(A1:B2).If,
later,anewcolumnmustbeinfrontofcolumnA,theelementstobeaddedwouldthenbein
B1:C2andtheformulawouldbeinF1,notinE1.Afterinsertingthenewcolumn,all
formulaewouldthereforehavetobecheckedandcorrectedinthecurrentsheet,andpossibly
inothersheets.
Fortunately,Calcdoesthisworkautomatically.AfterinsertinganewcolumnA,theformula
=SUM(A1:B2)isupdatedto=SUM(B1:C2).Rownumbersarealsoautomaticallyadjusted
whenanewrow1isinserted.AbsoluteandrelativereferencesarealwaysadjustedinCalc
wheneverthereferencedareaismoved.Butbecarefulwhencopyingaformulasinceinthat
caseonlytherelativereferenceswillbeadjustedbutnotanyabsolutereferences.
OpenOffice.orgUserGuidefor2.x
227
Pdf hyperlinks - insert, remove PDF links in C#.net, ASP.NET, MVC, Ajax, WinForms, WPF
Free C# example code is offered for users to edit PDF document hyperlink (url), like inserting and deleting
adding links to pdf document; adding hyperlinks to pdf documents
Pdf hyperlinks - VB.NET PDF url edit library: insert, remove PDF links in vb.net, ASP.NET, MVC, Ajax, WinForms, WPF
Help to Insert a Hyperlink to Specified PDF Document Page
add links to pdf in acrobat; add url to pdf
Chapter4:SpreadsheetsinCalc
Absolutereferencesareusedwhenacalculationreferstoonespecificcellinthesheet.For
example,ifaformulathatreferstoexactlythiscelliscopiedrelativelytoacellbelowthe
originalcell,thereferencewillalsobemoveddownifthecellcoordinatesarenotdefinedas
absolute.Asidefromwhennewrowsandcolumnsareinserted,referencescanalsochange
whenanexistingformulareferringtoparticularcellsiscopiedtoanotherareaofthesheet.
Assume,forexample,theformula=SUM(A1:A9)isenteredinrow10butthecalculated
summustbeintheadjacentcolumntotheright.Inthiscase,simplycopythisformulatothe
celltotheright.ThecopyoftheformulaincolumnBwillbeautomaticallyadjustedto
=SUM(B1:B9).
Therearemanymethodstocopyaformula,oneofwhichis:
1. Selectthecellcontainingtheformula.
2. ChooseEdit>Copy,orpressCtrl+Ctocopyit.
3. Selectthecellintowhichtheformulaistobecopied.
4. ChooseEdit>Paste,orpressCtrl+V.Theformulawillbepositionedinthenewcell,
andallnon­absolutereferenceswillbeupdated.
Copyingaformulaintomultiplecellscanbequickandeasy.Onewaytocopyintoadjacent
cellareasisasfollows:
1. Selectthecellcontainingtheformula.
2. Positionthemouseonthebottomrightofthehighlightedborderofthecell,andcontinue
holdingdownthemousebuttonuntilthepointerchangestoacross­hairsymbol.
3. Withthemousebuttonpressed,dragitdownortotherightoverallthecellsintowhich
theformulawillbecopied.
4. Uponreleasingthemousebutton,theformulawillbecopiedintothecellsand
automaticallyadjusted.
Wherevaluesandtextsmustnotbeautomaticallyadjusted,thenholddowntheCtrlkey
whendragging.formulae,however,arealwaysadjustedaccordinglywhendragging.
Nowlookatwhathappenedwhenrowsandcolumnsareadded.Assumeacalculationincell
E1thatsumsthecellsinrangeA1:B2.TheformulatoenterintoE1wouldbe:
=SUM(A1:B2).If,later,anewcolumnmustbeinfrontofcolumnA,theelementstobe
addedwouldthenbeinB1:C2andtheformulawouldbeinF1,notinE1.Afterinserting
thenewcolumn,allformulaewouldthereforehavetobecheckedandcorrectedinthecurrent
sheet,andpossiblyinothersheets.
Fortunately,Calcdoesthisworkautomatically.AfterinsertinganewcolumnA,theformula
=SUM(A1:B2)isupdatedto=SUM(B1:C2).Rownumbersarealsoautomaticallyadjusted
whenanewrowisinserted.AbsoluteandrelativereferencesarealwaysadjustedinCalc.
OpenOffice.orgUserGuidefor2.x
228
C# PDF Convert to HTML SDK: Convert PDF to html files in C#.net
Embed zoom setting (fit page, fit width). Turn PDF form data to HTML form. Export PDF images to HTML images. Embed PDF hyperlinks to HTML links.
add links to pdf document; add links pdf document
VB.NET PDF Convert to HTML SDK: Convert PDF to html files in vb.
Turn PDF images to HTML images in VB.NET. Embed PDF hyperlinks to HTML links in VB.NET. Available zoom setting (fit page, fit width).
add links in pdf; add a link to a pdf
Chapter4:SpreadsheetsinCalc
AddressingbyName
Aneatwayofmakingthereferencestocellsandcellrangesinformulaelegibleistogivethe
rangesnames.Forexample,nametherangeA1:B2asStartthenintheformulasimplyenter
=SUM(Start).FollowingtheinsertionordeletionofrowsandcolumnsOpenOffice.org
alsocorrectlyassignstherangesidentifiedbyname.Remember,rangenamescannotcontain
anyspaces.
Forexample,itismucheasiertoreadaformulaforsalestaxifwrittenas=Amount*
Tax_rateinsteadof=A5*B2.Inthiscase,cellA5wouldbenamedasAmountand
cellB2asTax_rate.
Inordertospecifyrangenames,followsthesesteps:
1. Firsthighlighttheareatobenamed.
2. SelectInsert>Names>Define,orusetheCtrl+F3keycombination.
3. Enterthenameforthefirstarea.
4. ClickAdd.
Othercellrangescanalsobenamedinthisdialoguebyenteringthedesirednameinthe
fieldandthenselectingtherespectivecells.UsetheDefineNamesdialoguetodefine
namesforformulaeorpartsofformulaeneededmoreoften.
5. ClosethedialoguewithOK.
RecognizingNamesasAddressing
OpenOffice.orgcancorrelatenameswithareasforthemostpartautomatically,aslongasthe
rowandcolumncoordinatesareentered.Refertothefollowinggraphic.
OpenOffice.orgUserGuidefor2.x
229
VB.NET PDF Page Replace Library: replace PDF pages in C#.net, ASP.
DotNetNuke), SharePoint. Able to replace all PDF page contents in VB.NET, including text, image, hyperlinks, etc. Professional VB.NET
add links to pdf document; check links in pdf
.NET PDF SDK | Read & Processing PDF files
of text, hyperlinks, bookmarks and metadata; Advanced document cleanup and image processing options provided; Annotate and redact in PDF documents; Fully support
pdf link open in new window; pdf edit hyperlink
Chapter4:SpreadsheetsinCalc
Theautomaticformularecognitionallowsentering,forexample,theformula=SUM(Ju ly)
incellB8.Thisformulacanthenbecopiedtotheright.CellC8automaticallyshowsthe
formula=SUM(August),cellD8=SUM(September),andsoon.Thisfeaturealsoworks
forhorizontalcalculations.Tryitoutbyenteringtheformula=SUM(London)incellE4.
TheNewYorkentrymustbetreatedasaspecialcasebecausethisnamecontainsaspace.
Thus,itmustbeenclosedinsinglequotationmarks:=SUM('NewYork').
Automaticrecognitiondoesnotworkwithformulae.Onecannotentertheformula
=SUM(sum)incellE8.Instead,enter=SUM(B8:D8)or=SUM(E3:E6).Thelatter
formulawillalsobeinsertedautomaticallyifthecursorisplacedincellE8andtheSumicon
intheFormulabarisclicked.
Thisfunctionisactivebydefault.Toturnitoff,selectTools­Options...>Spreadsheet
Document>CalculateandunchecktheboxinfrontofAutomaticallyfindcolumnand
rowlabels.
UseInsert>Names>Labels....todefineanynamesrequiredasshownintheprevious
section.
Note:Tohavenamesautomaticallyrecognized,namesmustconsistofalphanumericcharactersandthefirst
charactermustbealetter.Tousenonalphanumericcharacters(includingspaces),enclosethenamesinsingle
quotationmarks
(')
.Whereasinglequotationmarkappearsinaname,e.g.,Jan'97,enterabackslashinfrontof
thequotationmark(Jan\'97).ThistypeofreferencingisnotcompatiblewithearlierversionsofOpenOffice.org.
Calcautomaticallyrecognizesanarea,calledarange,ofcellsthatbelongtogether.To
determinewhichcellsmakeupaparticularrange,placethecellcursorinthedesiredrange
andpressCtrl+*butremembertouseonlythe*fromthenumericalkeypadas
Ctrl+Shift+8(theasteriskoverthe8)doesnotwork.Therangewillbehighlighted.
OnlyCopyVisibleCells
Assumetheuserhashiddenareafewrowsorcolumnsinacellrangeandnowonlythe
visiblecellsaretobecopied.
Calcbehavesdifferentlydependingonthemethodusedtohidetheinvisiblecellsandwhatis
theintendedactiontobeperformeduponthem.
Method
Result
CellswerefilteredoutbywayofAutoFilters,default
filtersorspecialfilters.
Onlythevisiblecellsarecopied.
OpenOffice.orgUserGuidefor2.x
230
VB.NET PDF Thumbnail Create SDK: Draw thumbnail images for PDF in
application. Generating thumbnail for PDF document is an easy work and gives quick access to PDF page and file, or even hyperlinks. This
add links in pdf; add url pdf
PDF Image Viewer| What is PDF
advanced capabilities, such as text extraction, hyperlinks, bookmarks and metadata as well as updating, splitting and merging pages from existing PDF documents
pdf links; convert excel to pdf with hyperlinks
Chapter4:SpreadsheetsinCalc
Method
Result
Copythevisiblecells,forexample,withcopyand
pasteviatheclipboard,withthemiddlemousebutton
ordraganddropwhilepressingtheCtrlkey.
CellswerefilteredoutbywayofAutoFilters,default
filtersorspecialfilters.
Movethevisiblecells,forexample,bywayofcut
andpasteviatheclipboardordraganddropwithout
pressinganadditionalkey.
Allcells,includingthehiddenones,are
moved.
CellswerehiddenmanuallybywayoftheHide
commandinthecontextmenuoftheroworcolumn
headers,orthroughanoutline.
Copyormovethevisiblecells.
Allcells,includingthehiddenones,are
copiedormoved.
ReferencingaCellinAnotherDocument
Cellscanalsobereferencedinotherspreadsheets:
1. Forthefollowingexample,thesourcedocumentisnamed'C:\test\test.sxc';and
itcontainsasheetnamedSheet1.
2. Openadifferentornewspreadsheetdocument.
3. PlacethecursorincellA1andenterthefollowingformula:
='C:\test\test.sxc'#Sheet 1.A1
4. Alternatively,enterthefilenameasaURLdesignation:
='file:///C/test/test.sxc'#Sheet 1.A1.Similarlyaccessafileonthe
Internetbyusing:='http://ww w.sun.com/test/test.sxc'#Sheet 1.A1.
5. Dragtheboxinthelowerrightcorneroftheactivecelltoselectarangeofcells.
OpenOffice.orgautomaticallyinsertsthecorrespondingreferencesintheadjacentcells.
Asaresult,thesheetnameisprecededwitha"$"signtodesignateitasanabsolute
reference.
OpenOffice.orgUserGuidefor2.x
231
Chapter4:SpreadsheetsinCalc
UsingReferencesinformulae
Sofarusershavebeenintroducedtoformulaecontainingabsolutevalues,absoluteand
relativereferencesandreferencestonamedranges.Occasionallywehavealsomentionedthe
Wizard:Functions.Hereisanexampleofreferencesusedinformulae,withthehelpofthe
Wizard:Functions.
Considertheexamplefromtheprevioustopic.Now,usethefunctionsWizardtocalculatethe
averageofmonthlysalesforeachlocation.
1. PositionthecursorincellB8,wheretheaveragewillbedisplayed.ActivatetheWizard:
Functionsbyclickingitsiconontheformulabar.
2. SelecttheAVERAGEfunction.Wherethecategorythatcontainsthisfunctionisunknown,
simplyleavethedefaultsettoallcategoriesandrepeatedlypresstheletterAuntilthe
AVERAGEfunctionisselected.
3. ClickNextintheWizard:Functions.OntherighthalfoftheWizard,anotherinputarea
willappearwhereonecanentertheargumentsofthefunction.
4. Clickintheinputfieldintowhichvaluesaretobeentered,inthiscasestartingwithfield
number1.EntereitherB3:B6orJanuaryorusethemousetoselectthatrangeinthe
sheet.Todoso,simplyclickonthefirstcell,B3,holddownthemousebuttonanddragto
B6.
OpenOffice.orgUserGuidefor2.x
232
Chapter4:SpreadsheetsinCalc
Whiledraggingthemousetohighlightthedesiredrange,theWizarddialoguewillbe
automaticallyminimizedsothatonlytheactiveinputfieldisvisible.Thefieldindicates
theareabeingselectedwiththemouse.Thedialoguecanbereducedorenlargedmanually
byclickingtheShrink/Maximizebuttontotherightoftheinputfield.
5. Finally,clickOKtoclosetheWizard.
Theformulaiscalculatedaccordingtothegivenarguments,andthefinalresultappearsin
cellB8.
ReferencestoOtherSheets
Inanysheetcell,userscanshowareferencetoacellinanothersheet.
1. Openanew,emptyspreadsheet.
2. Forexample,enterincellA1ofSheet1thefollowingformulaandcompletetheinput
withtheEnterkey:
=Sheet2.A1
3. NowclickthesheettablabelledSheet2onthebottomwindowbordertogotoSheet2
ofthecurrentdocument.SetthecursorincellA1thereandentertextoranumber.
4. UponswitchingbacktoSheet1,seethesamecontentincellA1aswasenteredon
Sheet2.IfthecontentsofSheet2.A1change,thenthecontentsofSheet1.A1also
change.
Inacorrespondingwayareferencecanalsobemadetoacellofanotherdocument.Open
anotherspreadsheetinadditiontothenewspreadsheet,whichisalreadysavedasafile(this
doesnotworkunlessadocumenthaspreviouslybeensaved).
1. Ifthesamplesfromhttp://
www.pathtech.org
/
OpenOffice.org/templates
/
areinstalled,
chooseFile>OpenandopentheMovie1documentfromthespreadsheetsamples.
2. Changebacktothenew,stillalmostemptyspreadsheet.Setthecursorinafreecelland
enteranequalssigntobeginaformula.
3. NowswitchtothedocumentthathasMovie1loaded.ClickcellC3.
4. Switchbacktothenewspreadsheet.InthecalculationrownoticethatCalchasaddedthe
referencetotheformula.Bywayofexample,thefollowingwillnowbelocatedthere:
In*
NIX
:
='file:////OpenOffice.org/share/samples/english/spreadsheets/Movie1.sdc'#$movievisits.C3
InWindows®:
='file:///C:/
OpenOffice.org/share/samples/english/spreadsheets/Movie1.sdc'#$movievisits.C3
5. Confirmtheformulabyclickingthegreencheckmark.
Thereferencetoacellofanotherdocumentthuscontainsthenameoftheotherdocumentin
singleinvertedcommas,thenahash#,thenthenameofthesheetintheotherdocument
followedbyapointandthenameofthecellthereC3.
OpenOffice.orgUserGuidefor2.x
233
Chapter4:SpreadsheetsinCalc
Thenameofthesheetautomaticallyreceivesaleadingdollarsign,sincethesheethas
absoluteaddressing.
Examinethenameoftheotherdocumentinthisformulaandnoticethatitiswrittenasa
URL.ThismeansthataURLfromtheInternetcanbeenteredtoo.Forexample,supposing
thereisanInternetpagecontainingcurrentstockexchangeinformationinspreadsheetcells,
justfollowthesestepstoloadthispageinCalc:
1. InaCalcdocument,positionthecursorinthecellinwhichtostartinsertingtheexternal
data.
2. SelectInsert>ExternalData.TheExternalDatadialogueappears.
3. Inthedialogue,entertheURLofthedocumentorWebpage.WritetheURLinthe
followingway:(fictionalexample)http://www.my­broker.com/table.html.Enterafile
namefromthelocalornetworkfilesystemintheusualway,i.e.,viatheFile>Open
dialogue.
OpenOffice.orgloadstheWebpageorfileinthebackground,thatis,withoutdisplayingit.
InthelargelistboxoftheExternalDatadialoguearethenamesofallthesheetsorareas
fromwhichtochoose.
4. Selectoneormoresheetsorareas,optionallyactivatetheautomaticupdatefunctionto
everynminutesandclickOK.
ThecontentsareinsertedasalinkintheCalcdocument.
5. Savethespreadsheet.Uponre­opening,Calcupdatesthelinkedcells.
6. UnderTools>Options>OpenOffice.orgCalc>General,choosetohavetheupdate,on
opening,executealways,uponrequestornever.Theupdatecanbestartedmanuallyinthe
dialogueunderEdit>Links.
OpenOffice.orgUserGuidefor2.x
234
Chapter4:SpreadsheetsinCalc
UsingCharts
Datafromspreadsheetscanberepresentedgraphicallyinachart.Awidevarietyofcharts
anddiagramssuitedforillustratingthestructureofthedatacanbeselectedandcreatedfrom
aspreadsheet.
1. Openanexistingspreadsheetorenterdatainanewsheetwithrowandcolumnheadings.
2. Selectthedatatogetherwiththeheadings.
3.
Long­clicktheInsertObjecticonfoundintheMaintoolbartodisplaythefloating
toolbar.ThenclicktheInsertCharticonandthecursorwillchangetoacross­hair
symbolwithasmallchartsymbol.
4. Inthespreadsheetdocument,openaselectionrectangledefiningthelocationandsizeof
thechart.Bothcanbemodifiedsubsequently.
5. Assoonasthemousebuttonisreleased,adialogueappearsthatmakesfurtherentries
possible.SimplyclickingtheCreatebuttoncreatesachartwiththedefaultsettings.
Thisexample,usingMovie1.sxcfromthesamplearchivementionedabove,showsthe
numberoftimespeopleofdifferentagegroupsinvariouscitiesgotothemovietheatre
annually.
OpenOffice.orgUserGuidefor2.x
235
Chapter4:SpreadsheetsinCalc
Iftheagegroups,ratherthanthetowns,shouldappearonthehorizontalaxis,tiltthechart.
Aslongasthechartremainsselected,themaintoolbarcontainsaDatainColumnsicon.
Clickandrotateitwiththemouse.
Otherchangestotheindividualelementsofthechartarealsopossible.Eitherdouble­clickon
theindividualpartsofthechartor,withthechartselected,choosetheindividualfunctions
fromtheFormatmenu.Forexample,double­clickingonthecolourpreviewinthelegend
automaticallyreformatsallrelevantdatapoints.Ontheotherhand,clickingonthe
backgroundintheboxofthelegend,formatsthebackgroundtothelegend.
IfthechartwascreatedfromthedataofaCalcspreadsheet,clickingonthedataseriesinthe
chartbecomeshighlighted.
Supposethere'sanCalcspreadsheetinthebackground,andnowitneedstobe
selectedforfurtherediting.OpentheShowDrawFunctionsfloatingtoolbar
andselectthefirsttool,theSelectarrow.Thisallowsthecharttobeselected
byclicking.
InaCalcchart,adataseriescanbeforwardsorbackwards.Aseriescanbearrangedinsuch
awaythatthelowest3Drepresentationsareasfarforwardaspossibleandthehigherones
furtherback.Tochangethearrangementinthechart,usetheappropriatecommandfromthe
contextmenuofadataseriesorchooseFormat>Order.TheoutputdataintheCalc
spreadsheetwillnotberearranged.
Note:WhenachartisinsertedinapresentationordrawingdocumentwiththeInsert>InsertChartfloating
toolbar,itisdisplayedwithasetofsampledata.Toinsertachartinaspreadsheet,highlightthecellsinthe
spreadsheetwhosevaluesshouldbedisplayedinthechart.
InWriterdocumentstoo,chartscanbeinsertedthatareobtainedfromthedataofaWritertable.Wherenodata
areselectedinaOpenOffice.orgWritertable,selectingInsert>Object>Chartalsoinsertsachartcontaining
sampledatainOpenOffice.orgWriter.
Valuesinachartcontainingsampledatacanbechangedbydouble­clickingonthechartandthenchoosingEdit>
ChartData.Whenthevaluesofachartobtainedfromselecteddataarechanged,thevaluesinthecellsofthetable
mustbechanged.Ifthechartisinatextdocument,press(
F9
)toupdatethechart.
Modificationofchartdataisalsopossibleif,forexample,achartiscopiedachartfromaCalcdocumentintoa
Writerdocumentandisnowdouble­clickedintheWriterdocument.Remember,however,onlythiscopyisbeing
editedandnottheoriginaldocument.
OpenOffice.orgUserGuidefor2.x
236
Documents you may be interested
Documents you may be interested