Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset of a minumum | Excel Worksheet Functions | |||
Offset Function | Excel Worksheet Functions | |||
Offset Function | Excel Worksheet Functions | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Question for use of offset and range | Excel Worksheet Functions |