Difficulty referencing a row in another worksheet

[Veev]

On Sheet 2 (named Attendance), I have the following formula:

=IF(ISNUMBER(SEARCH("Veev",Attendance!2:2)),Attendance!B2,0)+IF(ISNUMBER(SEARCH("Veev",Attendance!3:3)),Attendance!B3,0)

On that sheet I have "4" in B2, and "2" in B3. The formula returns "6", as intended (since it found the word "Veev" in both rows).

However, when I paste that exact same formula on Sheet 1, it just returns "0". I cannot, for the life of me, figure out why. If I just try to reference "Attendance!B2" it will return "4" just fine, but it seems to be having some trouble in referencing the full row?

I have tried replacing "Attendance!2:2" with "Attendance!C2:ZZ2" but it didn't make a difference.

Any help would be greatly appreciated!

 

[shg]

I think you want MATCH:

=IF(ISNUMBER(MATCH("*veev*", Attendance!2:2, 0)), Attendance!B2) + ...


The match is not case-sensitive. The formula must be confirmed with Ctrl+Shift+Enter.

 

[Veev]

Hmm... thanks for the prompt reply, but that doesn't seem to be working.

=IF(ISNUMBER(MATCH("Veev",Attendance!2:2)),Attendance!B2,0)+IF(ISNUMBER(MATCH("Veev",Attendance!3:3)),Attendance!B3,0)

This returns 6, whether the word "veev" is there or not.

=IF(ISNUMBER(MATCH("*veev*",Attendance!2:2)),Attendance!B2,0)+IF(ISNUMBER(MATCH("*veev*",Attendance!3:3)),Attendance!B3,0)

This returns 0, no matter what I do.

{=IF(ISNUMBER(MATCH("*veev*",Attendance!2:2)),Attendance!B2,0)+IF(ISNUMBER(MATCH("*veev*",Attendance!3:3)),Attendance!B3,0)}

(Ctrl+Shift+Enter) This returns 0 as well.

The search function works flawlessly on the other page, I delete the word "Veev" from 1 row and the returned value changes from 6 to either 4 or 2. I'm not sure what the advantage of using match is over search.

Here is the spreadsheet I'm working with if that'd be more helpful: www.veevsvault.com/veev/riftdkp.xlsx

The cells I'm trying to get to work properly are B2 and B3 on the 1st worksheet -- which are referencing some data on the 2nd worksheet.

Thanks again!

 

[shg]

The 0 applies to the MATCH function:

=IF(ISNUMBER(MATCH("*veev*", Attendance!2:2, 0)), Attendance!B2) + ...

 

[Veev]

Ah, wow... don't I feel foolish. :P Works like a charm now, thanks a bunch!

 

[shg]

No worries, we all see what we expect to see somtimes. Glad you got it sorted.

 

[Veev]

One more question if you don't mind... if I need to create a new thread I don't mind, since this is a sort of different topic, just don't want to clutter the forum.

=IF(ISNUMBER(MATCH(A2, Attendance!$3:$3, 0)),Attendance!$B$3,0)+IF(ISNUMBER(MATCH(A2, Attendance!$4:$4, 0)),Attendance!$B$4,0)

This is my final (working) formula. A2 being the word "Veev" -- so it checks row 3 for "Veev", then row 4 for "Veev", etc.

I'd like for it to check as many rows that exist on that worksheet, without having to specify each one... whether it's 2 rows or 200 rows.

Is there an easy way to do this? I assume it has something to do with the index function, but unfortunately I have no experience using it.