# Solved: Excel 2007 - Can't Calculate Values Between Sheets

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### bbureau12

This is an awfully strange and frustrating one.

I've been working on a formula that will compare values between sheets. ie. if the value in Sheet1 B1 match anything in Sheet2 B:B AND the adjacent value in C1 matches the adjacent value in Sheet2 column C, return TRUE.

I've gotten this formula to work in a proof of concept workbook with two sheets and about five columns worth of data. It will NOT work in another workbook where I'm comparing a 400-row sheet to a 65K row sheet. It will also not work when I break it down to a 20K row sheet. It IS able to find specific values. I can ask it to compare Sheet1!B1 to Sheet2!B375 for example, and it will return TRUE if they match. However, if I ask it to look through Sheet2!B:B for the same value (even though I know it is there on B375), it returns FALSE.

Does anyone know why this is happening, and if so can they suggest a fix/workaround?

#### computerman29642

So, the formula will always compare data to cell B1 of Sheet1? Did you copy down the formula?

Could you attach a sample file?

#### computerman29642

bbureau, did you attach a file?

#### bbureau12

Thanks for the reply! While it can find Sheet1 B1 (10700) on B318 of Sheet St Paul, it CANNOT find B1 in Column B of St Paul. Ive pared it down to 14K, 1K, and even 3 entries same thing.

The formulas are:
=IF(B2='St. Paul'!B:B,"YES","NO")
=IF(B2='St. Paul'!B317,"YES","NO")

#### Attachments

• 185.2 KB Views: 13

#### bbureau12

I did. Looks like it didn't take the first time.

#### computerman29642

Give this formula a try...

Code:
``=IF(MATCH(B2,'St. Paul'!B:B,0),"Yes","No")``

#### bbureau12

That's it, thanks!! Have been bashing my head against the wall for a while on this one.

#### computerman29642

I am glad that I could help. Be sure to test the formula thoroughly. You know the spreadsheet better than us.

If you have any further issues, please let us know.

If you think the issue has been resolved, please feel free to mark the thread as solved.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On