Get reference to first row of a range

[christhedonstar]

Hi,

What worksheet formula can I use to get the reference to the first row of a range?

Thanks,

Chris

 

[xlbo]

Surely if you know the range, you know the 1st row...???

How is the range to be determined?

If you have a range address e.g. "$A$5:%G$100 or a range name then

=row(RangeName) or Row(RangeAddress) will return the number of the 1st row in the range...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[christhedonstar]

Returning the number doesn't return a reference to it though does it?

I mean say I have

Geoff Chris Tony
happy TRUE TRUE FALSE
Age 25 11 1

the whole range is called people_stuff I want a reference to the first row. A function that will do that. I could write a UDF but I want to know if there is something existing already.

Thanks,

Chris

 

[GlennUK]

=OFFSET(people_stuff,0,0,1,)

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[xlbo]

As per Glenn but having the row number allows you to create a reference....

=indirect("A" & row(people_stuff))

Will return the data in col A, for whatever row your range name starts in...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[christhedonstar]

They're both volatile aren't they?

 

[christhedonstar]

I should have said non volatile functions.

 

[GlennUK]

You still haven't said why you want this.

=INDEX(A:A,ROW(people_stuff))

might do want you want, as a guess.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[christhedonstar]

Its for a table lookup. Previously was using vlookup and match on a seperate header range to do it. I'll try your suggestion...

 

[christhedonstar]

Actually I don't quite understand what you've sent me.

 

[GlennUK]

Explain what your table lookup is supposed to do, and we'll all try to suggest a solution. E.g. show us what you had previously, and what you intended to change it to.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[christhedonstar]

table is

a b c
1 ; ' #
2 [ ] %
3 $ % !

if I want the result from looking up under b and 2 (which is ])

Then I'll have a range called my "my_range" which has the rows except the (a b c) heading. Then I have another range called my_range_headings then I'll do something like

vlookup(2, my_range, match("b", my_range_headings,0),0)

I want to not have have to define my_range_headings hence my original question.

Thanks,

Chris

 

[GlennUK]

Can you say why this must be done with non volatile functions? And why can you not define my_range_headings? Defining the headings is such a simple solution, and the next most simple solution is using OFFSET.

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[SkipVought]

[tt]
=INDEX(My_Range,2, MATCH("b", my_range_headings,0))
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[christhedonstar]

the data which the lookups are done on doesn't change, and I'm amending a massive file, I don't want to carry on doing lots of recalculations uncessesarily so I have to be careful what functions I use.

SkipVought is yours more efficient than mine? (still uses headings though)