Compare cells in different worksheet in excel

March 10, 2010 at 20:28:06
Specs: Windows XP
I need to check that value entered in any cell of column A in active worksheet is less than or equal to difference between values in A1 and B1 in another worksheet of the same workbook. Later I need to loop the same check. For example value entered in column two of the active worksheet must be compared agains cells A2 and B2 in another worksheet.

See More: Compare cells in different worksheet in excel

Report •


#1
March 14, 2010 at 05:26:38
Sending this to the Office forum, where it has a chance of being answered.

Report •

#2
March 14, 2010 at 06:52:58
Hi,

It is not clear what you mean by value entered in any cell of column A

If you mean test active sheet cell A1 against B1-A1 on other sheet and then test A2 against B2-A2 on other sheet and so on,
try this:
=IF(A1<=Sheet2!B1-Sheet2!A1,"Smaller or equal to the difference","Larger than difference")
Then drag the formula down to do the comparison for active sheet A2 etc.

If the 'other' worksheet name contains a space, surround the name with single quotes.

Here is a modification that returns three states and where the 'other' worksheet name contains a space:
=IF(A1<'St 3'!B1-'St 3'!A1,"Smaller than the difference",IF(A1='St 3'!B1-'St 3'!A1,"Equal to the difference","Larger than the difference"))

If you meant that you want to do a single test to see if the value in any cell in a range of cells in column A on the active sheet is less than or equal to the difference B1-A1, try this:

=IF(MIN(A1:A20)<='St 3'!B1-'St 3'!A1,"At least one cell is smaller or equal to the difference","All are larger than the difference")

You could include the number that are equal to or less than by counting the number with COUNTIF():
=IF(MIN(A1:A20)<='St 3'!B1-'St 3'!A1,COUNTIF(A1:A20,("<=" & 'St 3'!B1-'St 3'!A1)) & " cell(s) is/are smaller or equal to the difference","All are larger than the difference")

Regards


Report •
Related Solutions


Ask Question