Formula to display cell reference (ex.. B7)

I built a formula but I need it to display the cell reference (column letter
& row number), not TRUE or FALSE, of the cell(s) that matches the criterias.

I have provided the formula to demonstrate what I'm trying to do.

=IF(--($F17="B")--($F17="D")--($F17="RS")*AND($M17="no",$O17="no"),"cell
reference")

The cell reference should indicate the cell in column "$F" along with the
row number.

i have no idea what youre trying to do!Take a look at the address,row and
column functions
--
paul
[email protected]
remove nospam for email addy!



"Irv" wrote:

> I built a formula but I need it to display the cell reference (column letter
> & row number), not TRUE or FALSE, of the cell(s) that matches the criterias.
>
> I have provided the formula to demonstrate what I'm trying to do.
>
> =IF(--($F17="B")--($F17="D")--($F17="RS")*AND($M17="no",$O17="no"),"cell
> reference")
>
> The cell reference should indicate the cell in column "$F" along with the
> row number.

Perhaps I didn't explain it clearly.... The result I'm hoping to see is the
location of cell.. Bob Phillips would know what I'm talking about....

"paul" wrote:

> i have no idea what youre trying to do!Take a look at the address,row and
> column functions
> --
> paul
> [email protected]
> remove nospam for email addy!
>
>
>
> "Irv" wrote:
>
> > I built a formula but I need it to display the cell reference (column letter
> > & row number), not TRUE or FALSE, of the cell(s) that matches the criterias.
> >
> > I have provided the formula to demonstrate what I'm trying to do.
> >
> > =IF(--($F17="B")--($F17="D")--($F17="RS")*AND($M17="no",$O17="no"),"cell
> > reference")
> >
> > The cell reference should indicate the cell in column "$F" along with the
> > row number.

perhaps if you post an example of your data and your expected result.I am
thinking that the match function used with address,row and colunn will do
what you want


--
paul
[email protected]
remove nospam for email addy!



"Irv" wrote:

> Perhaps I didn't explain it clearly.... The result I'm hoping to see is the
> location of cell.. Bob Phillips would know what I'm talking about....
>
> "paul" wrote:
>
> > i have no idea what youre trying to do!Take a look at the address,row and
> > column functions
> > --
> > paul
> > [email protected]
> > remove nospam for email addy!
> >
> >
> >
> > "Irv" wrote:
> >
> > > I built a formula but I need it to display the cell reference (column letter
> > > & row number), not TRUE or FALSE, of the cell(s) that matches the criterias.
> > >
> > > I have provided the formula to demonstrate what I'm trying to do.
> > >
> > > =IF(--($F17="B")--($F17="D")--($F17="RS")*AND($M17="no",$O17="no"),"cell
> > > reference")
> > >
> > > The cell reference should indicate the cell in column "$F" along with the
> > > row number.

How do I post an example of the data?..

"paul" wrote:

> perhaps if you post an example of your data and your expected result.I am
> thinking that the match function used with address,row and colunn will do
> what you want
>
>
> --
> paul
> [email protected]
> remove nospam for email addy!
>
>
>
> "Irv" wrote:
>
> > Perhaps I didn't explain it clearly.... The result I'm hoping to see is the
> > location of cell.. Bob Phillips would know what I'm talking about....
> >
> > "paul" wrote:
> >
> > > i have no idea what youre trying to do!Take a look at the address,row and
> > > column functions
> > > --
> > > paul
> > > [email protected]
> > > remove nospam for email addy!
> > >
> > >
> > >
> > > "Irv" wrote:
> > >
> > > > I built a formula but I need it to display the cell reference (column letter
> > > > & row number), not TRUE or FALSE, of the cell(s) that matches the criterias.
> > > >
> > > > I have provided the formula to demonstrate what I'm trying to do.
> > > >
> > > > =IF(--($F17="B")--($F17="D")--($F17="RS")*AND($M17="no",$O17="no"),"cell
> > > > reference")
> > > >
> > > > The cell reference should indicate the cell in column "$F" along with the
> > > > row number.

just in a message,give an example of the data we would see and the answer you
expect/ want.It doesnt have to be actual data,just an illustration
--
paul
[email protected]
remove nospam for email addy!



"Irv" wrote:

> How do I post an example of the data?..
>
> "paul" wrote:
>
> > perhaps if you post an example of your data and your expected result.I am
> > thinking that the match function used with address,row and colunn will do
> > what you want
> >
> >
> > --
> > paul
> > [email protected]
> > remove nospam for email addy!
> >
> >
> >
> > "Irv" wrote:
> >
> > > Perhaps I didn't explain it clearly.... The result I'm hoping to see is the
> > > location of cell.. Bob Phillips would know what I'm talking about....
> > >
> > > "paul" wrote:
> > >
> > > > i have no idea what youre trying to do!Take a look at the address,row and
> > > > column functions
> > > > --
> > > > paul
> > > > [email protected]
> > > > remove nospam for email addy!
> > > >
> > > >
> > > >
> > > > "Irv" wrote:
> > > >
> > > > > I built a formula but I need it to display the cell reference (column letter
> > > > > & row number), not TRUE or FALSE, of the cell(s) that matches the criterias.
> > > > >
> > > > > I have provided the formula to demonstrate what I'm trying to do.
> > > > >
> > > > > =IF(--($F17="B")--($F17="D")--($F17="RS")*AND($M17="no",$O17="no"),"cell
> > > > > reference")
> > > > >
> > > > > The cell reference should indicate the cell in column "$F" along with the
> > > > > row number.

Going back to your original formula, and assuming it was on row 17
somewhere, try this:

=IF(--($F17="B")--($F17="D")--($F17="RS")*AND($M17="no",$O17="no"),"$F"&ROW())

Hope this helps.

Pete

Pete, I believe that that is what I'm looking for.... I've seen this
["$F"&ROW())] in responces to other posts and I thought something like that
would work for what I'm trying to do.... but looing at in now that looks
like the ticket.. Thanx

Coincidently, if you can show me how to also add
("$f17="C")--("$f17="O")*AND($L17="no", $N17="no") to the formula I would
apprciate it. Thanks in advance.

"Pete_UK" wrote:

> Going back to your original formula, and assuming it was on row 17
> somewhere, try this:
>
> =IF(--($F17="B")--($F17="D")--($F17="RS")*AND($M17="no",$O17="no"),"$F"&ROW())
>
> Hope this helps.
>
> Pete
>
>

Well I tried your suggestions, but unfortunately it didn't work... The result
I got was the cell the formula was in. for instance I typed the formula in
cell A2, and when M3 & O3 was equal to "no", the resule was A2. Therefor
"$F"&Row()) shows the cell the formula is in and not the cell F3 where M3 &
O3 is equal to "no".

"Pete_UK" wrote:

> Going back to your original formula, and assuming it was on row 17
> somewhere, try this:
>
> =IF(--($F17="B")--($F17="D")--($F17="RS")*AND($M17="no",$O17="no"),"$F"&ROW())
>
> Hope this helps.
>
> Pete
>
>

Well, I echo Paul's earlier request - can you try to explain what it is
you want to do? I don't understand when you say:

> and not the cell F3 where M3 & O3 is equal to "no".

Pete

It's simple, if cell F17 is ="B" and cell_M17="no" and cell_O17="no", then
the cell where I type the formula, let's say I typed the formula in cell A3
then I want cell A3 to show me cell F17 in red text... You see, one of the
cells, M17 or O17 should ="yes", so if both M17 & O17 ="no", then that's an
error and that's the errors I'm looking to find and correct in the spread
sheet...

"Pete_UK" wrote:

> Well, I echo Paul's earlier request - can you try to explain what it is
> you want to do? I don't understand when you say:
>
> > and not the cell F3 where M3 & O3 is equal to "no".
>
> Pete
>
>