How to get Excel forumla

[Aldous]

I am seeking to get a cell reference (row and column) for the cell in which particular text is found. I need an excel formula to find the part of the text in the range of cells and display the values. Essentially, I would like to be able to do a vlookup() but instead of getting the value from a different column, I'd like to get the row number. How do you make the silly thing count to the next number....from 1 and the next time you open it up it will be on 2.

[Adley]

You can use the MATCH formula for that. It returns the position of the matched result on the searched range, so if you use the full column (or a range starting on the first row), it will return the row number where the match is. For example:

=INDEX(A1,B:B,0);=AVERAGE(LARGE(A1:A60,ROW(INDIRECT("1:10"))))

you can try to call the method with with an empty/dummy workbook reference, it MAY work for formulas without Names or 3D references, but be sure to catch all exceptions if you do that.

[Timothi]

I used the following formula,It seems to be working but only to that cell if i try to put the same formula in another sheet or another cell ,I don't see the values.

My Formula is as follows.
=IF((OR(A1:A25="ARRA 123",A1:A25="NoN ARRA 123")),B1:B25,0)

If you want to copy a range of formulas without changing the references, you can use "Replace With", CNTRL H.

[StudyBoy]

However, since formula's contain references to other parts of the file, you'll need a reference to a workbook to get a string representation. I'm not sure if you're interested at all, but I found out what the problem was. When you specify the formula with the dynamic range you must put the dynamic range in quotes so instead of having ActiveCell.Formula = "=MEDIAN(Patientrange)" you must have ActiveCell.Formula = "=MEDIAN(" & Patientrange & ")". But now you are saying that you want values in B so it means you want to see column A values in B then write below formula in B.

[BertieStan]

The best way to do this, assuming your explanation is not a vast over simplification of your real problem, would be with a pivot table.Select your range (columns must have unique labels) go to pivot table (data->pivot table in excel 2003) and put height on the left axis and count of height in the data area, click finish and that should do you.