How 2 make excel return a cell location (ie.C3) of the first instance a number is over X.Need 2 create a range?

Question

Anyone know how to create a defined range in Excel? For example, lets say I wanted a range of only rows that had "cats" in it. Here's what excel looks like:
A
1 CAT
2 CAT
3 DOG
4 DOG
I need it to return the text of the cell location of A2 - when cats no longer appear any more.

So in the formula box that is reading in the array of all the contents of the A row, it knows to stop reading after it hits a non-CAT cell and simply say "A2" in the result.

The answer is going to have something to do wtih an IF statement (to determine if its a cat or not), and then finally a way to address/refernce the cell that it happened in so I can use that cell location to create a range w/ my other formulas.

Answer 1

its a little more complicated then a simple if() statement

copy&paste this formula to find "dog"



="A"&MATCH("dog",A:A, FALSE)+ IF(COUNTIF(A:A,"dog")=1,0, COUNTIF(A:A,"dog")-1)



and you should get A4



edit-

bomb is right, the sorting matters

also the cell reference would make it easier to copy&paste



but you need the if() statement to count if theres more then1 instance of what you are looking for. if theres just 1, then the if statement will make the correction.

Answer 2

Cell Location Excel

Answer 3

Important: is your data sorted that way (all CATs, then all DOGs, etc.)?



If C1 = "CAT", then:



="A"&MATCH(C1,A:A,0)+

COUNTIF(A:A,C1)-1



returns "A2"; but if C1 = "DOG", it returns "A4".



However, the value is returned as a string. If you need to make it function as a value, you'll have to wrap it with the INDIRECT function.



Do you actually want a dynamic named range that automatically adjusts according a value entered in a "control" cell?



Edit: if you need the dynamic named range, do this.



Press CTRL+F3. In "Names in workbook" type Data. Copy the following formula into "Refers to":



=OFFSET(Sheet1!$A$1,

MATCH(Sheet1!$C$1,

Sheet1!$A:$A,0)-1,0,

COUNTIF(Sheet1!$A:$A,

Sheet1!$C$1),1)



then click OK. In the worksheet type CAT in C1. Press F5, type Data, press Enter. A1:A2 will be selected. Change C1 to DOG. Press F5, type Data, press Enter. A3:A4 will be selected. Thus the named range is dynamic based on the input at C1.

Answer 4

1. Nick's friend 2. hug from Nick. 3. romantic date with the JB of my dream. 4. real phone number 5. date them fro a whole day!