Podobne
- Strona startowa
- (ebook pdf) Teach Yourself Database Programming with Visual C in 21 days
- (ebook pdf) Teach Yourself SQL in 21 Days
- Linux. .Mandrake.10.Podręcznik.Użytkownika.[eBook.PL] (3)
- (ebook PDF Philosophy) Russell, Bertrand Political Ideals
- (eBook) James, William The Principles of Psychology Vol. I
- [eBook] DirectX 3D Graphics Programming Bible
- (ebook PDF) Schreiner Object oriented Programming With A
- Petersin Thomas Ogrodnik Szoguna (2)
- Apulejusz Metamorfozy
- 05wininout (6)
- zanotowane.pl
- doc.pisz.pl
- pdf.pisz.pl
- myszkuj.opx.pl
Cytat
Do celu tam się wysiada. Lec Stanisław Jerzy (pierw. de Tusch-Letz, 1909-1966)
A bogowie grają w kości i nie pytają wcale czy chcesz przyłączyć się do gry (. . . ) Bogowie kpią sobie z twojego poukładanego życia (. . . ) nie przejmują się zbytnio ani naszymi planami na przyszłość ani oczekiwaniami. Gdzieś we wszechświecie rzucają kości i przypadkiem wypada twoja kolej. I odtąd zwyciężyć lub przegrać - to tylko kwestia szczęścia. Borys Pasternak
Idąc po kurzych jajach nie podskakuj. Przysłowie szkockie
I Herkules nie poradzi przeciwko wielu.
Dialog półinteligentów równa się monologowi ćwierćinteligenta. Stanisław Jerzy Lec (pierw. de Tusch - Letz, 1909-1966)
[ Pobierz całość w formacie PDF ]
.Neither of the conditional formatting formulas presentedcould distinguish between cells that contain a date and cells that contain a normalvalue.You can use the AND function to ensure that the formatting applies only todate cells.The following conditional formatting formula applies formatting to cell A1 if itcontains a date and the month is June:=AND(HASDATE(A1),MONTH(A1)=6)The following conditional formatting formula applies formatting to cell A1 if itcontains a date and the date falls on a weekend:=AND(HASDATE(A1),OR(WEEKDAY(A1)=7,WEEKDAY(A1)=1)) 4800-x Ch19.F 8/27/01 11:58 AM Page 532532 Part V: Miscellaneous Formula TechniquesIDENTIFYING LINK FORMULASYou may want to identify cells that contain a link formula (a formula that uses areference in a different workbook).The following VBA function returns TRUE if thecell contains a formula that contains an external link.The HASLINK function usesVBA s versatile Like operator to determine whether a formula contains a set ofsquare brackets.Function HASLINK(cell)If cell.HasFormula ThenHASLINK = cell.Formula Like *[[]* ElseHASLINK = FalseEnd IfEnd FunctionTo apply conditional formatting to cells that contain a link, you can create aconditional formatting formula such as the following:=HASLINK(A1)The HASLINK function is not perfect.In some cases it will falsely identify aformula as being a linked formula.For example, the following formula con-tains a set of square brackets, but it is not a linked formula.The HASLINKfunction, however, reports otherwise.= [ &A1& ] IDENTIFYING INVALID DATAYou might have a situation in which the data entered must adhere to some veryspecific rules, and you d like to apply special formatting if the data entered is notvalid.You might have part numbers that consist of seven characters: four upper-case alphabetic characters, followed by a hyphen, and then a two-digit number.Forexample: ADSS-09 or DYUU-43.You can write a conditional formatting formula to determine if part numbersadhere to this structure, but the formula is very complex.The following formula,for example, returns TRUE only if the value in A1 meets the part number rulesspecified:=AND(LEN(A1)=7,AND(LEFT(A1)>= A ,LEFT(A1)= A ,MID(A1,2,1)= A ,MID(A1,3,1)= A ,MID(A1,4,1)=0,VALUE(MID(A1,6,2))A1 4800-x Ch19.F 8/27/01 11:58 AM Page 539Chapter 19: Conditional Formatting and Data Validation 539This formula assumes that A2 is the upper left cell in the selected range.Notethat you can t use this formula for a cell in row 1.ACCEPTING NONDUPLICATE ENTRIES ONLYThe following data validation formula does not permit the user to make a duplicateentry in the range A1:C20:=COUNTIF($A$1:$C$20,A1)=1This formula assumes that A1 is the upper left cell in the selected range.Notethat the first argument for COUNTIF is an absolute reference.The second argumentis a relative reference, and it adjusts for each cell in the validation range.Figure19-22 shows this validation criteria in effect, using a custom error alert message.Figure 19-22: Using data validation to prevent duplicate entries in a rangeACCEPTING TEXT THAT BEGINS WITH A The following data validation formula demonstrates how to check for a specificcharacter.In this case, the formula ensures that the user s entry is a text string thatbegins with the letter A (either upper- or lowercase).=LEFT(A1)= a This formula assumes that the upper left cell in the selected range is cell A1.The following formula is a variation of this validation formula.In this case, theformula ensures that the entry begins with the letter A and contains exactly fivecharacters.=COUNTIF(A1, A???? )=1 4800-x Ch19.F 8/27/01 11:58 AM Page 540540 Part V: Miscellaneous Formula TechniquesUsing Custom Worksheet Functions inData Validation FormulasEarlier in this chapter, I described how to use custom VBA functions for customformatting (see Using Custom Functions in Conditional Formatting Formulas ).Forsome reason, Excel does not permit you to use a custom VBA function in a datavalidation formula
[ Pobierz całość w formacie PDF ]