SOS!

[Brian Jarvis]

Hi!

I have a query for help, I have been left a spreadsheet to run a Fantasy
Football comp for my workplace, but it has some bugs in it that I have no
idea how to fix.

It is basically 3 sheets- 1 sheet a list of all players with their reference
number, 2nd sheet a list of participating teams (you enter the code number
and the player details appear), and the 3rd sheet is a league table.

However, there seem to be some bugs in the system in that some links do not
work- its a VLOOKUP formula which, when you type in the 3 number code in the
2nd sheet wil look for the details from the 1st sheet, but it gets to a
point where it will not look up. e.g. gets to player 415 and sticks there
onwards.

If anyone could help me it would be greatly appreciated, as I aid I would
run this for fun for colleagues and could end up with 200 teams with a
bugged spreadsheet!

Thanks in advance
Brian

 

[Ken Wright]

Sounds like the range in the VLOOKUP formula needs to be extended to cover the additional players.
There are better ways of handling this though, eg naming ranges and using those names in the
formulas, so that when you increase a range by adding more records, the rest of the formulas that
apply to it don't need to be updated.

eg, suppose you had a bunch of records in the range H1:L500, and you named them myrange, then you
could use that in a VLOOKUP formula as in the following example:-

=VLOOKUP(A1,myrange,3,FALSE)

 

[Brian Jarvis]

Sounds like the range in the VLOOKUP formula needs to be extended to cover the additional players.
There are better ways of handling this though, eg naming ranges and using those names in the
formulas, so that when you increase a range by adding more records, the rest of the formulas that
apply to it don't need to be updated.

eg, suppose you had a bunch of records in the range H1:L500, and you named them myrange, then you
could use that in a VLOOKUP formula as in the following example:-

=VLOOKUP(A1,myrange,3,FALSE)

Thanks

Ill have a look and see if I can do that although my use of Excel is very
basic!

Brian

 

[Ken Wright]

No Problem, look at it this way:-

You start with a range of data in say H1:K415, and you have a formula such as
=VLOOKUP(A1,H1:K415,3,FALSE) which will go look for whatever value is in A1 and see if it can find
it in the leftmost column of your range H1:K415. If it finds it it will then return whatever data
is in the 3rd column of your range on the matching row. the FALSE bit at the end means it must be
an exact match.

Now suppose you add some more records to your range so that it is really now H1:K500. If you
haven't altered the formula in the VLOOKUP formula, it is still only looking at the first 415 rows
of data, and it doesn't know that there are now another 85 records underneath it.

One way to avoid this is to perhaps set yopur range of records up so that you have a dummy row at
the bottom, eg H416:K416 in this case - Colour it yellow or something to tell you it is a dummy
row. Change your formula to pick up this dummy row, eg make it =VLOOKUP(A1,H1:K416,3,FALSE). Now
whenever you want to add a record, click on to the dummy row header (Far left of the spreadsheet),
and then hit CTRL & + together, which will insert a row. Now put your new record in the new row.
By doing it this way, any formula that refers to that range will automatically be updated.

You can still name the range and use the name in the formula, but again, make sure that the named
range includes the dummy row. There are other ways as well, but this may be the easiest to
understand.

 

[Brian Jarvis]

It seems to be thinking in! I'll have a look at it tonight when I get into
work. The problem was that the formula looked right, to me it looked as if
it was looking in the rigt place for the data. I'll give it a bash, thanks
for the advice.

Brian

No Problem, look at it this way:-

You start with a range of data in say H1:K415, and you have a formula such as
=VLOOKUP(A1,H1:K415,3,FALSE) which will go look for whatever value is in A1 and see if it can find
it in the leftmost column of your range H1:K415. If it finds it it will then return whatever data
is in the 3rd column of your range on the matching row. the FALSE bit at the end means it must be
an exact match.

Now suppose you add some more records to your range so that it is really now H1:K500. If you
haven't altered the formula in the VLOOKUP formula, it is still only looking at the first 415 rows
of data, and it doesn't know that there are now another 85 records underneath it.

One way to avoid this is to perhaps set yopur range of records up so that you have a dummy row at
the bottom, eg H416:K416 in this case - Colour it yellow or something to tell you it is a dummy
row. Change your formula to pick up this dummy row, eg make it

=VLOOKUP(A1,H1:K416,3,FALSE). Now

 

[Ken Wright]

LOL - It's sunday, everything's forgiven on a sunday.

 

[Brian Jarvis]

Couldnt get it to work unfortunately :-(


The players are in cells a3: C431 in sheet 1

The formula for the first player on sheet 2 (which works) is
=IF($A5="","",VLOOKUP($A5,PLAYERS!$A$4:$H$862,2,TRUE))

However, the one that doesn't work is:
=IF($A13="","",VLOOKUP($A13,PLAYERS!$A$4:$H$862,2,TRUE)) Meaning players in
cells numbers 301-320 all show the player in cell 300.

Wrackig my brains tried editing the formula to:
=IF($A13="","",VLOOKUP($A13,PLAYERS!A4:C231,2,TRUE))

But no joy, in fact this moves it up to cell numbers 269.
I can't think why!!!!

Any help greatly appreciated (again!!)

Brian

 

[John Wilson]

Brian,

Why did you switch to the "True" option on the VLookup????
True means that the VLookup expects the table to be sorted
(alphabetically or numerically). If the table isn't sorted, the VLookup
will give you the closest match without going over the looked up
value.
The "False" option doesn't care whether the table is in order or
not and will give you an exact match (also an error if there
isn't an exact match).

John

 

[Brian Jarvis]

John,

It was somebody else's spreadsheet that I have been left
with trying to figure out! The formulas seem correct- the
range given is the same in the cells that are correct and
incorrect. I tried changing the range but it gave wrong
values still, changing it to FALSE gave an "#N/A" message
for the incorrect cells. I'm assuming its a step in the
right direction but as the formulas look the same all the
way through (the only change is the cell it is in) it is
perplexing as to why it is missing some.

Brian

 

[John Wilson]

Brian,

changing it to FALSE gave an "#N/A" message

The false means that it's looking for an exact match and
if an exact match isn't found, it'll return the #N/A.
That's the way it's designed to work.
The "True" option gives the closest match without going
over the value looked up. One of the prime uses for this type
of lookup is when your searching within a range of numbers.
Example:
Consider a tax table with the values 100, 200, 300, etc.
running down column A. A "False" lookup for the value
233 will result in a #N/A since that value isn't on the table
but the "True" option will lock onto the 200 value. As a matter
of fact, any number that you look up between 200 and
299 will lock onto that 200 value. This can be quite useful
for tax tables and the like but I don't think it'll work for
what you're looking to do.
If you're getting a #N/A for a lookup, what you're looking
up can not be found on the table that you're looking it up
in.

If you'd like to forward the workbook directly to me, I'll
take a quick look at it and see if I can offer any pointers.

John

 

[Immanuel]

Sounds to me like you might have extra spaces in the lookup array. Try
performing an edit / replace of <space> with nothing in the lookup array.

/i.

Brian,

changing it to FALSE gave an "#N/A" message

The false means that it's looking for an exact match and
if an exact match isn't found, it'll return the #N/A.
That's the way it's designed to work.
The "True" option gives the closest match without going
over the value looked up. One of the prime uses for this type
of lookup is when your searching within a range of numbers.
Example:
Consider a tax table with the values 100, 200, 300, etc.
running down column A. A "False" lookup for the value
233 will result in a #N/A since that value isn't on the table
but the "True" option will lock onto the 200 value. As a matter
of fact, any number that you look up between 200 and
299 will lock onto that 200 value. This can be quite useful
for tax tables and the like but I don't think it'll work for
what you're looking to do.
If you're getting a #N/A for a lookup, what you're looking
up can not be found on the table that you're looking it up
in.

If you'd like to forward the workbook directly to me, I'll
take a quick look at it and see if I can offer any pointers.

John

John,

It was somebody else's spreadsheet that I have been left
with trying to figure out! The formulas seem correct- the
range given is the same in the cells that are correct and
incorrect. I tried changing the range but it gave wrong
values still, changing it to FALSE gave an "#N/A" message
for the incorrect cells. I'm assuming its a step in the
right direction but as the formulas look the same all the
way through (the only change is the cell it is in) it is
perplexing as to why it is missing some.

Brian

-----Original Message-----
Brian,

Why did you switch to the "True" option on the VLookup????
True means that the VLookup expects the table to be sorted
(alphabetically or numerically). If the table isn't sorted, the VLookup
will give you the closest match without going over the looked up
value.
The "False" option doesn't care whether the table is in order or
not and will give you an exact match (also an error if there
isn't an exact match).

John


Brian Jarvis wrote:

Couldnt get it to work unfortunately :-(

The players are in cells a3: C431 in sheet 1

The formula for the first player on sheet 2 (which works) is
=IF($A5="","",VLOOKUP($A5,PLAYERS!$A$4:$H$862,2,TRUE))

However, the one that doesn't work is:
=IF($A13="","",VLOOKUP($A13,PLAYERS!$A$4:$H$862,2,TRUE)) Meaning players in
cells numbers 301-320 all show the player in cell 300.

Wrackig my brains tried editing the formula to:
=IF($A13="","",VLOOKUP($A13,PLAYERS!A4:C231,2,TRUE))

But no joy, in fact this moves it up to cell numbers 269.
I can't think why!!!!

Any help greatly appreciated (again!!)

Brian

LOL - It's sunday, everything's forgiven on a sunday.

----------------------------------------------------------------------------------------------------------------------------------------------------

--



It seems to be thinking in!

Possibly, or even sinking in!
D'oh!

Brian






.

 

[Brian Jarvis]

Hi

Thanks for the offer, I seem to have it sorted now. Changed all the VLOOKUP
to false and added in cells so that they would all be in numerical order
(rather than 0-154 then 200-395 then 500-612 for example). Also changed the
formatting of some of the cells and it seems to be picking most things up.
And yes the weather here is nice in lovely Dagenham too!!

Brian

 

[Brian Jarvis]

OK, glad you are sorted, but the whole FALSE or 0 thing as an optional 4th argument is so that you
do not have to sort the data.

Thanks, I have now established the VLOOKUP command to a basic degree, but I
think some of the creators dodgy formatting caused most of the problems as
the adice you and others gave seemed dead right and understandable to me but
the system was having none of the it. Re-formatting most of the cells and
changing the properties, along with going from true to false seems to have
sorted it. Now all I need to do is win the Fantasy Football comp to make it
worth the hassle!!!

Brian