find entry in corresponding sheet and return related value

**kildareman** · 08-18-2010, 11:41 AM

Hi all,

I have a table for our company tag rugby league which outlines each team in column A, the rest of the columns relate to opponents of these teams each gameweek.

In the workbook I also have a fixtures page where I can enter scores and this will update a further sheet which is the league table.

What I want to do is find the teams entered in the gameweek table in the league and relay their current position in the league. This will enable me to see which teams have hard or easy runs of games coming up according to the teams they are due to play.

Hope I havent made this too confusing and thanks to anyone who takes their time to read it!

kildareman

**DonkeyOte** · 08-18-2010, 12:27 PM

For this type of thing a sample file is generally helpful as it saves people guessing as to the exact layout of your data.

To attach a file click the paperclip icon in the reply window - if you can not see it first click Go Advanced

**kildareman** · 08-19-2010, 04:25 AM

Hi all

I have attached the sheet I am currently working from.

Im trying to work with the "upcoming fixture" sheet. I would like to give each team a value according to their current position in the "league table" sheet.

Eg if we take team 1 for example their upcoming fixtures are Week 2 - Team 5
Week 3 - Team 4
& Week 4 - Team 6.
I would like the upcoming fixture sheet to show their positions 8, 18 & 15 from league table - column B.

Hopefully as i update fixtures and results pages, thus updating league table this would also update my upcoming fixture values.

I really hope I havent made that sound too confusing. If anyone thinks they can help please do and let me know if you have any more questions.

kildareman

**DonkeyOte** · 08-19-2010, 05:29 AM

The simplest formula based solution would be to create a new sheet which replicates the values from the Upcoming Fixtures sheet whilst adding to that the league placing.

On your duplicated sheet your formula for B2 etc would be:

Please Login or Register  to view this content.

Does that make sense ?

In terms of "old fixtures" - I would be inclined to hide rather than delete those weekly columns (on both original & duplicate sheet)