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


K

ksean897

Any help would be appreciated. I can't attach the spreadsheet... so pleasecontact 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 <I>Lineups</I> tab to determine when skater numbers are not on a Home/Away roster. It helps to point outtypos.

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 <b>HomeNumbers</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 <b>AwayNumbers</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
 
Ad

Advertisements

M

Martin Brown

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 <I>Lineups</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 <b>HomeNumbers</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 <b>AwayNumbers</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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top