Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and isblank (or ???)
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-K50;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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and isblank (or ???)
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and isblank (or ???)
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: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup and isblank (or ???)
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using a vlookup, can i use isblank to show an empty cell | Excel Worksheet Functions | |||
VLOOKUP & ISBLANK | About this forum | |||
VLOOKUP & ISBLANK | About this forum | |||
Vlookup finds a blank, but returns a zero - HELP! | Excel Discussion (Misc queries) | |||
isblank function | Excel Worksheet Functions |