(Msg. 1) Posted: Wed Sep 30, 2009 9:45 am
Post subject: Help! Forgot formula for auto copy of cell value... Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
Hi...thanks in advance for your help.
Let's say I have two tabs. The formula is going in a cell B1 in tab1.
In Tab 2, col A contains names, col B contains address, col C contains
title...etc.
I would like to manually enter one of the names in Tab 1, cell A1, and have
cell B1 automatically copy the corresponding data from say, Col B in Tab 2.
No calculations, just straight copy.
(logic would be "if the value I entered in Tab1 A1 matches any of the names
in Tab2 Col A, then copy the person's address here).
I've used it before...and it's straightforward...but this old brain just
didn't hang on to it. This was not a code or macro thing...just a straight
formula you write in a cell.
(Msg. 2) Posted: Wed Sep 30, 2009 9:51 am
Post subject: RE: Help! Forgot formula for auto copy of cell value... [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
=VLOOKUP(A1,Tab2!A:B,2,0) would do it.
=IF(A1="","",VLOOKUP(A1,Tab2!A:B,2,0)) would show a blank cell until you
enter a value in A1.
"Stilla" wrote:
> Hi...thanks in advance for your help.
> Let's say I have two tabs. The formula is going in a cell B1 in tab1.
>
> In Tab 2, col A contains names, col B contains address, col C contains
> title...etc.
>
> I would like to manually enter one of the names in Tab 1, cell A1, and have
> cell B1 automatically copy the corresponding data from say, Col B in Tab 2.
> No calculations, just straight copy.
>
> (logic would be "if the value I entered in Tab1 A1 matches any of the names
> in Tab2 Col A, then copy the person's address here).
>
> I've used it before...and it's straightforward...but this old brain just
> didn't hang on to it. This was not a code or macro thing...just a straight
> formula you write in a cell.
>
> THANKS EVER SO MUCH!!
(Msg. 3) Posted: Wed Sep 30, 2009 10:07 am
Post subject: RE: Help! Forgot formula for auto copy of cell value... [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Oh thank you for reminding me of Vlookup!!! For this the reference data
MUST be in the first column, right?
Is there any other formula for using any other column but the first as a
reference? Say, if a value in Col Z in Tab 2 is "Mr. Jones", then copy his
address from Col B in Tab2)
Thanks so much!
"Sean Timmons" wrote:
> =VLOOKUP(A1,Tab2!A:B,2,0) would do it.
>
> =IF(A1="","",VLOOKUP(A1,Tab2!A:B,2,0)) would show a blank cell until you
> enter a value in A1.
>
> "Stilla" wrote:
>
> > Hi...thanks in advance for your help.
> > Let's say I have two tabs. The formula is going in a cell B1 in tab1.
> >
> > In Tab 2, col A contains names, col B contains address, col C contains
> > title...etc.
> >
> > I would like to manually enter one of the names in Tab 1, cell A1, and have
> > cell B1 automatically copy the corresponding data from say, Col B in Tab 2.
> > No calculations, just straight copy.
> >
> > (logic would be "if the value I entered in Tab1 A1 matches any of the names
> > in Tab2 Col A, then copy the person's address here).
> >
> > I've used it before...and it's straightforward...but this old brain just
> > didn't hang on to it. This was not a code or macro thing...just a straight
> > formula you write in a cell.
> >
> > THANKS EVER SO MUCH!!
(Msg. 4) Posted: Wed Sep 30, 2009 10:30 am
Post subject: RE: Help! Forgot formula for auto copy of cell value... [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
=INDEX(Tab2!A:Z,MATCH(A2,Tab2!Z:Z),2)
Should return your value.
this assumes your names in column Z are unique.
"Stilla" wrote:
> Oh thank you for reminding me of Vlookup!!! For this the reference data
> MUST be in the first column, right?
>
> Is there any other formula for using any other column but the first as a
> reference? Say, if a value in Col Z in Tab 2 is "Mr. Jones", then copy his
> address from Col B in Tab2)
>
> Thanks so much!
>
> "Sean Timmons" wrote:
>
> > =VLOOKUP(A1,Tab2!A:B,2,0) would do it.
> >
> > =IF(A1="","",VLOOKUP(A1,Tab2!A:B,2,0)) would show a blank cell until you
> > enter a value in A1.
> >
> > "Stilla" wrote:
> >
> > > Hi...thanks in advance for your help.
> > > Let's say I have two tabs. The formula is going in a cell B1 in tab1.
> > >
> > > In Tab 2, col A contains names, col B contains address, col C contains
> > > title...etc.
> > >
> > > I would like to manually enter one of the names in Tab 1, cell A1, and have
> > > cell B1 automatically copy the corresponding data from say, Col B in Tab 2.
> > > No calculations, just straight copy.
> > >
> > > (logic would be "if the value I entered in Tab1 A1 matches any of the names
> > > in Tab2 Col A, then copy the person's address here).
> > >
> > > I've used it before...and it's straightforward...but this old brain just
> > > didn't hang on to it. This was not a code or macro thing...just a straight
> > > formula you write in a cell.
> > >
> > > THANKS EVER SO MUCH!!
(Msg. 5) Posted: Wed Sep 30, 2009 10:57 am
Post subject: RE: Help! Forgot formula for auto copy of cell value... [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Perfect! Just what I was looking for! Actually, I'm going to suggest that
we work from #ID numbers vs from names.....since names may not be unique.
Thanks a million!
"Sean Timmons" wrote:
> =INDEX(Tab2!A:Z,MATCH(A2,Tab2!Z:Z),2)
>
> Should return your value.
>
> this assumes your names in column Z are unique.
>
> "Stilla" wrote:
>
> > Oh thank you for reminding me of Vlookup!!! For this the reference data
> > MUST be in the first column, right?
> >
> > Is there any other formula for using any other column but the first as a
> > reference? Say, if a value in Col Z in Tab 2 is "Mr. Jones", then copy his
> > address from Col B in Tab2)
> >
> > Thanks so much!
> >
> > "Sean Timmons" wrote:
> >
> > > =VLOOKUP(A1,Tab2!A:B,2,0) would do it.
> > >
> > > =IF(A1="","",VLOOKUP(A1,Tab2!A:B,2,0)) would show a blank cell until you
> > > enter a value in A1.
> > >
> > > "Stilla" wrote:
> > >
> > > > Hi...thanks in advance for your help.
> > > > Let's say I have two tabs. The formula is going in a cell B1 in tab1.
> > > >
> > > > In Tab 2, col A contains names, col B contains address, col C contains
> > > > title...etc.
> > > >
> > > > I would like to manually enter one of the names in Tab 1, cell A1, and have
> > > > cell B1 automatically copy the corresponding data from say, Col B in Tab 2.
> > > > No calculations, just straight copy.
> > > >
> > > > (logic would be "if the value I entered in Tab1 A1 matches any of the names
> > > > in Tab2 Col A, then copy the person's address here).
> > > >
> > > > I've used it before...and it's straightforward...but this old brain just
> > > > didn't hang on to it. This was not a code or macro thing...just a straight
> > > > formula you write in a cell.
> > > >
> > > > THANKS EVER SO MUCH!!
(Msg. 6) Posted: Wed Sep 30, 2009 12:27 pm
Post subject: RE: Help! Forgot formula for auto copy of cell value... [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
ALWAYS use unique identifiers when possible. Avoids many issues.
"Stilla" wrote:
> Perfect! Just what I was looking for! Actually, I'm going to suggest that
> we work from #ID numbers vs from names.....since names may not be unique.
>
> Thanks a million!
>
> "Sean Timmons" wrote:
>
> > =INDEX(Tab2!A:Z,MATCH(A2,Tab2!Z:Z),2)
> >
> > Should return your value.
> >
> > this assumes your names in column Z are unique.
> >
> > "Stilla" wrote:
> >
> > > Oh thank you for reminding me of Vlookup!!! For this the reference data
> > > MUST be in the first column, right?
> > >
> > > Is there any other formula for using any other column but the first as a
> > > reference? Say, if a value in Col Z in Tab 2 is "Mr. Jones", then copy his
> > > address from Col B in Tab2)
> > >
> > > Thanks so much!
> > >
> > > "Sean Timmons" wrote:
> > >
> > > > =VLOOKUP(A1,Tab2!A:B,2,0) would do it.
> > > >
> > > > =IF(A1="","",VLOOKUP(A1,Tab2!A:B,2,0)) would show a blank cell until you
> > > > enter a value in A1.
> > > >
> > > > "Stilla" wrote:
> > > >
> > > > > Hi...thanks in advance for your help.
> > > > > Let's say I have two tabs. The formula is going in a cell B1 in tab1.
> > > > >
> > > > > In Tab 2, col A contains names, col B contains address, col C contains
> > > > > title...etc.
> > > > >
> > > > > I would like to manually enter one of the names in Tab 1, cell A1, and have
> > > > > cell B1 automatically copy the corresponding data from say, Col B in Tab 2.
> > > > > No calculations, just straight copy.
> > > > >
> > > > > (logic would be "if the value I entered in Tab1 A1 matches any of the names
> > > > > in Tab2 Col A, then copy the person's address here).
> > > > >
> > > > > I've used it before...and it's straightforward...but this old brain just
> > > > > didn't hang on to it. This was not a code or macro thing...just a straight
> > > > > formula you write in a cell.
> > > > >
> > > > > THANKS EVER SO MUCH!!
(Msg. 7) Posted: Wed Sep 30, 2009 12:53 pm
Post subject: Re: Help! Forgot formula for auto copy of cell value... [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Look up VLookup in Help. HTH Otto
"Stilla" <Stilla.TakeThisOut@discussions.microsoft.com> wrote in message
news:73B2A3FC-AA54-4CB6-BB27-8EA897DC9384@microsoft.com...
> Hi...thanks in advance for your help.
> Let's say I have two tabs. The formula is going in a cell B1 in tab1.
>
> In Tab 2, col A contains names, col B contains address, col C contains
> title...etc.
>
> I would like to manually enter one of the names in Tab 1, cell A1, and
> have
> cell B1 automatically copy the corresponding data from say, Col B in Tab
> 2.
> No calculations, just straight copy.
>
> (logic would be "if the value I entered in Tab1 A1 matches any of the
> names
> in Tab2 Col A, then copy the person's address here).
>
> I've used it before...and it's straightforward...but this old brain just
> didn't hang on to it. This was not a code or macro thing...just a
> straight
> formula you write in a cell.
>
> THANKS EVER SO MUCH!!
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office