Oboji ceo red u kome se trenutno nalazi kursor

27/06/2013 § Leave a comment

Odabrati ceo red 1
Formulas
Name Manager
ActiveRow =1

Excel_CF002

Selektovati ceo sheet (polje izmedju A i 1)
Conditional Formatting
Use a formula to determine which cells to format
=row(A1)=ActiveRow
Izabrati boju kojom će red biti obojen
Kopirati makro
This Sheet
Option Explicit
Private Sub Worksheet_SelectionChange (ByVal Target As Range)
with ThisWorkbook.Names(“ActiveRow”)
.Name = “ActiveRow”
.RefersToR1C1 = “=” & ActiveCell.Row
End With
End Sub


Source:

http://chandoo.org/wp/2012/07/11/highlight-row-column-of-selected-cell-using-vba/
Download excel example:highlight-row-column-demo


http://www.msofficegurus.com/post/Excel-Highlight-the-Active-Cell.aspx

Color Palette:
http://dmcritchie.mvps.org/excel/colors.htm

Advertisements

Obojiti ceo red baziran na vrednosti ćelije u tom redu

27/06/2013 § Leave a comment

Odabrati ceo red

Conditional formatting

npr. =$e7 <> 0

i posle proširiti applies to na redove na koje želiš da se odnosi =$7:$25

Excel_CF001

Source:

http://stackoverflow.com/questions/10053095/color-entire-row-based-on-the-value-of-2nd-cell-in-that-row

Formula – odvoji imena i prezimena u posebne ćelije

22/06/2013 § Leave a comment

Extract First Name from Full Name

=LEFT(A1,FIND(” “,A1)-1)

Extract Last Name from Full Name

=RIGHT(A1,LEN(A1)-FIND(” “,A1))

 


Source:

http://www.bluemoosetech.com/microsoft-excel-functions.php?jid=32

http://www.extendoffice.com/documents/excel/829-excel-split-first-last-name.html

http://excelhints.com/2009/01/24/separate-full-name-into-first-last-name/

Formula – ime sheeta kopiranje

22/06/2013 § Leave a comment

Како променити име листа када се формула копира

=INDIRECT(“‘”&A1&”‘!$F$1”)

У ћелију А1 треба уписати име листа (sheeta) и копирати формулу


http://support.microsoft.com/kb/213933

http://support.microsoft.com/kb/151323

http://www.softpanorama.info/Office/Excel/Reference/excel_indirect_function.shtml

http://stackoverflow.com/questions/9776229/why-doesnt-this-vlookup-formula-using-the-indirect-function-work

Требао бих да пробам овај код: http://j-walk.com/ss/excel/tips/tip63.htm

Formula – Razlika izmedju dva datuma

22/06/2013 § Leave a comment

Како израчунати разлику између два датума

=DATEDIF(DАТЕ1,DАТЕ”2″m”)

Interval Meaning Description
m Months Complete calendar months between the dates.
d Days Number of days between the dates.
y Years Complete calendar years between the dates.
ym Months Excluding Years Complete calendar months between the dates as if they were of the same year.
yd Days Excluding Years Complete calendar days between the dates as if they were of the same year.
md Days Excluding Years And Months Complete calendar days between the dates as if they were of the same month and same year.

 


Source: http://www.cpearson.com/excel/datedif.aspx

Formula – Kalkulacija radnog vremena

22/06/2013 § Leave a comment

Калкулација колико времена проводите на послу током дана.
LowBound= 09:00, UpperBound=17.00 – Радно време за смену
CheckIn и CheckOut – Време доласка и одласка са посла
=IF(CheckOut>CheckIn,MAX(0,MIN(CheckOut,UpperBound)-MAX(CheckIn,LowBound)), MAX(0,UpperBound-MAX(CheckIn,LowBound))+MAX(0,MIN(CheckOut,UpperBound)-LowBound))


Source: http://puremis.net/excel/MastersText/text_Daniel.shtml

Formula – NETWORKDAYS

22/06/2013 § Leave a comment

=NETWORKDAYS(40544,40574,Holidays) = 19 (2 нерадна дана у јануару 3. јануар и 7. јануар)
01/01/2011 – 31/01/20111 и листа Holidays где су убачени нерадни празнични дани

Where Am I?

You are currently viewing the archives for June, 2013 at RheXenoR.