Excel Copy Based On Criteria

November 24, 2009 at 06:15:07
Specs: Windows XP
Hi all,

I would like to know which formula can I use to return the cell number if the citeria matched? e.g.

A1 = 1
A2 = 2
A3 = 3

I would like to know the cell number where any cell in column A is equal to 1

See More: Excel Copy Based On Criteria

Report •

November 24, 2009 at 07:32:51

I am not clear what you want.

In your example do you want the formula to return the value "A1", because cell A1 contains the value 1,
do you want the formula to return 1, because there is only one cell that contains the value 1.
or something else?

When you say any cell in column A is equal to 1, does this mean that there can be multiple cells in column A that contain 1,
if so, do you want the formula to return the number of cells containing 1.


Report •

November 25, 2009 at 03:13:46
I wanted the cell number which is A1 not the content of the cell.

If it is possible to return multiple cell reference number then it would be better.

I no need the number of cell containing 1.


Report •

November 25, 2009 at 05:04:55

I used the following data starting in cell A1:

1	43
2	8
3	18
4	12
5	4
6	15
7	8

In another cell enter this formula:
which returns the value 6, which is the position of the cell containing the number 15.
If you wanted the address of the cell containing 15 then you could use the following formula which includes the address of the first cell in the range:
This formula returned the value $A$6.

Hope this was what you were looking for.

I can't think of a formula that would return multiple cell addresses. You could use COUNTIF() to test if there is more than one cell in the range with the value you want.

This formula returns the value 2:



Report •

Related Solutions

November 26, 2009 at 07:01:23
Thanks, the formulas works if the matching citeria is number or text, but I am looking for error in the cell. In another word, I am looking for cell with "#Value!" or "#Ref!", and the formula cannot work already. I think is bcos of the "!" which cause the formula to have error.

Anymore solution???

Report •

November 26, 2009 at 08:19:39

You didn't mention searching for a cell with an error value before!

Excel has a function that returns TRUE if the cell contains an error:
If cell B5 contains the error value #VALUE!, this formula

=IF(ISERROR(B5),"Error", "No error")
will return a text message "Error", but if B4 does not contain an error, it will return the text "No error".

ISERROR(B5) on its own will return the logical values TRUE or FALSE

I am not sure what you mean by I think is bcos of the "!" which cause the formula to have error.

1. the ! is part of the error text, and it is not a separate character.
2. Which formula has the error. What does 'the formula' refer to?
3. An error message in a cell is there because the formula in the cell either has an error in it, or a cell it refers to has an error in it, or the function returns an error message by design, such as VLOOKUP when it does not find a match.
4. You can't search for error messages as though they are text. =IF(B5="#VALUE!,"Value error", "No Match") will not work even if cell B5 contains #VALUE!



Report •

November 26, 2009 at 18:16:17
ok, thanks...
so =IF(ISERROR(B5),"Error", "No error") is able to detect error but it cannot return me the cell reference.

What I need is to know the cell number. So if one of the cell in B columns got error I need which which cell got the error. So if B5 in column B got error I need to know is B5.

Report •

November 27, 2009 at 07:22:14

I don't know of a formula that will return the cell address of a cell containing an error.

Someone else may have an answer.

As a result I have written a user defined function specifically to return the address of a cell
containing an error.

The function is FindErr()
It takes two arguments. The first is the range of cells to search and the second which is optional
and defaults to 1 is the incidence of a cell error.

If cells A1 to A10 contain two cells with errors, A5 and A8, then:
=FindErr(A1:A10) will return "A5"
=FindErr(A1:A10, 2) will return "A8"
If there is no error in the range of cells, FindErr returns the #NA error.

Here is the code for the function which should be placed in a standard module in your workbook:

Option Explicit

Public Function FindErr(rngSearch As Range, Optional intErrOcc As Integer = 1) As Variant
Dim rngCell As Range
Dim n As Integer
n = 1
For Each rngCell In rngSearch.Cells
    If IsError(rngCell.Value) Then
        If n = intErrOcc Then
            FindErr = rngCell.Address
            Exit Function
        End If
        n = n + 1
    End If
Next rngCell
'No error found so return NA error
FindErr = CVErr(xlErrNA)
End Function

FindErr can be incorporated into other formulas.
Note that as this is a custom function and is in a module in the Workbook it will only be available
in that workbook, or copies of that workbook.


Report •

November 27, 2009 at 09:49:25
The =CELL() function will return the cell address using:


So somthing along the lines of:

=IF(ISERROR(B5),CELL("address",B5), "No error")

might get you what your looking for.



Report •

December 1, 2009 at 20:34:09
Thanks Humar...your solution may be a bit complicated but it works...

Thanks mike as well although your solution is not the one i looking for.

Report •

Ask Question