if...

[phil2006]

I need cells to automatically update when other cells have text inserted. I have some data on sheet 2 in columns 1 and 2. On sheet 1 when the text that matches the text on sheet 2 is entered I want the corresponding text from sheet 2 column two to be inserted. Some of my data is prefixed with '. Thanks for any help!

[Toppers]

In cells in Sheet1:

=IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)),"",(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)))

Copy down as far as required. Cells remain blank until text is entered OR no
match is found.

Adjust ranges on Sheet2 to suit.

HTH

"phil2006" wrote:

>
> I need cells to automatically update when other cells have text
> inserted. I have some data on sheet 2 in columns 1 and 2. On sheet 1
> when the text that matches the text on sheet 2 is entered I want the
> corresponding text from sheet 2 column two to be inserted. Some of my
> data is prefixed with '. Thanks for any help!
>
>
> --
> phil2006
> ------------------------------------------------------------------------
> phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
> View this thread: http://www.excelforum.com/showthread...hreadid=559849
>
>

[Naveen]

Dear Phil,

u can use lookup function to do this. This is simplest way to do.

Eg: insert this formula in sheet1, column 2.
=LOOKUP(A1,Sheet2!a:a,Sheet2!b:b)
A1- Data on Sheet1.
sheet2!a:a- where the reference data on sheet1 should match with the data in
sheet2!(column1)
sheet2!b:b- is the corresponding data to the column1 in sheet2.

Hope i am clear.

Naveen

"phil2006" wrote:

>
> I need cells to automatically update when other cells have text
> inserted. I have some data on sheet 2 in columns 1 and 2. On sheet 1
> when the text that matches the text on sheet 2 is entered I want the
> corresponding text from sheet 2 column two to be inserted. Some of my
> data is prefixed with '. Thanks for any help!
>
>
> --
> phil2006
> ------------------------------------------------------------------------
> phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
> View this thread: http://www.excelforum.com/showthread...hreadid=559849
>
>

[Naveen]

just to remind u if Vlookup is used the data should be in ascending order.

"Toppers" wrote:

> In cells in Sheet1:
>
> =IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)),"",(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)))
>
> Copy down as far as required. Cells remain blank until text is entered OR no
> match is found.
>
> Adjust ranges on Sheet2 to suit.
>
> HTH
>
> "phil2006" wrote:
>
> >
> > I need cells to automatically update when other cells have text
> > inserted. I have some data on sheet 2 in columns 1 and 2. On sheet 1
> > when the text that matches the text on sheet 2 is entered I want the
> > corresponding text from sheet 2 column two to be inserted. Some of my
> > data is prefixed with '. Thanks for any help!
> >
> >
> > --
> > phil2006
> > ------------------------------------------------------------------------
> > phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
> > View this thread: http://www.excelforum.com/showthread...hreadid=559849
> >
> >

[Naveen]

just to remind u if Vlookup is used the data should be in ascending order.

"Toppers" wrote:

> In cells in Sheet1:
>
> =IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)),"",(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)))
>
> Copy down as far as required. Cells remain blank until text is entered OR no
> match is found.
>
> Adjust ranges on Sheet2 to suit.
>
> HTH
>
> "phil2006" wrote:
>
> >
> > I need cells to automatically update when other cells have text
> > inserted. I have some data on sheet 2 in columns 1 and 2. On sheet 1
> > when the text that matches the text on sheet 2 is entered I want the
> > corresponding text from sheet 2 column two to be inserted. Some of my
> > data is prefixed with '. Thanks for any help!
> >
> >
> > --
> > phil2006
> > ------------------------------------------------------------------------
> > phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
> > View this thread: http://www.excelforum.com/showthread...hreadid=559849
> >
> >

[phil2006]

Thanks, that's just what I needed! How would I write this as a macro that could be used in a number of sheets and would only be inserted into the cells if there was something inserted in the data column. i.e. if the initial column is empty the corresponding cell remains empty.

Thanks very much.

[Dave Peterson]

Not always.

Toppers looks for an exact match (that 4th argument is 0 or false).

Naveen wrote:
>
> just to remind u if Vlookup is used the data should be in ascending order.
>
> "Toppers" wrote:
>
> > In cells in Sheet1:
> >
> > =IF(ISERROR(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)),"",(VLOOKUP(A1,Sheet2!$A$1:$B$100,2,0)))
> >
> > Copy down as far as required. Cells remain blank until text is entered OR no
> > match is found.
> >
> > Adjust ranges on Sheet2 to suit.
> >
> > HTH
> >
> > "phil2006" wrote:
> >
> > >
> > > I need cells to automatically update when other cells have text
> > > inserted. I have some data on sheet 2 in columns 1 and 2. On sheet 1
> > > when the text that matches the text on sheet 2 is entered I want the
> > > corresponding text from sheet 2 column two to be inserted. Some of my
> > > data is prefixed with '. Thanks for any help!
> > >
> > >
> > > --
> > > phil2006
> > > ------------------------------------------------------------------------
> > > phil2006's Profile: http://www.excelforum.com/member.php...o&userid=35092
> > > View this thread: http://www.excelforum.com/showthread...hreadid=559849
> > >
> > >

--

Dave Peterson