|  | Can the cell function return "B2" instead of "$B$2"? | | |
| | | Square Peg |  |
|  Posted: Sun Sep 07, 2008 1:33 am Post subject: Can the cell function return "B2" instead of "$B$2"? |  |
Is there a way to get the Cell function to return just the column and
row without the "$" signs?
=cell("address",maxrate) --> "$B$2"
I would like just "B2". |
| |
| | | Héctor Miguel | |
| | Posted: Sun Sep 07, 2008 1:49 am Post subject: Re: Can the cell function return "B2" instead of "$B$2"? | |
hi, !
| Quote: | Is there a way to get the Cell function to return just the column and row without the "$" signs?
=cell("address",maxrate) --> "$B$2"
I would like just "B2".
|
is this a valid alternate ?
=substitute(cell("address",maxrate),"$","")
hth,
hector. |
| |
| | | Ron Rosenfeld | |
| | Posted: Sun Sep 07, 2008 1:55 am Post subject: Re: Can the cell function return "B2" instead of "$B$2"? | |
On Sat, 06 Sep 2008 20:33:46 -0700, Square Peg <SquarePeg@Round.Hole> wrote:
| Quote: | Is there a way to get the Cell function to return just the column and
row without the "$" signs?
=cell("address",maxrate) --> "$B$2"
I would like just "B2".
|
=SUBSTITUTE(CELL("address",maxrate),"$","")
--ron |
| |
| | | Rick Rothstein | |
| | Posted: Sun Sep 07, 2008 1:55 am Post subject: Re: Can the cell function return "B2" instead of "$B$2"? | |
You could do it without using the CELL function...
=ADDRESS(ROW(maxrate),COLUMN(maxrate),4)
--
Rick (MVP - Excel)
"Square Peg" <SquarePeg@Round.Hole> wrote in message
news:fpi6c4d2n2rvcagvvhtkgvvojrh5440q9d@4ax.com...
| Quote: | Is there a way to get the Cell function to return just the column and
row without the "$" signs?
=cell("address",maxrate) --> "$B$2"
I would like just "B2". |
|
| |
| | | Rick Rothstein | |
| | Posted: Sun Sep 07, 2008 2:20 am Post subject: Re: Can the cell function return "B2" instead of "$B$2"? | |
I would also note that CELL is a volatile function whereas ADDRESS, ROW and
COLUMN are not.
--
Rick (MVP - Excel)
"Rick Rothstein" <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote in message
news:%231GtL2JEJHA.952@TK2MSFTNGP03.phx.gbl...
| Quote: | You could do it without using the CELL function...
=ADDRESS(ROW(maxrate),COLUMN(maxrate),4)
--
Rick (MVP - Excel)
"Square Peg" <SquarePeg@Round.Hole> wrote in message
news:fpi6c4d2n2rvcagvvhtkgvvojrh5440q9d@4ax.com...
Is there a way to get the Cell function to return just the column and
row without the "$" signs?
=cell("address",maxrate) --> "$B$2"
I would like just "B2".
|
|
| |
| | | Héctor Miguel | |
| | Posted: Sun Sep 07, 2008 3:13 am Post subject: Re: Can the cell function return "B2" instead of "$B$2"? | |
hi, Rick !
I'm curious, *IF* OP uses volatile functions to define "maxrate"...
- would it be better using only one *call* to the name, even being CELL a volatile function ?
- or definitely its better two *calls* to the name through non-volatile functions (row & column) ?
(just wondering)
regards,
hector.
| Quote: | I would also note that CELL is a volatile function whereas ADDRESS, ROW and COLUMN are not.
--
Rick (MVP - Excel)
"Rick Rothstein wrote in message ...
You could do it without using the CELL function...
=ADDRESS(ROW(maxrate),COLUMN(maxrate),4)
--
Rick (MVP - Excel)
Square Peg wrote in message ...
Is there a way to get the Cell function to return just the column and row without the "$" signs?
=cell("address",maxrate) --> "$B$2"
I would like just "B2". |
|
| |
| | | T. Valko | |
| | Posted: Sun Sep 07, 2008 3:44 am Post subject: Re: Can the cell function return "B2" instead of "$B$2"? | |
If your intention is to use B2 in an INDIRECT function it will make no
difference since INDIRECT will process B2 as $B$2.
--
Biff
Microsoft Excel MVP
"Square Peg" <SquarePeg@Round.Hole> wrote in message
news:fpi6c4d2n2rvcagvvhtkgvvojrh5440q9d@4ax.com...
| Quote: | Is there a way to get the Cell function to return just the column and
row without the "$" signs?
=cell("address",maxrate) --> "$B$2"
I would like just "B2". |
|
| |
| | | Square Peg | |
| | Posted: Sun Sep 07, 2008 4:14 am Post subject: Re: Can the cell function return "B2" instead of "$B$2"? | |
On Sat, 6 Sep 2008 23:55:18 -0400, "Rick Rothstein"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:
| Quote: | You could do it without using the CELL function...
=ADDRESS(ROW(maxrate),COLUMN(maxrate),4)
|
That works perfectly, thanks. |
| |
| | | Square Peg | |
| | Posted: Sun Sep 07, 2008 4:15 am Post subject: Re: Can the cell function return "B2" instead of "$B$2"? | |
On Sun, 7 Sep 2008 00:20:52 -0400, "Rick Rothstein"
<rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:
| Quote: | I would also note that CELL is a volatile function whereas ADDRESS, ROW and
COLUMN are not.
|
I didn;t know about "volatile" functions. I just looked them up. Why
is Cell volatile and Address not? At least in this example, they are
doing the same work, no? |
| |
| | | Square Peg | |
| | Posted: Sun Sep 07, 2008 4:16 am Post subject: Re: Can the cell function return "B2" instead of "$B$2"? | |
On Sun, 7 Sep 2008 01:44:27 -0400, "T. Valko" <biffinpitt@comcast.net>
wrote:
| Quote: | If your intention is to use B2 in an INDIRECT function it will make no
difference since INDIRECT will process B2 as $B$2.
|
I am just trying to display the cell address in some instructions,
like, "Enter the maximum rate in cell B2". |
| |
| Page 1 of 2 .:. Goto page 1, 2 Next | |
|
|
