cell references between worksheets

[yonner1]

this is the situation:

in worksheet 1: a1 contains the name jane and b1 contains 100. (this is jane's personal #).

a2 contains the name buster and b2 contains the number 400.

in worksheet 2: a2 contains the name jane and b2 has the formula (=sheet1!b1). which means that b1 will contain the number 100 as the answer.

now in worksheet 1: if i sort a1,b1,a2,and b2 by the (a) column, a1 contains the name buster and b1 contains the number 400.

what will happen in worksheet 2 is that b2 also contains the number 400, but a2 still contains the name of jane in which 400 is not her personal number, it is 100.

QUESTION: what i want to do is everytime i change jane's personal number or change where i want her placed in worksheet 1, it will also change in worksheet 2 as well.

i hope this was not confusing.

thanks

yonner1

 

[Bowers74]

"Jane" will never change in worksheet 2 because you have "hardcoded" it to the cell (i.e. It has no reference to the cell in worksheet 1). You need to put "=Sheet1!A1" in cell A2 on worksheet 2 and "=Sheet1!B1" in cell B2 on worksheet 2. Copy these formulae down and when you change a cell in columns A or B on worksheet 1 the values in worksheet 2 will change as well because they are all referenced.

QUESTION: Have you thought about using the VLOOKUP() function to do what you are doing?



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[yonner1]

thanks mike for responding so quickly. i understand that about jane, but let's assume that jane was changed too.

Question: what i want to do is everytime i change jane's personal number or change where i want her placed in worksheet 1, it will also change in worksheet 2 as well.

Question rephrased:

in worksheet 2, jane is in cell a2 and her personal # is in cell b2. i would like cell a2 for jane to always stay the same no matter what. (permanent if you will) and in cell b2 also to be permanent....but....

in worksheet1 if i just happen to change jane from a1 to let's say cell d3 and change her personal number of 100 to 403 cell d4, i would still want worksheet2 in cell a2 to still have jane's name and b2 to still have jane's new personal number of 403.

in other words worksheet2 is permanent for those people in those cells, no matter what i change in other worksheets, it will go to the permanent cells in worksheet2.

i hope i did not confuse you anymore than i already have and i never considered the vlookup function, but you can tell me if you wish. will be giving you a star

thanks
yonner1

 

[yonner1]

thanks mike74 for responding so quickly. i understand that about jane, but let's assume that jane was changed too.

Question: what i want to do is everytime i change jane's personal number or change where i want her placed in worksheet 1, it will also change in worksheet 2 as well.

Question rephrased:

in worksheet 2, jane is in cell a2 and her personal # is in cell b2. i would like cell a2 for jane to always stay the same no matter what. (permanent if you will) and in cell b2 also to be permanent....but....

in worksheet1 if i just happen to change jane from a1 to let's say cell d3 and change her personal number of 100 to 403 cell d4, i would still want worksheet2 in cell a2 to still have jane's name and b2 to still have jane's new personal number of 403.

in other words worksheet2 is permanent for those people in those cells, no matter what i change in other worksheets, it will go to the permanent cells in worksheet2.

i hope i did not confuse you anymore than i already have and i never considered the vlookup function, but you can tell me if you wish. will be giving you a star

thanks
yonner1

 

[AGlazer]

Yonner1,

Try this. If you have

sheet1:
a b c d
1 jane buster joe sue
2 102 203 304 405

sheet2:
a b c d
1 buster joe jane sue

and you want row 2 to be the company numbers

in Sheet2, A2, put the following
=HLOOKUP(A1,Sheet1!$B$1:$D$2,2,FALSE)

and autofill to the right.
HLOOKUP do a look up of a horizontal table:
- A1 is the cell value you're looking up (in this case, buster)
- Sheet1$B$1:$D$2 is the absolute reference to the table (in this case, sheet1, from jane to sue and from 102 to 405)
- 2 is the row number to look up (in this case, the row with employee numbers)
- false should be your default for this -- only use true if you're looking up numerical data and want it to round.

let me know if this works.


aaron

 

[SkipVought]

yonner,

VLOOKUP is the way to go...

On your 2nd sheet, the formula in b2

=VLOOKUP(A2,Sheet1!A$2$:B$999$,1,FALSE)

the range, A$2$:B$999$ is the data range on your sheet 1.

Copy that down and no matter how sheet 1 is sorted, you'll have the associated data.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com