Roger has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Referring to cells in a range
I'm trying to avoid using VBA for something...
I want to find the value in the first cell in a named range. I think I can do this with Range.Cells in VBA, but is there a way to do it on the spreadsheet alone?
If it's called TestRange, I guess I am looking for something like "=TestRange(Cell1)", but I can find no evidence that such a thing exists.
Any suggestions gratefully received.
Roger
RE: referring to cells in a range
Morning Roger
Yes, you can find a value in a range without using VBA by using the INDEX function.
In any cell of your workbook containing a range called TestRange try typing:
=INDEX(TestRange,1,1)
The Index function returns the value at the position row 1, col 1 within the range TestRange.
Hope that helps.
Other example of Index
Index is sometimes used to find a value if a position in a range in known (throught the Match function)
A B
John 250
Sue 400
Rich 120
Val 300
Example
To find the person with the highest value without sorting type:
=INDEX(A1:A4,MATCH(MAX(B1:B4),B1:B4,0),1)
The Match function finds the postion 2 for Max value in B1:B4 and the Index function returns the value in that row of the range A1:A4 which is Sue!
Regards
Doug
Doug Dunn
Best STL
