Vlookup and isblank (or ???)

[Guest]

I am modifying my spreadsheet for a coming football cup for youths.

Today I have one sheet per Group (Boys89, Boys90, Girls90 etc.) and move
between sheets when entering in the results
The important columns are (A=Match.no, J=Result Home, K=Result Away)
Each match get 0,1,3 points and points are located in column T,U...
Formula in T5 is: =IF(ISBLANK(J5);0;IF(J5-K5>0;3;IF(J5-K5=0;1;0)))
Goals scored are in column P: (=+J5+J7+J9) and goals against in Q
(=+K5+K7+K9)

When entering the results in cell J5 and K5 the points and goals for and
goals against are updated.

This works fine, but I want to improve entering results. I want to type the
result in one sheet called RegisterResult where all matches are sorted on
match.no from 1 to 162 (no. of matches last year)

The important columns are (A=Match.no, L=Result Home, M=Result Away)
When I register results for match.no. 1 I want to have updated the same
results in the actual sheet.
This works fine using vlookup, i.e.
=IF(ISBLANK(VLOOKUP(A5;RegisterResult!$A$2:$M$162;12));"";VLOOKUP(A5;RegisterResult!$A$2:$M$162;12))

But this formula creates a problem when no results are registered. The cell
T5 reports #VALUE due to the "" in the above formula (J5) (ISBLANK in cell T5
does not work any more)

I have tried other ways (0 - zero cannot be used (i.e. goalless draw) but
with no luck.

Anybody out there with a way around?

*gublues

 

[Pete_UK]

Why not return a negative number (eg -1) and have your formula in T5
check for a negative number before doing what it does at the moment?
Or, rather than use ISBLANK you could keep things as they are and have
the formula in T5 do something like:

=IF(AND(J5="",K5=""),"no result yet", what_you_want_it_ to_do)

I'm not sure where the VLOOKUP formula above is actually located - is
this in T5?

Hope this helps.

Pete

 

[Guest]

Sorry, The VLookup is located in cell J5.

Good suggestion Pete_UK, but it solves only some of my problems.

Alt. 1 using -1 in vlookup
It partly works if I use a conditional formatting (white font if cell value
less than 0) in the cells J5 and K5 (goals scored and goals against) (-1 does
not look nice on printouts). I get into problems or I have to have so many
if's when adding the goals for each team (the formula for team no 1 for goals
scored is (+J5+J7+J9).

The other suggestion also works partially, but same problem arise there.

So, I have to manually adjust so many cells that I hope someone has a easier
one to implement.

Thanks anyway

Pete_UK skrev:

 

[Pete_UK]

You're welcome.

Anyway, you might like to check out this link for a few more ideas
about maintaining a football league:

http://www.xldynamic.com/source/xld.LeagueTable.html

Hope it helps.

Pete