Prev: Sumif across a table
Next: my paste special function won't let me copy formulas
From: C-Breeze on 6 Apr 2010 16:10
We use Excel for our memebrship data, on our zip code entires for NJ and Y
which have a leading zero we get a blank. On last number in CC entry we only
are able to display, 0, 1, or 2
-- How do we over come these issue?
C-Ya

GC
From: Teethless mama on 6 Apr 2010 19:26
Assuming your data in A1:F5

NY MO IL NJ KS
Jim x x x
Joe x x x x
Amy x x x
Jane x x x x

Criteria in G1

In H1:
=LOOKUP("zzzz",CHOOSE({1,2},"",INDEX($A$2:$A$5,SMALL(IF(($B$1:$F$1=$G$1)*($B$2:$F$5="x"),ROW(INDIRECT("1:"&ROWS($A$2:$A$5)))),ROWS($1:1)))))

ctrl+shift+enter, not just enter
copy down as far as needed



"C-Breeze" wrote:

> We use Excel for our memebrship data, on our zip code entires for NJ and Y
> which have a leading zero we get a blank. On last number in CC entry we only
> are able to display, 0, 1, or 2
> -- How do we over come these issue?
> C-Ya
>
> GC
From: Teethless mama on 6 Apr 2010 19:31
Sorry, wrong post


"Teethless mama" wrote:

> Assuming your data in A1:F5
>
> NY MO IL NJ KS
> Jim x x x
> Joe x x x x
> Amy x x x
> Jane x x x x
>
> Criteria in G1
>
> In H1:
> =LOOKUP("zzzz",CHOOSE({1,2},"",INDEX($A$2:$A$5,SMALL(IF(($B$1:$F$1=$G$1)*($B$2:$F$5="x"),ROW(INDIRECT("1:"&ROWS($A$2:$A$5)))),ROWS($1:1)))))
>
> ctrl+shift+enter, not just enter
> copy down as far as needed
>
>
>
> "C-Breeze" wrote:
>
> > We use Excel for our memebrship data, on our zip code entires for NJ and Y
> > which have a leading zero we get a blank. On last number in CC entry we only
> > are able to display, 0, 1, or 2
> > -- How do we over come these issue?
> > C-Ya
> >
> > GC
From: FSt1 on 7 Apr 2010 00:26
hi
if you are entering the zips as numbers, excel will strip any leading zero
off.
to keep the leading zeros, you have to preformat the cell as text or preceed
the entry with an apostrophe ie '00001.

as excel see it leading zeros on numbers are unnecessary so it strips them
but leading zeros on id numbers formated as text are kept. and a zip code
would be considered an id number or a number(?) that you wouldn't want to do
math to.
think...have you ever had a reason to devide your zip by 2 or add 1000 to
your phone number. technically these are not true numbers but ids using
number characters as text.
yes i know. real technical. but if you can't think like the computer, you
have problems understanding what it's doing.

regards
FSt1

"C-Breeze" wrote:

> We use Excel for our memebrship data, on our zip code entires for NJ and Y
> which have a leading zero we get a blank. On last number in CC entry we only
> are able to display, 0, 1, or 2
> -- How do we over come these issue?
> C-Ya
>
> GC
 | 
Pages: 1
Prev: Sumif across a table
Next: my paste special function won't let me copy formulas