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.

Sending this to the Office forum, where it has a chance of being answered.

Hi, It is not clear what you mean by

value entered in any cell of column A

Ifyou 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"))

Ifyou 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

Ask Your Question

Weekly Poll

Do you think Microsoft's new Surface Go will be a hit?

Discuss in The Lounge

Poll History