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

Thread Starter
Joined
Sep 18, 2008
Messages
6
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?
 
Joined
Dec 4, 2007
Messages
2,895
So, the formula will always compare data to cell B1 of Sheet1? Did you copy down the formula?

Could you attach a sample file?
 

bbureau12

Thread Starter
Joined
Sep 18, 2008
Messages
6
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

bbureau12

Thread Starter
Joined
Sep 18, 2008
Messages
6
That's it, thanks!! Have been bashing my head against the wall for a while on this one.
 
Joined
Dec 4, 2007
Messages
2,895
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.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Staff online

Top