Offset function problems

[Karen]

Your problem is that the OFFSET function requires the
first argument be a cell address, not the value in the
cell whose address you want. You need to do a little
fancy footwork to return the cell address of the lookup
equation. Try this:

=OFFSET(INDIRECT(ADDRESS(4,MATCH(HLOOKUP($A$2,'[Cross
Country Tracker.xls]Sign-up Roster'!
$E$2:$HM$2,1,FALSE),'[Cross Country Tracker.xls]Sign-up
Roster'!$E$2:$HM$2,0)+COLUMN('[Cross Country Tracker.xls]
Sign-up Roster'!$E$2:$HM$2)-1)),0,2)

Hope that helps (I got it to work for me, except without
the '[Cross Country Tracker.xls]Sign-up Roster'! part),
Karen

 

[Patrick]

I thought that might be the problem, but I was using the
CELL address function to try to return the cell reference,
but it wasn't working. I'll try the indirect....Thanks.

-----Original Message-----
Your problem is that the OFFSET function requires the
first argument be a cell address, not the value in the
cell whose address you want. You need to do a little
fancy footwork to return the cell address of the lookup
equation. Try this:

=OFFSET(INDIRECT(ADDRESS(4,MATCH(HLOOKUP($A$2,'[Cross
Country Tracker.xls]Sign-up Roster'!
$E$2:$HM$2,1,FALSE),'[Cross Country Tracker.xls]Sign-up
Roster'!$E$2:$HM$2,0)+COLUMN('[Cross Country Tracker.xls]
Sign-up Roster'!$E$2:$HM$2)-1)),0,2)

Hope that helps (I got it to work for me, except without
the '[Cross Country Tracker.xls]Sign-up Roster'! part),
Karen

-----Original Message-----
I cannot get this formula to work, and don't know why:

=offset(HLOOKUP($A$2,'[Cross Country Tracker.xls]Sign-up
Roster'!$E$2:$HM$70,3,FALSE),0,2)

The hlookup function works, and returns a value "J", which
is what I want. Now I need the next cell to return the
value in the cell 2 columns over. Any chance?
.

.

 

[Patrick]

Well, didn't work for me. Not sure what all the addition
and subtraction amounted to. ended up referring to an
empty cell. Surely there's a way to return the cell
reference....

-----Original Message-----
Your problem is that the OFFSET function requires the
first argument be a cell address, not the value in the
cell whose address you want. You need to do a little
fancy footwork to return the cell address of the lookup
equation. Try this:

=OFFSET(INDIRECT(ADDRESS(4,MATCH(HLOOKUP($A$2,'[Cross
Country Tracker.xls]Sign-up Roster'!
$E$2:$HM$2,1,FALSE),'[Cross Country Tracker.xls]Sign-up
Roster'!$E$2:$HM$2,0)+COLUMN('[Cross Country Tracker.xls]
Sign-up Roster'!$E$2:$HM$2)-1)),0,2)

Hope that helps (I got it to work for me, except without
the '[Cross Country Tracker.xls]Sign-up Roster'! part),
Karen

-----Original Message-----
I cannot get this formula to work, and don't know why:

=offset(HLOOKUP($A$2,'[Cross Country Tracker.xls]Sign- up
Roster'!$E$2:$HM$70,3,FALSE),0,2)

The hlookup function works, and returns a value "J", which
is what I want. Now I need the next cell to return the
value in the cell 2 columns over. Any chance?
.

.

 

[Karen]

I forgot that, in the end, the INDIRECT function still
needs text pointing it to the other book too!!! This
should work:

=OFFSET(INDIRECT("'[Cross Country Tracker.xls]Sign-up
Roster'!"&ADDRESS(4,MATCH(HLOOKUP($A$2,'[Cross Country
Tracker.xls]Sign-up Roster'!$E$2:$HM$2,1,FALSE),'[Cross
Country Tracker.xls]Sign-up Roster'!$E$2:$HM$2,0)+COLUMN
('[Cross Country Tracker.xls]Sign-up Roster'!$E$2:$HM$2)-
1)),0,2)

-----Original Message-----
Well, didn't work for me. Not sure what all the addition
and subtraction amounted to. ended up referring to an
empty cell. Surely there's a way to return the cell
reference....

-----Original Message-----
Your problem is that the OFFSET function requires the
first argument be a cell address, not the value in the
cell whose address you want. You need to do a little
fancy footwork to return the cell address of the lookup
equation. Try this:

=OFFSET(INDIRECT(ADDRESS(4,MATCH(HLOOKUP($A$2,'[Cross
Country Tracker.xls]Sign-up Roster'!
$E$2:$HM$2,1,FALSE),'[Cross Country Tracker.xls]Sign-up
Roster'!$E$2:$HM$2,0)+COLUMN('[Cross Country Tracker.xls]
Sign-up Roster'!$E$2:$HM$2)-1)),0,2)

Hope that helps (I got it to work for me, except without
the '[Cross Country Tracker.xls]Sign-up Roster'! part),
Karen

-----Original Message-----
I cannot get this formula to work, and don't know why:

=offset(HLOOKUP($A$2,'[Cross Country Tracker.xls]Sign- up
Roster'!$E$2:$HM$70,3,FALSE),0,2)

The hlookup function works, and returns a value "J", which
is what I want. Now I need the next cell to return the
value in the cell 2 columns over. Any chance?
.

.

.