OFFSET PLEASE HELP!
 

what's wrong with this formula?

=offset(curbal,0,-1,1,1)

I'm trying to get the data of the cell ON THE LEFT of the one named "Curbal"

ie:

a b c d
1 x Curbal
2 9

I don't know where is going to be located neither the name of the column the
only thing I know its going to be on row 1 and is going to be on the left of
the one named "Curbal"
So in this example the number I want to get is 9

OFFSET PLEASE HELP!
 

If
You actually have a single cell with the range name "curbal"
AND
that cell is always in Row_1
AND
you want to return the value that is 1 cell to the left and 1 cell down from
curbal

then try this:
=OFFSET(curbal,1,-1,1,1)

If curbal is in cell F1
that formula will return the value from E2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"HERNAN" wrote:

what's wrong with this formula?

=offset(curbal,0,-1,1,1)

I'm trying to get the data of the cell ON THE LEFT of the one named "Curbal"

ie:

a b c d
1 x Curbal
2 9

I don't know where is going to be located neither the name of the column the
only thing I know its going to be on row 1 and is going to be on the left of
the one named "Curbal"
So in this example the number I want to get is 9

OFFSET PLEASE HELP!
 

I don't think they are using a "Named" cell, but I could be wrong.

If your value to find is in column B, you might try something like:

=INDIRECT("A"&MATCH("curbal",B:B,0))

Regards,
Paul

"Ron Coderre" wrote in message
...
If
You actually have a single cell with the range name "curbal"
AND
that cell is always in Row_1
AND
you want to return the value that is 1 cell to the left and 1 cell down
from
curbal

then try this:
=OFFSET(curbal,1,-1,1,1)

If curbal is in cell F1
that formula will return the value from E2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"HERNAN" wrote:

what's wrong with this formula?

=offset(curbal,0,-1,1,1)

I'm trying to get the data of the cell ON THE LEFT of the one named
"Curbal"

ie:

a b c d
1 x Curbal
2 9

I don't know where is going to be located neither the name of the column
the
only thing I know its going to be on row 1 and is going to be on the left
of
the one named "Curbal"
So in this example the number I want to get is 9

OFFSET PLEASE HELP!
 

If you're looking for the number 9, don't you need to be one cell down, as
well as one cell to the left of Curbal?
=offset(curbal,1,-1,1,1)

Your original formula would have given x, not 9.
--
David Biddulph

"HERNAN" wrote in message
...
what's wrong with this formula?

=offset(curbal,0,-1,1,1)

I'm trying to get the data of the cell ON THE LEFT of the one named
"Curbal"

ie:

a b c d
1 x Curbal
2 9

I don't know where is going to be located neither the name of the column
the
only thing I know its going to be on row 1 and is going to be on the left
of
the one named "Curbal"
So in this example the number I want to get is 9

OFFSET PLEASE HELP!
 

Well.....if that's true, then I think this would be the less "volatile"
approach <g

=INDEX($2:$2,MATCH("curbal",$1:$1,0)-1)

***********
Regards,
Ron

XL2002, WinXP


"PCLIVE" wrote:

I don't think they are using a "Named" cell, but I could be wrong.

If your value to find is in column B, you might try something like:

=INDIRECT("A"&MATCH("curbal",B:B,0))

Regards,
Paul

"Ron Coderre" wrote in message
...
If
You actually have a single cell with the range name "curbal"
AND
that cell is always in Row_1
AND
you want to return the value that is 1 cell to the left and 1 cell down
from
curbal

then try this:
=OFFSET(curbal,1,-1,1,1)

If curbal is in cell F1
that formula will return the value from E2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"HERNAN" wrote:

what's wrong with this formula?

=offset(curbal,0,-1,1,1)

I'm trying to get the data of the cell ON THE LEFT of the one named
"Curbal"

ie:

a b c d
1 x Curbal
2 9

I don't know where is going to be located neither the name of the column
the
only thing I know its going to be on row 1 and is going to be on the left
of
the one named "Curbal"
So in this example the number I want to get is 9

OFFSET PLEASE HELP!
 

I'm sure you're right, but I was going for a creative and probably
unorthodox approach. i also didn't take in account, as you did, that he
wanted the result of 9 which was indeed on the next row.

Thanks Ron. You're always a big help on these forums.


"Ron Coderre" wrote in message
...
Well.....if that's true, then I think this would be the less "volatile"
approach <g

=INDEX($2:$2,MATCH("curbal",$1:$1,0)-1)

***********
Regards,
Ron

XL2002, WinXP


"PCLIVE" wrote:

I don't think they are using a "Named" cell, but I could be wrong.

If your value to find is in column B, you might try something like:

=INDIRECT("A"&MATCH("curbal",B:B,0))

Regards,
Paul

"Ron Coderre" wrote in message
...
If
You actually have a single cell with the range name "curbal"
AND
that cell is always in Row_1
AND
you want to return the value that is 1 cell to the left and 1 cell down
from
curbal

then try this:
=OFFSET(curbal,1,-1,1,1)

If curbal is in cell F1
that formula will return the value from E2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"HERNAN" wrote:

what's wrong with this formula?

=offset(curbal,0,-1,1,1)

I'm trying to get the data of the cell ON THE LEFT of the one named
"Curbal"

ie:

a b c d
1 x Curbal
2 9

I don't know where is going to be located neither the name of the
column
the
only thing I know its going to be on row 1 and is going to be on the
left
of
the one named "Curbal"
So in this example the number I want to get is 9

OFFSET PLEASE HELP!
 

THANK YOU! the only change I'll have to change B;B because I don't know the
Column, I only know the row where it will be.
Thanks ALL!!



"PCLIVE" wrote:

I don't think they are using a "Named" cell, but I could be wrong.

If your value to find is in column B, you might try something like:

=INDIRECT("A"&MATCH("curbal",B:B,0))

Regards,
Paul

"Ron Coderre" wrote in message
...
If
You actually have a single cell with the range name "curbal"
AND
that cell is always in Row_1
AND
you want to return the value that is 1 cell to the left and 1 cell down
from
curbal

then try this:
=OFFSET(curbal,1,-1,1,1)

If curbal is in cell F1
that formula will return the value from E2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"HERNAN" wrote:

what's wrong with this formula?

=offset(curbal,0,-1,1,1)

I'm trying to get the data of the cell ON THE LEFT of the one named
"Curbal"

ie:

a b c d
1 x Curbal
2 9

I don't know where is going to be located neither the name of the column
the
only thing I know its going to be on row 1 and is going to be on the left
of
the one named "Curbal"
So in this example the number I want to get is 9