VLOOKUP ERROR

[Chard9401]

In cell A1 i enter a number between 1-40. Now depending on what number is entered will effect the results on my table.

I have a table with 4 headings. Position, Name, Team Name, and Points. Now the cells under the points column is worked out by a VLOOKUP. The VLOOKUP i use is =VLOOKUP(B1,Weeks,7,FALSE) now when i sort this table into numerical order it does not display the correct points and displays #REF! and the VLOOKUP has changed to =VLOOKUP(#REF!,Weeks,7,FALSE)

Why is this? Is any one able to help? If you need anymore info, let me know

 

[Chard9401]

sorry it should be cell B1 i enter a number between 1 to 40 not A1

 

[crimlet]

Have you moved or deleted cell B1 @ any time ? if so this results in a #REF!

 

[crimlet]

Just replace the #REF! by overtyping with B1 again

 

[Chard9401]

i have gone over it and retyped B1 but i get the same error again once i sort the table into order.

I haven't moved cell B1 it has a drop down list of 1 to 40 to select from and the depending on which number i choose will effect the results in the table. Now the error only occurs when the table is sorted into order. Is there any way i can prevent this from happening?

 

[Chard9401]

it also changes it to =VLOOKUP(B2,Weeks,11,FALSE) and get the error #N/A why is it changing my VLOOKUP formula? it never stays the same! i have a VLOOKUP on another sheet and that works fine, why is it i keep getting this error?

 

[Chard9401]

could i have a multiple IF statement? =IF(B1=1,C38,IF(B1=2,D38,IF(B2=3,E38))) but all the way up to IF(B1=40 if you know what i am trying to do. I could do it this way couldn't I? i really need some help with this