Cell values not being properly evaluated by VLOOKUP() orSUMPRODUCT()... why?
 

Any help would be appreciated. I can't attach the spreadsheet... so please contact me for a copy, if you have time and interest in assisting me. I assure you that I am quite capable of using Excel. I have looked at this for way too long and have no comparable colleagues to turn to...




I have separated the two issues...


VLOOKUP()

I created some Conditional Formatting in the <ILineups</I tab to determine when skater numbers are not on a Home/Away roster. It helps to point out typos.

I have similar conditional formulas in the following columns (C, F, I, L, O). If a value in even rows between 4 and 79 can NOT be found in the database <bHomeNumbers</b, change the font to strikethrough.

In the following columns (Y, AB, AE, AH, AK). If a value in even rows between 4 and 79 can NOT be found in the database <bAwayNumbers</b, change the font to strikethrough.

In both cases, the C (or Y) column behaves differently than the other columns F,I,L,O (or AB,AE,AH,AK). In columns C (or Y), I receive false negatives (strikethrough). Is it a cell format issue? Any ideas?


SUMPRODUCT()

This might be the same issue. If you go to the LU tab. Some cells (O10 [3], O11 [1], O13 [5], O18 [4], O21 [5]) are returning zero, when they should be returning a positive number [#]. Why aren't the Lineups! values being properly evaluated?


Again, if you have the time and interest, please contact me for a copy of the spreadsheet. With the right eyes, this should not take long at all.


Sean Kelly
Santa Cruz, CA

Cell values not being properly evaluated by VLOOKUP() or SUMPRODUCT()...why?
 

On 27/01/2014 01:22, wrote:
Any help would be appreciated. I can't attach the spreadsheet... so please contact me for a copy, if you have time and interest in assisting me. I assure you that I am quite capable of using Excel. I have looked at this for way too long and have no comparable colleagues to turn to...




I have separated the two issues...


VLOOKUP()

I created some Conditional Formatting in the <ILineups</I tab to determine when skater numbers are not on a Home/Away roster. It helps to point out typos.

I have similar conditional formulas in the following columns (C, F, I, L, O). If a value in even rows between 4 and 79 can NOT be found in the database <bHomeNumbers</b, change the font to strikethrough.

In the following columns (Y, AB, AE, AH, AK). If a value in even rows between 4 and 79 can NOT be found in the database <bAwayNumbers</b, change the font to strikethrough.

In both cases, the C (or Y) column behaves differently than the other columns F,I,L,O (or AB,AE,AH,AK). In columns C (or Y), I receive false negatives (strikethrough). Is it a cell format issue? Any ideas?

I suspect one or more of your cases in the list are not in alphabetical
order and/or a fencepost error. Try tacking ",FALSE)" on the end.


SUMPRODUCT()

This might be the same issue. If you go to the LU tab. Some cells (O10 [3], O11 [1], O13 [5], O18 [4], O21 [5]) are returning zero, when they should be returning a positive number [#]. Why aren't the Lineups! values being properly evaluated?

Suspect some of the cells may be formatted as text.


Again, if you have the time and interest, please contact me for a copy of the spreadsheet. With the right eyes, this should not take long at all.


Sean Kelly
Santa Cruz, CA



--
Regards,
Martin Brown