How do i reference another tab in my spreadsheet

lumpar

New Member
Joined
Jun 19, 2012
Messages
10
Hello

I am trying to create a lookup or if statement, i am a bit clueless when it comes to this so apologies.

I have tab 1 of my spreadsheet and in a1 i would like to reference some data on another page my other page would be players, and in the players i would have 4 sections, for example goalkeepers, defenders etc

example is trevor
Player
Club
Value
Points
Trevor
Badajoz
1.0
52

<tbody>
</tbody>


I would type in trevor in a1 in my first tab and it would then reference the players list and fill in club, value, points, the reference would need to look at goalkeepers, defenders etc.

Can anyone help with this please?

Thanks

Matt
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
this is how it can be done.

notice the differance between B6 en B7 (2 different formula's)

you can also work with defined names.

sheet1
A
B
C
D
5
Player
Club
Value
Points
6
Trevor
Badajoz
1.0
52
7
van Basten
NL
15
53

<TBODY>
</TBODY>


Worksheet Formulas
Cell
Formula
B6
=VLOOKUP($A6,sheet2!$A$2:$D$3,2,0)
C6
=VLOOKUP($A6,sheet2!$A$2:$D$3,3,0)
D6
=VLOOKUP($A6,sheet2!$A$2:$D$3,4,0)
B7
=VLOOKUP($A7,sheet2!$A$2:$D$3,COLUMN(),0)
C7
=VLOOKUP($A7,sheet2!$A$2:$D$3,COLUMN(),0)
D7
=VLOOKUP($A7,sheet2!$A$2:$D$3,COLUMN(),0)

<TBODY>
</TBODY>

<TBODY>
</TBODY>
 
Upvote 0
I tried doing this and cannot get to work

I cannot get it to reference it just has an N/A error

Any ideas?
 
Last edited:
Upvote 0
1) Then VLookup had no match.

2) Are the namens in sheet 2 sorted A- Z?

If not VLookup has problem with that.

You can use then index / match.
 
Upvote 0
When using Vlookup you don't have to sort A-Z

I look at this and post back

Also can you post the name of your tabs? Also what does the data in your second tab look like. Could do with you posting some of it?
 
Last edited:
Upvote 0
When using Vlookup you don't have to sort A-Z

I look at this and post back

Also can you post the name of your tabs? Also what does the data in your second tab look like. Could do with you posting some of it?

hi

The tabs are called teams and players, they are based on team name in order. So i have goalkeeper
arsenal, everton etc, then defenders they will be based on the same

eg
GK 4 W Szczesny Arsenal £4.5m
GK 65 S Given Aston Villa £1.5m
GK 24 P Cech Chelsea £5.5m

DF 463K Gibbs Arsenal £3.5m
DF 461J Djourou Arsenal £3.0m
DF 197S Squillaci Arsenal £3.0m
DF 169A Santos Arsenal £2.5m
DF 167P Mertesacker Arsenal £3.5m
DF 106B Sagna Arsenal £4.0m
DF 105T Vermaelen Arsenal £6.0m
DF 103L Koscielny Arsenal £4.0m
DF 329R Vlaar Aston Villa £2.5m
DF 236R Dunne Aston Villa £3.0m
DF 186M Lowton Aston Villa £1.5m
DF 136S Warnock Aston Villa £2.0m
DF 115C Clark Aston Villa £1.5m

<colgroup><col span="2"><col><col><col></colgroup><tbody>
</tbody>

Would really appreciate any guidance
 
Upvote 0
Because you have named your worksheets "teams" and "players", all you need to do is substitute the appropriate worksheet name for "sheet2" in the below formula.



Worksheet Formulas
Cell
Formula
B6
=VLOOKUP($A6,sheet2!$A$2:$D$3,2,0)
C6
=VLOOKUP($A6,sheet2!$A$2:$D$3,3,0)
D6
=VLOOKUP($A6,sheet2!$A$2:$D$3,4,0)
B7
=VLOOKUP($A7,sheet2!$A$2:$D$3,COLUMN(),0)
C7
=VLOOKUP($A7,sheet2!$A$2:$D$3,COLUMN(),0)
D7
=VLOOKUP($A7,sheet2!$A$2:$D$3,COLUMN(),0)

<tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:
Upvote 0
I assume that your second worksheet (tab) is named, players. In this case, you would enter players! in place of sheet2!

Don't forget to use the ! mark after players. The rest of the formula remains the same as previously posted.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top