Multiple sheets match data too all sheets

[Glensteven4life]

Good Day All,
I have multiple sheets with names of countries with times indicated for each one over distances categorised with a point allocation above each country.
If for example an athlete in Italy ran over 100 metres in 9.7 sec the point allocation should be 148 points in Sheet1 D2.
If place has been changed in A , with distance and time inputted then the points would automatically update for each athlete in their country.
Please assist to create a formula for me since I have tried several times with vlookup .

[alansidman]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Glensteven4life]

I can only see Manage attachments
then I uploaded 12.9 kb spreadsheet
I cannot see go advanced.

[Glensteven4life]

Good Evening / Morning All
I'l try to explain.
In Sheet 1, A1 is the Venue , B1 the distance, C1 time , D1 points
in A2 there is a dropdown list of the countries example Greece , Italy, Germany , England and Japan.
All these countries has their own time tables respectively in different worksheets. From B1: L1 is the points to be allocated. Points can be (10 , 9, 8 , 7, 6, 5)
From A2 :A5 is the selected distances.
From B2 : L2 is the time intervals for 100 metres ( 9.5 , 9.6 , 9.7 , 9.8 .....)
From B3 : L3 is the time interval for 200 metres ( 21.5 , 21.6 , 21.7 , 21.8 )
All counties athletes have different times for their current athletes.
So when you select country in Sheet1 A2 as Germany from list the formula should automatically recognise the sheet for country to select , after distance time has been captured in B2 and C2 respectively the points will be allocated once all variables has been selected.

[alansidman]

See the picture
Capture.JPG

[Glensteven4life]

See the picture
Attachment 469174

Thanks for replying Alan i am using a mobile phone .

[Glensteven4life]

Kindly find attachment finally default settings

[JohnTopley]

in D2

=IFERROR(INDEX(INDIRECT("'" & A2 &"'!$B$1:$L$1"),MATCH($C2,OFFSET(INDIRECT("'" & A2 &"'!$A$1"),MATCH($B2,INDIRECT("'" & A2 &"'!$A$2:$a$8"),0),0,1,11),0)-1),"")

Copy down

[Glensteven4life]

Hi John i really appreciate your time on this one.
The formula works it's only the time intervals in column L2 down to L5 that the formula don't pick up the points.

[JohnTopley]

Try


=IFERROR(INDEX(INDIRECT("'" & A2 &"'!$B$1:$L$1"),MATCH($C2,OFFSET(INDIRECT("'" & A2 &"'!$A$1"),MATCH($B2,INDIRECT("'" & A2 &"'!$A$2:$a$8"),0),0,1,12),0)-1),"")

[Glensteven4life]

Found the problem change ,0,1,11 to 0,1,12
i applied in my master file but result only comes up blank.
The formula works in the example
If i mark it solved then i will not be able to comeback because of rules of forum my master file is almost 3 Mb

[JohnTopley]

What is different about your master file? If the layout is the same as your sample then there is no obvious reason why it does not work.

Can you not extract a subset of your master file where the errors are occurring?

Alternatively you could ZIP your master file and upload, but an small extract would be much better.

[Glensteven4life]

It works now I found the error on my side it's a format cells issue on my side if a time is 10.19 sec it doesn't pick it up as 10.20 doesn't round off to the nearest tenth .
I'll fix the format of the time
thank you for your input the time and effort.
Amazing formula

[JohnTopley]

Glad you have it working.

If the problem has been fixed, could you please mark the thread as solved ("Thread Tools" at top of first post).