How do I link a cell to another cell even if that cell moves

Jasonp4 · Apr 19th, 2002, 12:17 PM

Here’s my question to you. I am using excel 2002 at school and I am trying to set up a hockey pool stats worksheet.

On Sheet 1, I have my master list of 100 players and on sheet 2 I have 4 individual roster lists each containing 25 player from the master list.

Example
Sheet 1
Player Team Pos Point
Total
Sundin Tor Fwd 1 (let's call the point total cell D3)
99 more players

On sheet 2 I have the same setup
Player Team Pos Point
Total
Sundin Tor Fwd =Sheet1!$D$3
24 more players

My problem is that when I sort Sheet 1 to display point leaders, the link on sheet 2 (=Sheet1!$D$3) is no longer accurate because Sundin on sheet 1 is no longer at cell D3.

How can I assign a link or formula to Sundin's cell even if it moves?

Thanks everyone for your help!

Mark W. · Apr 19th, 2002, 12:24 PM

Consider using VLOOKUP.

Jasonp4 · Apr 19th, 2002, 12:29 PM

Thanks Mark....
God that was easy... I feel stupid now!!
hehe
Even my computer professor didn't know what to do!
Thanks Again

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 19th, 2002, 12:17 PM	#1
Jasonp4 New Member Join Date: Apr 2002 Posts: 2	Here’s my question to you. I am using excel 2002 at school and I am trying to set up a hockey pool stats worksheet. On Sheet 1, I have my master list of 100 players and on sheet 2 I have 4 individual roster lists each containing 25 player from the master list. Example Sheet 1 Player Team Pos Point Total Sundin Tor Fwd 1 (let's call the point total cell D3) 99 more players On sheet 2 I have the same setup Player Team Pos Point Total Sundin Tor Fwd =Sheet1!$D$3 24 more players My problem is that when I sort Sheet 1 to display point leaders, the link on sheet 2 (=Sheet1!$D$3) is no longer accurate because Sundin on sheet 1 is no longer at cell D3. How can I assign a link or formula to Sundin's cell even if it moves? Thanks everyone for your help!

Apr 19th, 2002, 12:24 PM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Consider using VLOOKUP.

Apr 19th, 2002, 12:29 PM	#3
Jasonp4 New Member Join Date: Apr 2002 Posts: 2	Thanks Mark.... God that was easy... I feel stupid now!! hehe Even my computer professor didn't know what to do! Thanks Again