Providing a reference to another cell in a different workbook

[Darren]

My problem: I am a football team manager. I have 2 workbooks, 1 has top
appearances for each player (column 1 shows their overall position, column 2
shows their name, column 3 shows total appearances) the other has top
goalscorers (column 1 shows their overall position, column 2 shows their
name, column 3 shows their percentage of goals scored per game using a simple
formula that divides the number of goals scored by...column 4 which shows the
number of appearances.)

I am trying to get a formula that will automatically update the number of
appearances in column 4 on my top goalscorers workbook whenever i update my
top appearances for a particular player, whilst ensuring that a player's
position in the top appearances can change.

I have tried using the ADDRESS function, as well as VLOOKUP and MATCH and
still can't get anywhere. help help help!!!!!!!!!!

 

[tim m]

I just did up a bit of test data with three names and the books set up as you
described and this formula seemed to work.

=VLOOKUP(B2,[Book1]Sheet1!$B$1:$C$4,2,FALSE)

The formula looks at B2 which is the 1st name at the top of the list in Book2.
It then looks at Book1, Sheet1 and looks at the names and number of
appearances. When it finds the proper name it takes the 2nd cell to the
right (which is the apperances for that player.)

Since it is using the name to search for the corresponding appearances it
shouldn't matter if the order of appearances changes in Book1.

(as an aside, I take it you have a seperate column for goals in book2 in
which you use to calculate the goal %?)

 

[Darren]

Hi Tim

Thanks for your reply. I'm not that conversant with formulae, and in
particular ones such as vlookup, so can you please dumb down for me a little
further.

Firstly, to answer your question, yes i forgot to add that i had a column
for number of goals scored in book 1 which then links in with the number of
appearances to give me a percentage figure. If i elaborate a little further
on the exact cell detail, could you help me out. Here goes:

Workbook 1: called Top Appearances.xls
Has 3 columns: column A= the rank in appearances (eg 1-150); column B=the
name of the player; column C=number of appearances.

Workbook2: called Top Goalscorers.xls
Has 5 columns: column A=the rank in goals scored (eg 1-150); column B=the
name of the player; column C=number of goals scored; column D=my formula to
show goal percentage for that player which is column C divided by column E;
column E=number of appearances.

So, I want to cross-reference my column E in book 2 to wherever that
person's appearances show in Book 1. I think you have the gist of it from
your response although i have tried to copy your formula and replace your
example cells but i still can't get it to work. help!!!

I just did up a bit of test data with three names and the books set up as you
described and this formula seemed to work.

=VLOOKUP(B2,[Book1]Sheet1!$B$1:$C$4,2,FALSE)

The formula looks at B2 which is the 1st name at the top of the list in Book2.
It then looks at Book1, Sheet1 and looks at the names and number of
appearances. When it finds the proper name it takes the 2nd cell to the
right (which is the apperances for that player.)

Since it is using the name to search for the corresponding appearances it
shouldn't matter if the order of appearances changes in Book1.

(as an aside, I take it you have a seperate column for goals in book2 in
which you use to calculate the goal %?)

My problem: I am a football team manager. I have 2 workbooks, 1 has top
appearances for each player (column 1 shows their overall position, column 2
shows their name, column 3 shows total appearances) the other has top
goalscorers (column 1 shows their overall position, column 2 shows their
name, column 3 shows their percentage of goals scored per game using a simple
formula that divides the number of goals scored by...column 4 which shows the
number of appearances.)

I am trying to get a formula that will automatically update the number of
appearances in column 4 on my top goalscorers workbook whenever i update my
top appearances for a particular player, whilst ensuring that a player's
position in the top appearances can change.

I have tried using the ADDRESS function, as well as VLOOKUP and MATCH and
still can't get anywhere. help help help!!!!!!!!!!