Docstoc

ex

Document Sample
ex Powered By Docstoc
					Date      Sales       Y/N   SR
   3/9/10     $3.00   Y     SR5
  3/12/10     $6.00   N     SR3
  3/16/10    $10.00   N     SR1
 Extract New Records That Are Not In Old List – Filter
                         Method
               & (Shift + 7) = join symbol
     Alt +D + F + F or Ctrl + Shift + L turn on Filter
             Shift + F11 inserts new sheet
MATCH function delivers an ordinal or relative position of
                     an item in a list
  Array formulas are entered with Ctrl + Shift + Enter

Date    Sales       Y/N   SR    Date       Sales     Y/N     SR Not In Other List
  3/8/10 $2.00      N     SR2     3/8/10     $2.00   N       SR2
 3/10/10 $4.00      N     SR2     3/9/10     $3.00   Y       SR5
 3/11/10 $5.00      Y     SR1    3/10/10     $4.00   N       SR2
 3/13/10 $7.00      Y     SR2    3/11/10     $5.00   Y       SR1
 3/14/10 $8.00      N     SR4    3/12/10     $6.00   N       SR3
 3/15/10 $9.00      Y     SR5    3/13/10     $7.00   Y       SR2
 3/17/10 $11.00     Y     SR3    3/14/10     $8.00   N       SR4
 3/18/10 $12.00     N     SR3    3/15/10     $9.00   Y       SR5
                                 3/16/10    $10.00   N       SR1
                                 3/17/10    $11.00   Y       SR3
                                 3/18/10    $12.00   N       SR3
 Extract New Records That Are Not In Old List – Filter
                         Method
               & (Shift + 7) = join symbol
     Alt +D + F + F or Ctrl + Shift + L turn on Filter
             Shift + F11 inserts new sheet
MATCH function delivers an ordinal or relative position of
                     an item in a list
  Array formulas are entered with Ctrl + Shift + Enter

Date    Sales       Y/N   SR    Date       Sales     Y/N     SR Not In Other List
  3/8/10 $2.00      N     SR2     3/8/10     $2.00   N       SR2                    1
 3/10/10 $4.00      N     SR2     3/9/10     $3.00   Y       SR5           #N/A
 3/11/10 $5.00      Y     SR1    3/10/10     $4.00   N       SR2                    2
 3/13/10 $7.00      Y     SR2    3/11/10     $5.00   Y       SR1                    3
 3/14/10 $8.00      N     SR4    3/12/10     $6.00   N       SR3           #N/A
 3/15/10 $9.00      Y     SR5    3/13/10     $7.00   Y       SR2                    4
 3/17/10 $11.00     Y     SR3    3/14/10     $8.00   N       SR4                    5
 3/18/10 $12.00     N     SR3    3/15/10     $9.00   Y       SR5                    6
                                 3/16/10    $10.00   N       SR1           #N/A
                                 3/17/10    $11.00   Y       SR3                    7
                                 3/18/10    $12.00   N       SR3                    8
Extract New Records That Are Not In Old List – Array Formula

To learn the basics of array formulas, see these two playlists:
Excel Array Formulas Beg - Advanced
Excel Extract Records From Database Table / List


     Count New Records that are in 2nd List, but not in 1st


1st List (Old List)                2nd List (New List)
Date        Sales     Y/N   SR     Date       Sales      Y/N   SR    Date   Sales   Y/N SR
  3/8/10 $2.00        N     SR2      3/8/10 $2.00        N     SR2
 3/10/10 $4.00        N     SR2      3/9/10 $3.00        Y     SR5
 3/11/10 $5.00        Y     SR1     3/10/10 $4.00        N     SR2
 3/13/10 $7.00        Y     SR2     3/11/10 $5.00        Y     SR1
 3/14/10 $8.00        N     SR4     3/12/10 $6.00        N     SR3
 3/15/10 $9.00        Y     SR5     3/13/10 $7.00        Y     SR2
 3/17/10 $11.00       Y     SR3     3/14/10 $8.00        N     SR4
 3/18/10 $12.00       N     SR3     3/15/10 $9.00        Y     SR5
                                    3/16/10 $10.00       N     SR1
                                    3/17/10 $11.00       Y     SR3
                                    3/18/10 $12.00       N     SR3
Extract New Records That Are Not In Old List – Array Formula

To learn the basics of array formulas, see these two playlists:
Excel Array Formulas Beg - Advanced
Excel Extract Records From Database Table / List


     Count New Records that are in 2nd List, but not in 1st
       3

1st List (Old List)                2nd List (New List)
Date        Sales     Y/N   SR     Date       Sales      Y/N   SR    Date        Sales Y/N   SR
  3/8/10 $2.00        N     SR2      3/8/10 $2.00        N     SR2      3/9/2010      3Y     SR5
 3/10/10 $4.00        N     SR2      3/9/10 $3.00        Y     SR5     3/12/2010      6N     SR3
 3/11/10 $5.00        Y     SR1     3/10/10 $4.00        N     SR2     3/16/2010     10 N    SR1
 3/13/10 $7.00        Y     SR2     3/11/10 $5.00        Y     SR1
 3/14/10 $8.00        N     SR4     3/12/10 $6.00        N     SR3
 3/15/10 $9.00        Y     SR5     3/13/10 $7.00        Y     SR2
 3/17/10 $11.00       Y     SR3     3/14/10 $8.00        N     SR4
 3/18/10 $12.00       N     SR3     3/15/10 $9.00        Y     SR5
                                    3/16/10 $10.00       N     SR1
                                    3/17/10 $11.00       Y     SR3
                                    3/18/10 $12.00       N     SR3
Date      Sales    Y/N   SR    CC            Date        Sales     Y/N   SR    CC
 3/8/2010 $2.00    N     SR2   402452NSR2     3/8/2010     $2.00   N     SR2   402452NSR2
3/10/2010 $4.00    N     SR2   402474NSR2     3/9/2010     $3.00   Y     SR5   402463YSR5
3/11/2010 $5.00    Y     SR1   402485YSR1    3/10/2010     $4.00   N     SR2   402474NSR2
3/13/2010 $7.00    Y     SR2   402507YSR2    3/11/2010     $5.00   Y     SR1   402485YSR1
3/14/2010 $8.00    N     SR4   402518NSR4    3/12/2010     $6.00   N     SR3   402496NSR3
3/15/2010 $9.00    Y     SR5   402529YSR5    3/13/2010     $7.00   Y     SR2   402507YSR2
3/17/2010 $11.00   Y     SR3   4025411YSR3   3/14/2010     $8.00   N     SR4   402518NSR4
3/18/2010 $12.00   N     SR3   4025512NSR3   3/15/2010     $9.00   Y     SR5   402529YSR5
                                             3/16/2010    $10.00   N     SR1   4025310NSR1
                                             3/17/2010    $11.00   Y     SR3   4025411YSR3
                                             3/18/2010    $12.00   N     SR3   4025512NSR3

                                             Count New Records             3

                                             Date      Sales    Y/N      SR    CC
                                              3/9/2010        3Y         SR5   402463YSR5
                                             3/12/2010        6N         SR3   402496NSR3
                                             3/16/2010       10 N        SR1   4025310NSR1
add column and then array formula
Date      Sales    Y/N   SR    Date        Sales     Y/N   SR    Count New Records          3
 3/8/2010 $2.00    N     SR2    3/8/2010     $2.00   N     SR2
3/10/2010 $4.00    N     SR2    3/9/2010     $3.00   Y     SR5   Date      Sales    Y/N   SR
3/11/2010 $5.00    Y     SR1   3/10/2010     $4.00   N     SR2    3/9/2010        3Y      SR5
3/13/2010 $7.00    Y     SR2   3/11/2010     $5.00   Y     SR1   3/12/2010        6N      SR3
3/14/2010 $8.00    N     SR4   3/12/2010     $6.00   N     SR3   3/16/2010       10 N     SR1
3/15/2010 $9.00    Y     SR5   3/13/2010     $7.00   Y     SR2
3/17/2010 $11.00   Y     SR3   3/14/2010     $8.00   N     SR4
3/18/2010 $12.00   N     SR3   3/15/2010     $9.00   Y     SR5
                               3/16/2010    $10.00   N     SR1
                               3/17/2010    $11.00   Y     SR3
                               3/18/2010    $12.00   N     SR3
add array formula

To learn the basics of array formulas,
see these two playlists:
Excel Array Formulas Beg - Advanced
Excel Extract Records From Database Table / List
Date      Sales    Y/N   SR    Date        Sales     Y/N   SR Not In Other List
 3/8/2010 $2.00    N     SR2    3/8/2010     $2.00   N     SR2      FALSE
3/10/2010 $4.00    N     SR2    3/9/2010     $3.00   Y     SR5       TRUE
3/11/2010 $5.00    Y     SR1   3/10/2010     $4.00   N     SR2      FALSE
3/13/2010 $7.00    Y     SR2   3/11/2010     $5.00   Y     SR1      FALSE
3/14/2010 $8.00    N     SR4   3/12/2010     $6.00   N     SR3       TRUE
3/15/2010 $9.00    Y     SR5   3/13/2010     $7.00   Y     SR2      FALSE
3/17/2010 $11.00   Y     SR3   3/14/2010     $8.00   N     SR4      FALSE
3/18/2010 $12.00   N     SR3   3/15/2010     $9.00   Y     SR5      FALSE
                               3/16/2010    $10.00   N     SR1       TRUE
                               3/17/2010    $11.00   Y     SR3      FALSE
                               3/18/2010    $12.00   N     SR3      FALSE
use filter
Date      Sales    Y/N   SR    Date        Sales     Y/N   SR Is Record Not In Old List?
 3/8/2010 $2.00    N     SR2    3/8/2010     $2.00   N     SR2           FALSE
3/10/2010 $4.00    N     SR2    3/9/2010     $3.00   Y     SR5           TRUE
3/11/2010 $5.00    Y     SR1   3/10/2010     $4.00   N     SR2           FALSE
3/13/2010 $7.00    Y     SR2   3/11/2010     $5.00   Y     SR1           FALSE
3/14/2010 $8.00    N     SR4   3/12/2010     $6.00   N     SR3           TRUE
3/15/2010 $9.00    Y     SR5   3/13/2010     $7.00   Y     SR2           FALSE
3/17/2010 $11.00   Y     SR3   3/14/2010     $8.00   N     SR4           FALSE
3/18/2010 $12.00   N     SR3   3/15/2010     $9.00   Y     SR5           FALSE
                               3/16/2010    $10.00   N     SR1           TRUE
                               3/17/2010    $11.00   Y     SR3           FALSE
                               3/18/2010    $12.00   N     SR3           FALSE


                               Date      Sales       Y/N   SR
                                3/9/2010 $3.00       Y     SR5
                               3/12/2010 $6.00       N     SR3
                               3/16/2010 $10.00      N     SR1
use advanced Filter
Date      Sales    Y/N   SR    Date      Sales        Y/N   SR
 3/8/2010 $2.00    N     SR2    3/8/2010      $2.00   N     SR2
3/10/2010 $4.00    N     SR2    3/9/2010      $3.00   Y     SR5
3/11/2010 $5.00    Y     SR1   3/10/2010      $4.00   N     SR2
3/13/2010 $7.00    Y     SR2   3/11/2010      $5.00   Y     SR1
3/14/2010 $8.00    N     SR4   3/12/2010      $6.00   N     SR3
3/15/2010 $9.00    Y     SR5
3/17/2010 $11.00   Y     SR3   3/13/2010     $7.00    Y     SR2
3/18/2010 $12.00   N     SR3   3/14/2010     $8.00    N     SR4
                               3/15/2010     $9.00    Y     SR5
                               3/16/2010    $10.00    N     SR1
                               3/17/2010    $11.00    Y     SR3
                               3/18/2010    $12.00    N     SR3

                               Count New Records               3 << == If blanks

                               Date      Sales        Y/N   SR
                                3/9/2010      $3.00   Y     SR5 << == If blanks
                               3/12/2010      $6.00   N     SR3
                               3/16/2010     $10.00   N     SR1
Criteria 1              Jacket
Criteria 2              Med
Last Price that meets
both criteria
Code                    Item      Size    Price
SW001                   Sweater   Small   $10.00
JK001                   Jacket    Small   $30.00
PN001                   Pants     Small   $25.00
SW002                   Sweater   Med     $12.00
JK002                   Jacket    Med     $35.00
PN002                   Jacket    Med     $30.00
SW003                   Sweater   Large   $38.00
DK003                   Jacket    Med     $32.00




Item                    Size
Sweater                 Small
Jacket                  Med
Pants                   Large
Criteria 1              Jacket
Criteria 2              Med

Last Price that meets
both criteria            $32.00

Code                    Item      Size    Price
SW001                   Sweater   Small      $10.00
JK001                   Jacket    Small      $30.00
PN001                   Pants     Small      $25.00
SW002                   Sweater   Med        $12.00
JK002                   Jacket    Med        $35.00
PN002                   Jacket    Med        $30.00
SW003                   Sweater   Large      $38.00
DK003                   Jacket    Med        $32.00




Item                    Size
Sweater                 Small
Jacket                  Med
Pants                   Large
PivotTable To Count How Many Times A Word Occurs In Word Document
         PivotTable To Count How Many Times A Word Occurs In Word Document

Words
The                 Count of Words
Story               Words            Total
of                              2010     1
Excel               Excellent            1
       2010         a                    7
How                 addition             1
to                  adds                 1
build               allow                1
efficient           amaze                1
spreadsheets        an                   2
to                  and                  9
save                any                  1
time                are                  3
get                 as                   1
promoted            at                   2
and                 basics               1
have                be                   1
extra               Because              2
time                become               1
for                 before               1
vacation            beginning            1
Most                big                  1
jobs                boss                 1
require             bosses               1
Excel               both                 1
even                breeze               1
though              build                1
most                but                  1
people              can                  7
don’t               candidate            1
know                complete             1
how                 completed            1
to                  course               1
use                 deal                 1
Excel               desired              1
This                do                   2
is                  don’t                1
great               dry                  1
Why                 during               1
Because             efficiency           2
if                  efficient            5
you                 efficiently          1
learn               employee             1
how                 employees            1
to            Employers          1
use           end                1
Excel         endeavors          1
well          enjoyable          1
people        entity             1
will          even               1
want          Excel             12
to            Excel-efficient    1
hire          extra              2
you           five-minute        1
If            for                3
you           foundations        1
are           from               1
an            further            1
employee      get                4
a             great              2
boss          have               3
or            help               2
working       hire               1
at            hired              1
home          home               1
knowing       hour               1
how           How                5
to            however            1
use           if                 3
Excel         impress            1
efficiently   in                 5
will          interview          1
not           into               1
only          is                 1
help          it                 1
you           job                4
to            jobs               1
get           jump               1
a             just               1
job           know               1
but           knowing            1
it            knowledgeable      1
will          land               1
allow         later              1
you           leads              1
to            learn              2
do            life               1
many          like               2
required      look               1
tasks         looking            1
with          lot                1
speed         make           1
and           many           1
efficiency    more           4
Excel         Most           2
can           must           1
help          No             1
you           not            1
turn          notice         1
your          of             7
one           one            1
hour          only           1
payroll       or             1
job           order          1
into          others         1
a             our            1
five-minute   ourselves      1
breeze        payroll        1
and           people         2
that          Pivot          1
weekly        produce        1
summary       professional   1
report        promoted       2
can           promotion      1
be            quickly        1
completed     ready          1
in            report         1
just          reports        1
six-clicks    require        1
with          required       1
a             save           3
Pivot         see            1
Table         six-clicks     1
We            skill          1
will          skills         1
see           soak           1
how           sounds         1
to            speed          2
do            sponges        1
both          spreadsheets   1
of            Still          1
those         Story          1
later         such           1
Efficient     summary        1
use           Table          1
of            tasks          1
Excel         techniques     1
will          that           6
save           The             3
a              their           1
lot            then            1
of             This            2
time           those           1
That           though          2
time           time            7
adds           to             15
up             turn            1
to             up              2
extra          use             5
time           vacation        2
for            vacations       1
your           want            1
more           We              5
enjoyable      weekly          1
endeavors      well            1
in             Why             1
life           will            6
such           with            5
as             work            1
vacations      working         1
In             you             8
addition       your            4
your           Grand Total   300
bosses
and
employees
will
notice
that
you
are
efficient
and
can
produce
professional
looking
reports
that
impress
This
of
course
leads
to
promotion
more
quickly
Still
further
your
knowledgeable
and
efficient
use
of
Excel
can
land
you
a
job
during
an
interview
Employers
are
like
dry
sponges
ready
to
soak
up
any
job
candidate
that
can
make
their
entity
more
efficient
with
Excel
skills
Save
time
Get
hired
Get
promoted
Have
more
time
for
vacation
That
sounds
like
a
great
skill
to
have
In
order
to
become
Excel-efficient
however
we
must
learn
the
foundations
of
Excel
before
we
can
jump
in
and
complete
our
desired
work
with
speed
and
efficiency
No
big
deal
though
because
if
we
look
at
Excel
basics
from
the
beginning
to
end
then
we
can
amaze
ourselves
and
others
with
Excellent
Excel
techniques
Build Time Schedule With 15 Minute Intervals
                  Build Time Schedule With 15 Minute Intervals




            8:00 AM   8:15 AM   8:30 AM    8:45 AM    9:00 AM    9:15 AM   9:30 AM   9:45 AM
Monday
Tuesday
Wednesday
Thursday
Friday
10:00 AM 10:15 AM 10:30 AM 10:45 AM 11:00 AM 11:15 AM 11:30 AM 11:45 AM 12:00 PM
12:15 PM 12:30 PM 12:45 PM   1:00 PM   1:15 PM   1:30 PM   1:45 PM   2:00 PM   2:15 PM
2:30 PM   2:45 PM   3:00 PM   3:15 PM   3:30 PM   3:45 PM   4:00 PM   4:15 PM   4:30 PM
4:45 PM   5:00 PM
                     For more about Conditional Formatting, see this video:
                         For more about logical formulas, see this video:

Dates       Values                                  Dates               Dates       Values
3/10/2010   A                                       >=3/12/2010         <=3/24/2010 A
3/11/2010   B
3/12/2010   B                                       End Date                3/24/2010
3/13/2010   A                                       Begin Date              3/12/2010
3/14/2010   C                                       Criteria             A
3/15/2010   A                                           Count A that fall between 3/12/2010 and 3/24/2010
3/16/2010   B                                       COUNTIFS                        5
3/17/2010   B                                       DCOUNT                          5
3/18/2010   A                                       SUMPRODUCT                      5
3/19/2010   C
3/20/2010   A
3/21/2010   B
3/22/2010   B
3/23/2010   A
3/24/2010   C
3/25/2010   A
3/26/2010   B
           Highline Excel Class 21: Conditional Formatting 12 Examples
           Highline Excel Class 11: Logical formulas, IS & IF functions




2/2010 and 3/24/2010
                     For more about Conditional Formatting, see this video:
                         For more about logical formulas, see this video:

Dates       Values                                  Dates               Dates       Values
3/10/2010   A             FALSE          FALSE      >=3/12/2010         <=3/24/2010 A
3/11/2010   B             FALSE          FALSE
3/12/2010   B             FALSE          FALSE      End Date                3/24/2010
3/13/2010   A             TRUE           TRUE       Begin Date              3/12/2010
3/14/2010   C             FALSE          FALSE      Criteria             A
3/15/2010   A             TRUE           TRUE           Count A that fall between 3/12/2010 and 3/24/2010
3/16/2010   B             FALSE          FALSE      COUNTIFS                        5
3/17/2010   B             FALSE          FALSE      DCOUNT                          5
3/18/2010   A             TRUE           TRUE       SUMPRODUCT                      5
3/19/2010   C             FALSE          FALSE
3/20/2010   A             TRUE           TRUE
3/21/2010   B             FALSE          FALSE
3/22/2010   B
3/23/2010   A
3/24/2010   C
3/25/2010   A
3/26/2010   B
           Highline Excel Class 21: Conditional Formatting 12 Examples
           Highline Excel Class 11: Logical formulas, IS & IF functions




2/2010 and 3/24/2010
 For COUNTIFS comparative operator must be in quotes, & is the join
                             symbol
      DCOUNTA must have criteria and field names for criteria
SUMPRODUCT can handle arrays, the double negative converts TRUEs
                     and FALSEs to 1s and 0s.
           For more about SUMPRODUCT see this video                 Excel Formula Efficiency 1: SUMPRODUCT function 1
             For more about COUNTIFS see this video                 Excel Formula Efficiency 4: SUMIFS & COUNTIFS Are
            For more about D functions see this video               Excel Magic Trick 330: DCOUNT DSUM between 2 N
                                                                    Excel Magic Trick 310: D Functions & Date Criteria

Dates        Values   Dates              Dates           Values
 3/10/2010   A        >=3/12/2010        <=3/24/2010     A
 3/11/2010   B
 3/12/2010   B        End Date                  3/24/2010
 3/13/2010   A        Begin Date                3/12/2010
 3/14/2010   C        Criteria            A
 3/15/2010   A             Count A that fall between 3/12/2010 and 3/24/2010
 3/16/2010   B        COUNTIFS
 3/17/2010   B        DCOUNTA
 3/18/2010   A        SUMPRODUCT
 3/19/2010   C
 3/20/2010   A
 3/21/2010   B
 3/22/2010   B
 3/23/2010   A
 3/24/2010   C
 3/25/2010   A
 3/26/2010   B
iciency 1: SUMPRODUCT function 12 Examples
iciency 4: SUMIFS & COUNTIFS Are Fast
 330: DCOUNT DSUM between 2 Numbers
 310: D Functions & Date Criteria
 For COUNTIFS comparative operator must be in quotes, & is the join
                             symbol
      DCOUNTA must have criteria and field names for criteria
SUMPRODUCT can handle arrays, the double negative converts TRUEs
                     and FALSEs to 1s and 0s.
           For more about SUMPRODUCT see this video                 Excel Formula Efficiency 1: SUMPRODUCT function 1
             For more about COUNTIFS see this video                 Excel Formula Efficiency 4: SUMIFS & COUNTIFS Are
            For more about D functions see this video               Excel Magic Trick 330: DCOUNT DSUM between 2 N
                                                                    Excel Magic Trick 310: D Functions & Date Criteria

Dates        Values   Dates              Dates           Values
 3/10/2010   A        >=3/12/2010        <=3/24/2010     C
 3/11/2010   B
 3/12/2010   B        End Date                  3/24/2010
 3/13/2010   A        Begin Date                3/12/2010
 3/14/2010   C        Criteria            C
 3/15/2010   A             Count C that fall between 3/12/2010 and 3/24/2010
 3/16/2010   B        COUNTIFS                          3
 3/17/2010   B        DCOUNTA                           3
 3/18/2010   A        SUMPRODUCT                        3
 3/19/2010   C
 3/20/2010   A
 3/21/2010   B
 3/22/2010   B
 3/23/2010   A
 3/24/2010   C
 3/25/2010   A
 3/26/2010   B
iciency 1: SUMPRODUCT function 12 Examples
iciency 4: SUMIFS & COUNTIFS Are Fast
 330: DCOUNT DSUM between 2 Numbers
 310: D Functions & Date Criteria
                          Check To See If Items Is In List – Case Sensitive
                   MATCH function tells you the relative position of an item in a list
                     ISNUMBER function says TRUE when the item is a number

 OR function can take more than one logical test and when any one of them is true it will deliver a true

  EXACT function compares two items and tells you whether they are the same and it is case sensitive.

                                  Is This In Other List Case Not Case
Names               Names         Sensitive                  Sensitive                         mikeyroro
Boomerang           Boomerang
boomerang           boom
Rang                rang
boom                Boom
                          Check To See If Items Is In List – Case Sensitive
                   MATCH function tells you the relative position of an item in a list
                     ISNUMBER function says TRUE when the item is a number

 OR function can take more than one logical test and when any one of them is true it will deliver a true

  EXACT function compares two items and tells you whether they are the same and it is case sensitive.

                              Is This In Other List Case Not Case
Names               Names     Sensitive                  Sensitive                             mikeyroro
Boomerang           Boomerang            TRUE              TRUE
boomerang           boom                 TRUE              TRUE
Rang                rang                 FALSE             TRUE
boom                Boom                 FALSE             TRUE
         Calculating Last Time File Was Accessed From Server Computer
      Advanced Filter - Unique Records - be sure to highlight field name also.
                For more about Array Formulas, see this playlist:                Excel Array Formulas Beg - Advanced

File names      Times
red                2:34 PM
ddd                2:16 PM
t                  1:19 PM
red                2:19 PM
ddd                9:16 AM
u                  3:32 PM
red               11:32 PM
ddd                2:49 PM
v                  3:40 PM
red                4:56 PM
ddd                7:10 AM
w                  6:20 PM
red                4:37 PM
ddd                7:26 PM
x                  3:30 PM
Formulas Beg - Advanced
             Calculating Last Time File Was Accessed From Server Computer
                    For more about Array Formulas, see this playlist:       Excel Array Formulas Beg - Advanced

File names         Times            File names Count        Last Time
red                   2:34 PM       red                 5     11:32:24 PM
ddd                   2:16 PM       ddd                 5      7:26:27 PM
t                     1:19 PM       t                   1      1:19:57 PM
red                   2:19 PM       u                   1      3:32:33 PM
ddd                   9:16 AM       v                   1      3:40:16 PM
u                     3:32 PM       w                   1      6:20:21 PM
red                  11:32 PM       x                   1      3:30:42 PM
ddd                   2:49 PM
v                     3:40 PM
red                   4:56 PM
ddd                   7:10 AM
w                     6:20 PM
red                   4:37 PM
ddd                   7:26 PM
x                     3:30 PM
Formulas Beg - Advanced
Products         Price
Bellen              $24.00
Carlota             $26.00
Sunshine            $22.00
Majestic Beaut      $32.00

Keyboard For Create Name From Selection is Ctrl + Shift + F3
Paste Name = F3
List of Names = Ctrl + F3
                             Data Validation List To Add Drop-Down To Cell
                                       Alt + D + L opens Data Validation
                                              Choose Allows: List
                                                 Select a Source
                              If it is on a different sheet you must Name it

Products         Price           Products          Price
Bellen              $22.00
Carlota             $24.00
Sunshine            $19.00       Products          Price
Majestic Beaut      $23.50




Use Drop Down To Select Product
                             Data Validation List To Add Drop-Down To Cell
                                       Alt + D + L opens Data Validation
                                              Choose Allows: List
                                                 Select a Source
                              If it is on a different sheet you must Name it

Products         Price           Products          Price
Bellen              $22.00       Sunshine                    $19.00
Carlota             $24.00
Sunshine            $19.00       Products          Price
Majestic Beaut      $23.50       Carlota                     $26.00




Use Drop Down To Select Product
     Dynamic Data Validation List To Add Drop-Down To Cell OFFSET or Table Feature?
 The OFFSET function can deliver a value from a cell or it can deliver a range to a formula or
                                function ==> see list to right
               Ctrl + T (2003 Ctrl + L) creates a table with a dynamic range.


Products         Price         Products    Price                  Products          Price
Bellen              $22.00                                        Bellen               $22.00
Carlota             $24.00                                        Carlota              $24.00
Sunshine            $19.00                                        Sunshine             $19.00
Majestic Beaut      $23.50                                        Majestic Beaut       $23.50
                                                                  Quad                 $40.00
                                                                  Sunset               $35.00
Products   Price
Sunset
Learn about the OFFSETS 5 arguments:

1.   Starting point
2.   How many rows (up or down) do you want to move the starting point?
3.   How many columns (left or right) do you want to move the starting point?
4.   How tall is the range?
5.   How wide is the range?

COUNTA function counts cells that are not empty
     Dynamic Data Validation List To Add Drop-Down To Cell OFFSET or Table Feature?
 The OFFSET function can deliver a value from a cell or it can deliver a range to a formula or
                                function ==> see list to right
               Ctrl + T (2003 Ctrl + L) creates a table with a dynamic range.


Products         Price         Products    Price                  Products          Price
Bellen              $22.00     Quad                    $40.00     Bellen               $22.00
Carlota             $24.00                                        Carlota              $24.00
Sunshine            $19.00                                        Sunshine             $19.00
Majestic Beaut      $23.50                                        Majestic Beaut       $23.50
Quad                $40.00                                        Quad                 $40.00
                                                                  Sunset               $35.00
Products     Price
Sunset          $35.00




   #VALUE!
Learn about the OFFSETS 5 arguments:

1.   Starting point
2.   How many rows (up or down) do you want to move the starting point?
3.   How many columns (left or right) do you want to move the starting point?
4.   How tall is the range?
5.   How wide is the range?

COUNTA function counts cells that are not empty
                                    Data Validation List Drop-Down Based On 2nd Data Validation List Drop-Down
                                             INDIRECT function takes text and converts it to a reference
                                         SUBSTITUTE will find spaces and replace them with an underscore.




Boomerang Events   Boom 1                         Boom 2               Boom 3
Aussie Round       Majestic Beaut                 Sunset               Sunshine
Trick Catch        Carlota                        Quad                 Bellen WindDancer
Fast Catch         TriFly                         GelFast              Sunshine
GLORP              Quad                           Bellen WindDancer    Sunshine
Doubling           Carlota                        Majestic Beaut       Sunshine
Accuracy           TriFly                         Rad German           Sunshine

Boomerang Events   Boom To Use
ata Validation List Drop-Down
erts it to a reference
 em with an underscore.




            Boom 4




            Bellen WindDancer
            GelFast
                                    Data Validation List Drop-Down Based On 2nd Data Validation List Drop-Down
                                             INDIRECT function takes text and converts it to a reference
                                         SUBSTITUTE will find spaces and replace them with an underscore.




Boomerang Events   Boom 1                         Boom 2               Boom 3
Aussie RoundAN     Majestic Beaut                 Sunset               Sunshine
Trick Catch1AN     Carlota                        Quad                 Bellen WindDancer
Fast CatchAN       TriFly                         GelFast              Sunshine
G L O R PAN        Quad                           Bellen WindDancer    Sunshine
DoublingAN         Carlota                        Majestic Beaut       Sunshine
AccuracyAN         TriFly                         Rad German           Sunshine

Boomerang Events   Boom To Use
Aussie RoundAN        Majestic Beaut


                   Majestic Beaut
ata Validation List Drop-Down
erts it to a reference
 em with an underscore.




            Boom 4




            Bellen WindDancer
            GelFast

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:2
posted:12/19/2011
language:
pages:57