Assign values for one column to another.

Hi

I have in column T certain numbers and texts that that I require to assign a 
value to as below, in the adjacent column. Again any pointers would be much 
appreciated.

Kind Regards

Celticshadow

T   U  

1   1
2   2
3   3
4   4
5   5
6   6
7   7
8   8
9   9
0   10
F   10
UR 10
U   10
R   10
S   10
L   10
P   10
PU  10
BD  10
D   10
0
10/14/2008 3:26:01 PM
excel 39879 articles. 2 followers. Follow

8 Replies
994 Views

Similar Articles

[PageSpeed] 22

Well, imagine that two-column table occupies cells Y1:Z20. Put this
formula in U1:

=3DVLOOKUP(T1,Y$1,Z$20,2,0)

and copy down.

Hope this helps.

Pete

On Oct 14, 4:26=A0pm, Celticshadow
<Celticsha...@discussions.microsoft.com> wrote:
> Hi
>
> I have in column T certain numbers and texts that that I require to assig=
n a
> value to as below, in the adjacent column. Again any pointers would be mu=
ch
> appreciated.
>
> Kind Regards
>
> Celticshadow
>
> T =A0 U =A0
>
> 1 =A0 1
> 2 =A0 2
> 3 =A0 3
> 4 =A0 4
> 5 =A0 5
> 6 =A0 6
> 7 =A0 7
> 8 =A0 8
> 9 =A0 9
> 0 =A0 10
> F =A0 10
> UR 10
> U =A0 10
> R =A0 10
> S =A0 10
> L =A0 10
> P =A0 10
> PU =A010
> BD =A010
> D =A0 10

0
pashurst (2576)
10/14/2008 3:34:10 PM
Hi Pete

Unfortunately that does not seem to work I either get a n/a or value error. 
It may be because I have not explained the situation very well. Column T row 
1 downwards could contain any one of the below texts or numbers. Thus if 
column T row 1 contained the figure 0 then I would like to be able to assign 
the value 10 and if column T row 2 contained the letter u then again I would 
like to assign the value 10.


Column T
Row 1              
Contains
one of the     Value to
below            Assign
1                      1
2                      2
3                      3
4                      4
5                      5
6                      6
7                      7
8                      8
9                      9
0                     10
F                     10
UR                   10
U                     10
R                    10
S                    10
L                    10
P                    10
PU                  10
BD                 10
D                   10

Kind Regards

Celticshadow

"Pete_UK" wrote:

> Well, imagine that two-column table occupies cells Y1:Z20. Put this
> formula in U1:
> 
> =VLOOKUP(T1,Y$1,Z$20,2,0)
> 
> and copy down.
> 
> Hope this helps.
> 
> Pete
> 
> On Oct 14, 4:26 pm, Celticshadow
> <Celticsha...@discussions.microsoft.com> wrote:
> > Hi
> >
> > I have in column T certain numbers and texts that that I require to assign a
> > value to as below, in the adjacent column. Again any pointers would be much
> > appreciated.
> >
> > Kind Regards
> >
> > Celticshadow
> >
> > T   U  
> >
> > 1   1
> > 2   2
> > 3   3
> > 4   4
> > 5   5
> > 6   6
> > 7   7
> > 8   8
> > 9   9
> > 0   10
> > F   10
> > UR 10
> > U   10
> > R   10
> > S   10
> > L   10
> > P   10
> > PU  10
> > BD  10
> > D   10
> 
> 
0
10/14/2008 7:28:06 PM
Well, it should have worked if you set it up like this:

 T     U     V     W     X     Y     Z
 x      v      ?      ?      ?     '1     1
                                      '2      2
                                      '3      3
                                      '4      4
                                      '5      5
                                      '6      6
                                      '7      7
                                      '8      8
                                      '9      9
                                      '0     10
                                      F     10
                                     UR    10
                                      U     10
                                      R     10
                                      S     10
                                       L     10
                                       P     10
                                      PU    10
                                      BD    10
                                       D     10

where x is a value that you put in T1, v is the vlookup formula I gave
you, and ? means anything. Notice that the numbers in column Y are
preceded by an apostrophe - this will turn them into text and will not
be displayed in the cell. You should format column T as General. You
can improve on the formula in U1 with this:

=3DIF(ISNA(VLOOKUP(T1&"",Y$1,Z$20,2,0)),"",VLOOKUP(T1&"",Y$1,Z$20,2,0))

Just copy this down column U for as many values as you have (or
expect) in column T.

But if you don't want to have a table in Y1:Z20, you could do it like
this in U1:

=3DIF(T1=3D"","",IF(ISNUMBER(T1),IF(T1>9,"",IF(T1=3D0,10,T1)),IF(ISNA(MATCH=
(T1,
{"F","UR","U","R","S","L","P","PU","BD","D"},0)),"",10)))

then copy this down. This (and the other formula) will give a blank
cell if T1 is not one of the acceptable values in your list.

Hope this helps.

Pete

On Oct 14, 8:28=A0pm, Celticshadow
<Celticsha...@discussions.microsoft.com> wrote:
> Hi Pete
>
> Unfortunately that does not seem to work I either get a n/a or value erro=
r.
> It may be because I have not explained the situation very well. Column T =
row
> 1 downwards could contain any one of the below texts or numbers. Thus if
> column T row 1 contained the figure 0 then I would like to be able to ass=
ign
> the value 10 and if column T row 2 contained the letter u then again I wo=
uld
> like to assign the value 10.
>
> Column T
> Row 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> Contains
> one of the =A0 =A0 Value to
> below =A0 =A0 =A0 =A0 =A0 =A0Assign
> 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01
> 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02
> 3 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03
> 4 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A04
> 5 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A05
> 6 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A06
> 7 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A07
> 8 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A08
> 9 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A09
> 0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> F =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> UR =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> U =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> R =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> S =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> L =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> P =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> PU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> BD =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> D =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
>
> Kind Regards
>
> Celticshadow
>
>
>
> "Pete_UK" wrote:
> > Well, imagine that two-column table occupies cells Y1:Z20. Put this
> > formula in U1:
>
> > =3DVLOOKUP(T1,Y$1,Z$20,2,0)
>
> > and copy down.
>
> > Hope this helps.
>
> > Pete
>
> > On Oct 14, 4:26 pm, Celticshadow
> > <Celticsha...@discussions.microsoft.com> wrote:
> > > Hi
>
> > > I have in column T certain numbers and texts that that I require to a=
ssign a
> > > value to as below, in the adjacent column. Again any pointers would b=
e much
> > > appreciated.
>
> > > Kind Regards
>
> > > Celticshadow
>
> > > T =A0 U =A0
>
> > > 1 =A0 1
> > > 2 =A0 2
> > > 3 =A0 3
> > > 4 =A0 4
> > > 5 =A0 5
> > > 6 =A0 6
> > > 7 =A0 7
> > > 8 =A0 8
> > > 9 =A0 9
> > > 0 =A0 10
> > > F =A0 10
> > > UR 10
> > > U =A0 10
> > > R =A0 10
> > > S =A0 10
> > > L =A0 10
> > > P =A0 10
> > > PU =A010
> > > BD =A010
> > > D =A0 10- Hide quoted text -
>
> - Show quoted text -

0
pashurst (2576)
10/14/2008 11:27:20 PM
Sorry, I just noticed I had put a comma instead of a colon in the
VLOOKUP formula (and earlier) - it should be this:

=3DIF(ISNA(VLOOKUP(T1&"",Y$1:Z$20,2,0)),"",VLOOKUP(T1&"",Y$1:Z$20,2,0))

Hope this helps.

Pete

On Oct 15, 12:27=A0am, Pete_UK <pashu...@auditel.net> wrote:
> Well, it should have worked if you set it up like this:
>
> =A0T =A0 =A0 U =A0 =A0 V =A0 =A0 W =A0 =A0 X =A0 =A0 Y =A0 =A0 Z
> =A0x =A0 =A0 =A0v =A0 =A0 =A0? =A0 =A0 =A0? =A0 =A0 =A0? =A0 =A0 '1 =A0 =
=A0 1
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 '2 =A0 =A0 =A02
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 '3 =A0 =A0 =A03
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 '4 =A0 =A0 =A04
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 '5 =A0 =A0 =A05
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 '6 =A0 =A0 =A06
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 '7 =A0 =A0 =A07
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 '8 =A0 =A0 =A08
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 '9 =A0 =A0 =A09
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 '0 =A0 =A0 10
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 F =A0 =A0 10
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0UR =A0 =A010
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 U =A0 =A0 10
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 R =A0 =A0 10
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 S =A0 =A0 10
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0L =A0 =A0 10
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0P =A0 =A0 10
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 PU =A0 =A010
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 BD =A0 =A010
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0D =A0 =A0 10
>
> where x is a value that you put in T1, v is the vlookup formula I gave
> you, and ? means anything. Notice that the numbers in column Y are
> preceded by an apostrophe - this will turn them into text and will not
> be displayed in the cell. You should format column T as General. You
> can improve on the formula in U1 with this:
>
> =3DIF(ISNA(VLOOKUP(T1&"",Y$1,Z$20,2,0)),"",VLOOKUP(T1&"",Y$1,Z$20,2,0))
>
> Just copy this down column U for as many values as you have (or
> expect) in column T.
>
> But if you don't want to have a table in Y1:Z20, you could do it like
> this in U1:
>
> =3DIF(T1=3D"","",IF(ISNUMBER(T1),IF(T1>9,"",IF(T1=3D0,10,T1)),IF(ISNA(MAT=
CH(T1,
> {"F","UR","U","R","S","L","P","PU","BD","D"},0)),"",10)))
>
> then copy this down. This (and the other formula) will give a blank
> cell if T1 is not one of the acceptable values in your list.
>
> Hope this helps.
>
> Pete
>
> On Oct 14, 8:28=A0pm, Celticshadow
>
>
>
> <Celticsha...@discussions.microsoft.com> wrote:
> > Hi Pete
>
> > Unfortunately that does not seem to work I either get a n/a or value er=
ror.
> > It may be because I have not explained the situation very well. Column =
T row
> > 1 downwards could contain any one of the below texts or numbers. Thus i=
f
> > column T row 1 contained the figure 0 then I would like to be able to a=
ssign
> > the value 10 and if column T row 2 contained the letter u then again I =
would
> > like to assign the value 10.
>
> > Column T
> > Row 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> > Contains
> > one of the =A0 =A0 Value to
> > below =A0 =A0 =A0 =A0 =A0 =A0Assign
> > 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01
> > 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02
> > 3 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03
> > 4 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A04
> > 5 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A05
> > 6 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A06
> > 7 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A07
> > 8 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A08
> > 9 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A09
> > 0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> > F =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> > UR =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> > U =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> > R =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> > S =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> > L =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> > P =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> > PU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> > BD =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> > D =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
>
> > Kind Regards
>
> > Celticshadow
>
> > "Pete_UK" wrote:
> > > Well, imagine that two-column table occupies cells Y1:Z20. Put this
> > > formula in U1:
>
> > > =3DVLOOKUP(T1,Y$1,Z$20,2,0)
>
> > > and copy down.
>
> > > Hope this helps.
>
> > > Pete
>
> > > On Oct 14, 4:26 pm, Celticshadow
> > > <Celticsha...@discussions.microsoft.com> wrote:
> > > > Hi
>
> > > > I have in column T certain numbers and texts that that I require to=
 assign a
> > > > value to as below, in the adjacent column. Again any pointers would=
 be much
> > > > appreciated.
>
> > > > Kind Regards
>
> > > > Celticshadow
>
> > > > T =A0 U =A0
>
> > > > 1 =A0 1
> > > > 2 =A0 2
> > > > 3 =A0 3
> > > > 4 =A0 4
> > > > 5 =A0 5
> > > > 6 =A0 6
> > > > 7 =A0 7
> > > > 8 =A0 8
> > > > 9 =A0 9
> > > > 0 =A0 10
> > > > F =A0 10
> > > > UR 10
> > > > U =A0 10
> > > > R =A0 10
> > > > S =A0 10
> > > > L =A0 10
> > > > P =A0 10
> > > > PU =A010
> > > > BD =A010
> > > > D =A0 10- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

0
pashurst (2576)
10/14/2008 11:35:23 PM
Hi Pete

I cant access my home PC until later on this evening so I shall let you know 
later how I got on, just thought I should mention incase you thought I was 
being ignorant. Many Thanks

Kind Regards

Celticshadow

"Pete_UK" wrote:

> Sorry, I just noticed I had put a comma instead of a colon in the
> VLOOKUP formula (and earlier) - it should be this:
> 
> =IF(ISNA(VLOOKUP(T1&"",Y$1:Z$20,2,0)),"",VLOOKUP(T1&"",Y$1:Z$20,2,0))
> 
> Hope this helps.
> 
> Pete
> 
> On Oct 15, 12:27 am, Pete_UK <pashu...@auditel.net> wrote:
> > Well, it should have worked if you set it up like this:
> >
> >  T     U     V     W     X     Y     Z
> >  x      v      ?      ?      ?     '1     1
> >                                       '2      2
> >                                       '3      3
> >                                       '4      4
> >                                       '5      5
> >                                       '6      6
> >                                       '7      7
> >                                       '8      8
> >                                       '9      9
> >                                       '0     10
> >                                       F     10
> >                                      UR    10
> >                                       U     10
> >                                       R     10
> >                                       S     10
> >                                        L     10
> >                                        P     10
> >                                       PU    10
> >                                       BD    10
> >                                        D     10
> >
> > where x is a value that you put in T1, v is the vlookup formula I gave
> > you, and ? means anything. Notice that the numbers in column Y are
> > preceded by an apostrophe - this will turn them into text and will not
> > be displayed in the cell. You should format column T as General. You
> > can improve on the formula in U1 with this:
> >
> > =IF(ISNA(VLOOKUP(T1&"",Y$1,Z$20,2,0)),"",VLOOKUP(T1&"",Y$1,Z$20,2,0))
> >
> > Just copy this down column U for as many values as you have (or
> > expect) in column T.
> >
> > But if you don't want to have a table in Y1:Z20, you could do it like
> > this in U1:
> >
> > =IF(T1="","",IF(ISNUMBER(T1),IF(T1>9,"",IF(T1=0,10,T1)),IF(ISNA(MATCH(T1,
> > {"F","UR","U","R","S","L","P","PU","BD","D"},0)),"",10)))
> >
> > then copy this down. This (and the other formula) will give a blank
> > cell if T1 is not one of the acceptable values in your list.
> >
> > Hope this helps.
> >
> > Pete
> >
> > On Oct 14, 8:28 pm, Celticshadow
> >
> >
> >
> > <Celticsha...@discussions.microsoft.com> wrote:
> > > Hi Pete
> >
> > > Unfortunately that does not seem to work I either get a n/a or value error.
> > > It may be because I have not explained the situation very well. Column T row
> > > 1 downwards could contain any one of the below texts or numbers. Thus if
> > > column T row 1 contained the figure 0 then I would like to be able to assign
> > > the value 10 and if column T row 2 contained the letter u then again I would
> > > like to assign the value 10.
> >
> > > Column T
> > > Row 1              
> > > Contains
> > > one of the     Value to
> > > below            Assign
> > > 1                      1
> > > 2                      2
> > > 3                      3
> > > 4                      4
> > > 5                      5
> > > 6                      6
> > > 7                      7
> > > 8                      8
> > > 9                      9
> > > 0                     10
> > > F                     10
> > > UR                   10
> > > U                     10
> > > R                    10
> > > S                    10
> > > L                    10
> > > P                    10
> > > PU                  10
> > > BD                 10
> > > D                   10
> >
> > > Kind Regards
> >
> > > Celticshadow
> >
> > > "Pete_UK" wrote:
> > > > Well, imagine that two-column table occupies cells Y1:Z20. Put this
> > > > formula in U1:
> >
> > > > =VLOOKUP(T1,Y$1,Z$20,2,0)
> >
> > > > and copy down.
> >
> > > > Hope this helps.
> >
> > > > Pete
> >
> > > > On Oct 14, 4:26 pm, Celticshadow
> > > > <Celticsha...@discussions.microsoft.com> wrote:
> > > > > Hi
> >
> > > > > I have in column T certain numbers and texts that that I require to assign a
> > > > > value to as below, in the adjacent column. Again any pointers would be much
> > > > > appreciated.
> >
> > > > > Kind Regards
> >
> > > > > Celticshadow
> >
> > > > > T   U  
> >
> > > > > 1   1
> > > > > 2   2
> > > > > 3   3
> > > > > 4   4
> > > > > 5   5
> > > > > 6   6
> > > > > 7   7
> > > > > 8   8
> > > > > 9   9
> > > > > 0   10
> > > > > F   10
> > > > > UR 10
> > > > > U   10
> > > > > R   10
> > > > > S   10
> > > > > L   10
> > > > > P   10
> > > > > PU  10
> > > > > BD  10
> > > > > D   10- Hide quoted text -
> >
> > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
> 
> 
0
10/15/2008 7:18:05 AM
That's alright, and I shall be away from my PC from lunchtime and all
day tomorrow, so I won't be able to get back to you until Friday
anyway.

Pete

On Oct 15, 8:18=A0am, Celticshadow
<Celticsha...@discussions.microsoft.com> wrote:
> Hi Pete
>
> I cant access my home PC until later on this evening so I shall let you k=
now
> later how I got on, just thought I should mention incase you thought I wa=
s
> being ignorant. Many Thanks
>
> Kind Regards
>
> Celticshadow
>
>
>
> "Pete_UK" wrote:
> > Sorry, I just noticed I had put a comma instead of a colon in the
> > VLOOKUP formula (and earlier) - it should be this:
>
> > =3DIF(ISNA(VLOOKUP(T1&"",Y$1:Z$20,2,0)),"",VLOOKUP(T1&"",Y$1:Z$20,2,0))
>
> > Hope this helps.
>
> > Pete
>
> > On Oct 15, 12:27 am, Pete_UK <pashu...@auditel.net> wrote:
> > > Well, it should have worked if you set it up like this:
>
> > > =A0T =A0 =A0 U =A0 =A0 V =A0 =A0 W =A0 =A0 X =A0 =A0 Y =A0 =A0 Z
> > > =A0x =A0 =A0 =A0v =A0 =A0 =A0? =A0 =A0 =A0? =A0 =A0 =A0? =A0 =A0 '1 =
=A0 =A0 1
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 '2 =A0 =A0 =A02
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 '3 =A0 =A0 =A03
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 '4 =A0 =A0 =A04
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 '5 =A0 =A0 =A05
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 '6 =A0 =A0 =A06
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 '7 =A0 =A0 =A07
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 '8 =A0 =A0 =A08
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 '9 =A0 =A0 =A09
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 '0 =A0 =A0 10
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 F =A0 =A0 10
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0UR =A0 =A010
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 U =A0 =A0 10
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 R =A0 =A0 10
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 S =A0 =A0 10
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0L =A0 =A0 10
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0P =A0 =A0 10
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 PU =A0 =A010
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 BD =A0 =A010
> > > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0 =A0 =A0D =A0 =A0 10
>
> > > where x is a value that you put in T1, v is the vlookup formula I gav=
e
> > > you, and ? means anything. Notice that the numbers in column Y are
> > > preceded by an apostrophe - this will turn them into text and will no=
t
> > > be displayed in the cell. You should format column T as General. You
> > > can improve on the formula in U1 with this:
>
> > > =3DIF(ISNA(VLOOKUP(T1&"",Y$1,Z$20,2,0)),"",VLOOKUP(T1&"",Y$1,Z$20,2,0=
))
>
> > > Just copy this down column U for as many values as you have (or
> > > expect) in column T.
>
> > > But if you don't want to have a table in Y1:Z20, you could do it like
> > > this in U1:
>
> > > =3DIF(T1=3D"","",IF(ISNUMBER(T1),IF(T1>9,"",IF(T1=3D0,10,T1)),IF(ISNA=
(MATCH(T1,
> > > {"F","UR","U","R","S","L","P","PU","BD","D"},0)),"",10)))
>
> > > then copy this down. This (and the other formula) will give a blank
> > > cell if T1 is not one of the acceptable values in your list.
>
> > > Hope this helps.
>
> > > Pete
>
> > > On Oct 14, 8:28 pm, Celticshadow
>
> > > <Celticsha...@discussions.microsoft.com> wrote:
> > > > Hi Pete
>
> > > > Unfortunately that does not seem to work I either get a n/a or valu=
e error.
> > > > It may be because I have not explained the situation very well. Col=
umn T row
> > > > 1 downwards could contain any one of the below texts or numbers. Th=
us if
> > > > column T row 1 contained the figure 0 then I would like to be able =
to assign
> > > > the value 10 and if column T row 2 contained the letter u then agai=
n I would
> > > > like to assign the value 10.
>
> > > > Column T
> > > > Row 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> > > > Contains
> > > > one of the =A0 =A0 Value to
> > > > below =A0 =A0 =A0 =A0 =A0 =A0Assign
> > > > 1 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A01
> > > > 2 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A02
> > > > 3 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A03
> > > > 4 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A04
> > > > 5 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A05
> > > > 6 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A06
> > > > 7 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A07
> > > > 8 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A08
> > > > 9 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A09
> > > > 0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> > > > F =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> > > > UR =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> > > > U =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> > > > R =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> > > > S =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> > > > L =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> > > > P =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> > > > PU =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A010
> > > > BD =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
> > > > D =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 10
>
> > > > Kind Regards
>
> > > > Celticshadow
>
> > > > "Pete_UK" wrote:
> > > > > Well, imagine that two-column table occupies cells Y1:Z20. Put th=
is
> > > > > formula in U1:
>
> > > > > =3DVLOOKUP(T1,Y$1,Z$20,2,0)
>
> > > > > and copy down.
>
> > > > > Hope this helps.
>
> > > > > Pete
>
> > > > > On Oct 14, 4:26 pm, Celticshadow
> > > > > <Celticsha...@discussions.microsoft.com> wrote:
> > > > > > Hi
>
> > > > > > I have in column T certain numbers and texts that that I requir=
e to assign a
> > > > > > value to as below, in the adjacent column. Again any pointers w=
ould be much
> > > > > > appreciated.
>
> > > > > > Kind Regards
>
> > > > > > Celticshadow
>
> > > > > > T =A0 U =A0
>
> > > > > > 1 =A0 1
> > > > > > 2 =A0 2
> > > > > > 3 =A0 3
> > > > > > 4 =A0 4
> > > > > > 5 =A0 5
> > > > > > 6 =A0 6
> > > > > > 7 =A0 7
> > > > > > 8 =A0 8
> > > > > > 9 =A0 9
> > > > > > 0 =A0 10
> > > > > > F =A0 10
> > > > > > UR 10
> > > > > > U =A0 10
> > > > > > R =A0 10
> > > > > > S =A0 10
> > > > > > L =A0 10
> > > > > > P =A0 10
> > > > > > PU =A010
> > > > > > BD =A010
> > > > > > D =A0 10- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

0
pashurst (2576)
10/15/2008 8:28:09 AM
Hi Pete

That works an absolute treat, delighted with the result. Thanks very much 
indeed. Just about to post another question. It is quite addictive this 
little knowledge base.

Thanks again

Kind Regards

Celticshadow

"Pete_UK" wrote:

> That's alright, and I shall be away from my PC from lunchtime and all
> day tomorrow, so I won't be able to get back to you until Friday
> anyway.
> 
> Pete
> 
> On Oct 15, 8:18 am, Celticshadow
> <Celticsha...@discussions.microsoft.com> wrote:
> > Hi Pete
> >
> > I cant access my home PC until later on this evening so I shall let you know
> > later how I got on, just thought I should mention incase you thought I was
> > being ignorant. Many Thanks
> >
> > Kind Regards
> >
> > Celticshadow
> >
> >
> >
> > "Pete_UK" wrote:
> > > Sorry, I just noticed I had put a comma instead of a colon in the
> > > VLOOKUP formula (and earlier) - it should be this:
> >
> > > =IF(ISNA(VLOOKUP(T1&"",Y$1:Z$20,2,0)),"",VLOOKUP(T1&"",Y$1:Z$20,2,0))
> >
> > > Hope this helps.
> >
> > > Pete
> >
> > > On Oct 15, 12:27 am, Pete_UK <pashu...@auditel.net> wrote:
> > > > Well, it should have worked if you set it up like this:
> >
> > > >  T     U     V     W     X     Y     Z
> > > >  x      v      ?      ?      ?     '1     1
> > > >                                       '2      2
> > > >                                       '3      3
> > > >                                       '4      4
> > > >                                       '5      5
> > > >                                       '6      6
> > > >                                       '7      7
> > > >                                       '8      8
> > > >                                       '9      9
> > > >                                       '0     10
> > > >                                       F     10
> > > >                                      UR    10
> > > >                                       U     10
> > > >                                       R     10
> > > >                                       S     10
> > > >                                        L     10
> > > >                                        P     10
> > > >                                       PU    10
> > > >                                       BD    10
> > > >                                        D     10
> >
> > > > where x is a value that you put in T1, v is the vlookup formula I gave
> > > > you, and ? means anything. Notice that the numbers in column Y are
> > > > preceded by an apostrophe - this will turn them into text and will not
> > > > be displayed in the cell. You should format column T as General. You
> > > > can improve on the formula in U1 with this:
> >
> > > > =IF(ISNA(VLOOKUP(T1&"",Y$1,Z$20,2,0)),"",VLOOKUP(T1&"",Y$1,Z$20,2,0))
> >
> > > > Just copy this down column U for as many values as you have (or
> > > > expect) in column T.
> >
> > > > But if you don't want to have a table in Y1:Z20, you could do it like
> > > > this in U1:
> >
> > > > =IF(T1="","",IF(ISNUMBER(T1),IF(T1>9,"",IF(T1=0,10,T1)),IF(ISNA(MATCH(T1,
> > > > {"F","UR","U","R","S","L","P","PU","BD","D"},0)),"",10)))
> >
> > > > then copy this down. This (and the other formula) will give a blank
> > > > cell if T1 is not one of the acceptable values in your list.
> >
> > > > Hope this helps.
> >
> > > > Pete
> >
> > > > On Oct 14, 8:28 pm, Celticshadow
> >
> > > > <Celticsha...@discussions.microsoft.com> wrote:
> > > > > Hi Pete
> >
> > > > > Unfortunately that does not seem to work I either get a n/a or value error.
> > > > > It may be because I have not explained the situation very well. Column T row
> > > > > 1 downwards could contain any one of the below texts or numbers. Thus if
> > > > > column T row 1 contained the figure 0 then I would like to be able to assign
> > > > > the value 10 and if column T row 2 contained the letter u then again I would
> > > > > like to assign the value 10.
> >
> > > > > Column T
> > > > > Row 1              
> > > > > Contains
> > > > > one of the     Value to
> > > > > below            Assign
> > > > > 1                      1
> > > > > 2                      2
> > > > > 3                      3
> > > > > 4                      4
> > > > > 5                      5
> > > > > 6                      6
> > > > > 7                      7
> > > > > 8                      8
> > > > > 9                      9
> > > > > 0                     10
> > > > > F                     10
> > > > > UR                   10
> > > > > U                     10
> > > > > R                    10
> > > > > S                    10
> > > > > L                    10
> > > > > P                    10
> > > > > PU                  10
> > > > > BD                 10
> > > > > D                   10
> >
> > > > > Kind Regards
> >
> > > > > Celticshadow
> >
> > > > > "Pete_UK" wrote:
> > > > > > Well, imagine that two-column table occupies cells Y1:Z20. Put this
> > > > > > formula in U1:
> >
> > > > > > =VLOOKUP(T1,Y$1,Z$20,2,0)
> >
> > > > > > and copy down.
> >
> > > > > > Hope this helps.
> >
> > > > > > Pete
> >
> > > > > > On Oct 14, 4:26 pm, Celticshadow
> > > > > > <Celticsha...@discussions.microsoft.com> wrote:
> > > > > > > Hi
> >
> > > > > > > I have in column T certain numbers and texts that that I require to assign a
> > > > > > > value to as below, in the adjacent column. Again any pointers would be much
> > > > > > > appreciated.
> >
> > > > > > > Kind Regards
> >
> > > > > > > Celticshadow
> >
> > > > > > > T   U  
> >
> > > > > > > 1   1
> > > > > > > 2   2
> > > > > > > 3   3
> > > > > > > 4   4
> > > > > > > 5   5
> > > > > > > 6   6
> > > > > > > 7   7
> > > > > > > 8   8
> > > > > > > 9   9
> > > > > > > 0   10
> > > > > > > F   10
> > > > > > > UR 10
> > > > > > > U   10
> > > > > > > R   10
> > > > > > > S   10
> > > > > > > L   10
> > > > > > > P   10
> > > > > > > PU  10
> > > > > > > BD  10
> > > > > > > D   10- Hide quoted text -
> >
> > > > > - Show quoted text -- Hide quoted text -
> >
> > > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
> 
> 
0
10/16/2008 12:48:01 PM
You're welcome - thanks for the feedback.

Pete

On Oct 16, 1:48=A0pm, Celticshadow
<Celticsha...@discussions.microsoft.com> wrote:
> Hi Pete
>
> That works an absolute treat, delighted with the result. Thanks very much
> indeed. Just about to post another question. It is quite addictive this
> little knowledge base.
>
> Thanks again
>
> Kind Regards
>
> Celticshadow
>
0
pashurst (2576)
10/17/2008 9:01:41 AM
Reply:

Similar Artilces:

Find Next Row With No Value In It
I've got this nextrow = Cells.Find(what:="*", searchdirection:=xlPrevious, searchorder:=xlByRows).Row + 1 Cells(nextrow, 1).Select which will find nextrow with nothing in it and select cell in Col A, but now I've got formulas in the cells, so instead of finding the next row with nothing in it, I want to find the next row with no value - even if it's got a formula in it. Can I do this by amending what's in the what:="*" part of this code? It's always better to include all the parms for .find(). If you don't, then you're at ...

Need VB code to edit cell values
I need some VB to do the following please (My VB is not good enough yet) Starting from the active cell (call it the StartCell) In the cell to the left.. ThreeChars = Value.right(3) (the last three chars) Value = Value - last three characters (assume value is text & at least 3 chars present) In StartCell.. value = ThreeChars (as text, overwrite any contents of StartCell) move active cell one down from StartCell Examples of before/after -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Stephen Ford" <Stephen_Ford_no@spam_uwclub....

merging 3 spreadsheets into one based upon a single common data field
OK I hope you guys can help me out. Here is the scenario. I have 3 spreadsheets. The main spreadsheet I working with has 500 lines with a unique number string. The next two spreadsheets have about 12000 lines but also contain the same unique string as the first spreadsheet. I want to search the 2nd and 3rd spreadsheet using the first spreadsheets unique id number and then take the information from the 2nd and 3rd spreadsheet and then merge it into the first. Does that make since? Any help would be great. -- xchosen --------------------------------------------------------------------...

Export to Text Files Based on Field Value
I have an Access table with personal information (e.g., first name, last name, birth date, etc.) for thousands of people. The table is sorted by state. I want to be able to run a macro on that table that will create separate pipe delimited text files for each state with all fields included. Any help would be appreciated? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201005/1 Instead of exporting the table, export a query based on the table. You will have to have some code that loops through the states and modifies the quer...

moving large amounts of data from one db to another
I have a table that has millions of rows of data in it, which then references another table with millions of referenced (matching by foreign key constraints)... I need to move this data from database A to a new database B, is there any way to do this and preserve the identity columns and their referential integrity?) Thanks! You can't have integrated RI across databases so that part is out. But you can certainly insert and keep the identities. Take a look at SET IDENTITY INSERT in BOL. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Smokey Grindel"...

matrix and sorting with running value
I have a matrix which has 1 row grouping and 1 column grouping. The column is a date. The matrix has 1 field which is doing a running sum. I want the date column sorted in descedning order. I changed the sort of the column group and the dates do appear to sort in desc order. However the runningsum still appears to sort as if the date column is in asc order. ...

Expand/Contract grouped columns in protected sheet
I�m trying to protect a sheet that has a pivottable with grouped columns. The problem is , after protecting the sheet , I can�t expand/contract the table. I get an error saying that I can�t use that command because it�s a protected sheet. Is there anyway around this? I.E. How can I protect a sheet, and still expand/contract a pivottable? --- Message posted from http://www.ExcelForum.com/ ...

Help with an expression to calculate a profit from one of three fi
Using Access 2003 SP3 - in Forms I know I'm probably asking a very complex question, so a very big THANK YOU to whomever can help. I am doing a detailed inventory and need help with an expression in my form that will calculate my profit based on one of three fields. Only one field would have the end data. To clarify what I am working with: Most stock items come by the "unit" and contain multiple "subunits" which, in turn, contain smaller "pieces." However, some "units" only come with "subunits" and some only come as &quo...

Restoring Public folders to another server
We have a server that has crashed and AD is all up the wop. It was our exchange server (Exchange 2000). We have a new server in place but we can't restore our public folders from backup (Veritas 9.1). Is there a way to restore public folders to a different server or do we have to rebuild the server with all the same names etc.....? In advance thankyou for the help. -- Network Consultant a full recovery server is your only option, unless you go with a 3rd party tool... "pete" <pete@discussions.microsoft.com> wrote in message news:0DCA3555-EA66-4BCC-AB7C-59896AB02...

how do you formula's but leave the value.
hi,how do you formula's but leave the value. if a1=1 and b1=2 and you have a code in c1 that says =a1+b1 c1 value would = 3 but how do you then delete the formula so that is stays as 3. i hope you understand what i mean. Hi Craig copy (don't cut) C1 and then click on C1 again and choose Edit / Paste Special now choose values and click Ok this should give you what you want Regards julie "craig" <anonymous@discussions.microsoft.com> wrote in message news:386401c4013b$71f004b0$a601280a@phx.gbl... > hi,how do you formula's but leave the value. > if a1=1 ...

Can I calculate a field with 2 different filter criteria in one q
Hi, I have a data source table with a customer name field and various metrics fields, I'd like to sum metric "a" based on customer name being like "abc" and another sum of metric "a" where the customer name is NOT LIKE "abc" in one query, but I can't seem to figure this out, but, I'm a novice at this. Best I can figure is 2 different queries, then a 3rd query to bring the two together. Thanks -- Jim jimd wrote: >Hi, I have a data source table with a customer name field and various metrics >fields, I'd like to sum metric &...

how can I split one cell's data into two cells?
I am using Excel as a database, and have a column with cells with a person's name (text) in it...e.g., John Smith. NOW, after the fact, I wish to have a column with just John in it, and another column with just Smith...but I have NO idea how to do this! Help would be appreciated. Thank you. Please refrain from multiposting, you have answers in one of the 4! newsgroups you posted the same question in. Multiposting is frowned upon and even the MS web interface states so -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It ...

Using VBA to find a value and select a range
I am trying to write a macro that will let me find a specific vale in a specific column, then select part of the row that the value is in, beginnign with a column 31 cells away from the column that the specific value is in. Someone suggested to me that I use an IF statement, but this is all I know about that: IF [value in column B] is “Grand Total” Then select from column AF to the end of the array in that row and copy it and paste a transpose of the values starting in cell I9 of worksheet X. What is the best way for me to accomplish this goal? Does this macro do what you...

To mark today's column with a special color or sign in work week view
In Microsoft Outlook, I use work week view, displaying me five columns, each for a single work day. I'd like to see today's column marked with a special color or sign. It there a way to do it? Thanks. Dan ...

Merging three publisher documents into one
How do I merge three Publisher documents into one master document? All three are in the same format. Copy and paste. -- JoAnn Paules MVP Microsoft [Publisher] "jo" <jo@discussions.microsoft.com> wrote in message news:91582EF0-7004-4BF3-9F40-1B82AE85C1BC@microsoft.com... > How do I merge three Publisher documents into one master document? All > three > are in the same format. ...

Extract Information To Another Worksheet
Hi All, I have a worksheet with Colonm Headings and Data below each heading, except the "Column A" (in some cases it has and in some cases it doesnt). I want to extract all rows where there is data in "Column A" to a separate worksheet. The Entire Row should be copied to the new Worksheet once i enter a value in Column A for each record and the new sheet should be frequently updated from the original list, its like a building list. -- M Imran Buhary In a separate, helper, column in row 2 enter: =--ISBLANK(A2) and copy down Then switch on Autofilter and set this n...

columns are numbered????
My columns are numbered not lettered, Whats up with that? The rows are numbered as well. Dan, Tools/Options/General Uncheck R1C1 Reference style John "Dan Lindell" <dlind@xtreme.net> wrote in message news:402C4A58.4000708@xtreme.net... > My columns are numbered not lettered, Whats up with that? > The rows are numbered as well. > ...

True or False if already in column
I have a list of unique phone numbers in column B. I want to enter a phone number in cell C2 with an if statement in cell D2 that states "Already in List" or "Not in List" Any suggestions? Thanks Tim Lookup() "Tim" <timh2ofall@comcast.net> wrote in message news:1129677983.692566.201670@g14g2000cwa.googlegroups.com... >I have a list of unique phone numbers in column B. I want to enter a > phone number in cell C2 with an if statement in cell D2 that states > "Already in List" or "Not in List" > Any suggestions? > Thanks...

Multiple records attached to one or many accounts
I am attempting to create an entity called Trading Partner that can be assigned to an account. Basically I would like to globally create these Trading Partners then assign multiple trading partners to every account. Is this possible with a many to one & one to many relationship? How would I go about setting that up? Try the Account Relationships to another Account, label the relationship Role "Trading Partner" Frank Lee Workopia, Inc. http://www.workopia.com/Links.htm "jojackovin@ontuet.com" wrote: > I am attempting to create an entity called Trading Partn...

set value in subform
I want to use the set value macro to set a control in a subform based on a control in another form. I have three forms: FormA, FormB, FormC. FormC is a subform within FormB. What I want is to be able to enter a value in FormA and then have FormC automatically updated. Currently, if I open FormA and FormC I am able to use the SetValue macro to do this. However, if I open FormA and FormB (recall that FormC is a subform within FormB) and try to run the macro, Access gives me an error. I'm assuming that it is because Access does not think FormC is open (even though it is op...

Forcing borders of all columns when records less than max
Is there a way to force the 28 fields borders if there are less than 28 fields (28 columns is forced but borders do not show up when less than 28 records)? Can you explain what you are attempting to do? It isn't clear whether you mean 28 fields or 28 records or both. You can use the Line method to draw rectangles anywhere in your report. -- Duane Hookom Microsoft Access MVP "Michael" wrote: > Is there a way to force the 28 fields borders if there are less than 28 > fields (28 columns is forced but borders do not show up when less than 28 > records)? > ...

Data table vs Axis value abbreviations
Windows 2000 Office 2000 I have a chart with the data table showing. The values go into the millions. On the axis, I'd like to use e.g. $1M instead of $1,000,000. Is there a way that I can maintain the actual value in the data table, while abbreviating the axis value? Thanks in advance for your help Rita Palazzi Senior Engineer/ FedEx Express Never mind. Someone showed me the "Display Units" on the axis format menu and I was able to do what I needed. Hope everyone has a GREAT Thanksgiving! Rita Palazzi wrote: > Windows 2000 > Office 2000 > > > I hav...

mail to any one with .org extension
When mail is sent to anyone with a .org extension from Outlook 2003.The mail goes to sent items but the reciepient never gets it.It works fine in outlook express. Please Help! Thanks in advance! Junaid ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ...

Why broken column-lines on my report?
Could someone please help me tidy up my report? I've modified it and now all the virtical lines are broken at the top where it should intersect the horizontal line to form a cell. That's happened on all of them even though I've only changed a couple of columns. I need to even up the columns sizes as well but the broken lines are bothering me the most. ...

two charts on one chart page
Hi, I have made multiple line charts and have placed them each in their own chart sheet. I am trying to figure out if it is possible to have 2 or even 4 charts posted in one chart page. The only way I see to do this may be to copy and paste and resize the charts or possibly to paste them in the worksheet. I ultimately just need to be able to print out 2 or 4 charts on a page. thanks kd a �crit : > Hi, > > I have made multiple line charts and have placed them each in their own > chart sheet. I am trying to figure out if it is possible to have 2 or even 4 > charts p...