Is there a formula that returns the address / row / column of the active cell?

So i can use this to produce different results based on the active cell.

PaulW

As far as I know you have to create one or more UDF's for this purpose like
this:

Function ActCellAddr()
Application.Volatile
ActCell = ActiveCell.Address(False, False)
End Function

or this:
Function ActCellRow()
Application.Volatile
ActCell = ActiveCell.Row
End Function

Regards,
Stefi


€žPaulW€� ezt Ã*rta:

Is there a formula that returns the address / row / column of the active cell?

So i can use this to produce different results based on the active cell.

PaulW

Hi Paul

in VBA yes, you can use
activecell.address
activecell.row
activecell.column

as a Worksheet formula
=ROW() will return the row number of the cell in which you type the
formula
=COLUMN() will return the column number

In a formula, I think you might be needing the Offset() function as
=CELL("address",C7), will return $C$7 but in that case, you have fed it
the location, so therefore you know the address.
=CELL("address"OFFSET(A1,2,6) will also return $C$7

can you give an example of what you are trying to achieve?

--
Regards

Roger Govier


"PaulW" wrote in message
...
Is there a formula that returns the address / row / column of the
active cell?

So i can use this to produce different results based on the active
cell.

PaulW

Cell B2 = Postcode Search

Cell C2 returns the first line of address from vlookup / indirect address
stuff like that. i have one cell that looks up the line of the address from
another workbook that feeds which address to pull up. All this based on the
postcode entered.

if the active cell highlights C2 i wish for that full address to appear, if
C3 is highlighted full address for that also etc etc.

I'm trying to avoid VBA as there is no real trigger for the macro.

I'll take a look at what your suggsting below and let you know!!

Thanks Roger.

"Roger Govier" wrote:

Hi Paul

in VBA yes, you can use
activecell.address
activecell.row
activecell.column

as a Worksheet formula
=ROW() will return the row number of the cell in which you type the
formula
=COLUMN() will return the column number

In a formula, I think you might be needing the Offset() function as
=CELL("address",C7), will return $C$7 but in that case, you have fed it
the location, so therefore you know the address.
=CELL("address"OFFSET(A1,2,6) will also return $C$7

can you give an example of what you are trying to achieve?

--
Regards

Roger Govier


"PaulW" wrote in message
...
Is there a formula that returns the address / row / column of the
active cell?

So i can use this to produce different results based on the active
cell.

PaulW

Thanks for the suggestion but that wont work for what im trying to atchieve!

Any other help gladly recieved!!!

"Roger Govier" wrote:

Hi Paul

in VBA yes, you can use
activecell.address
activecell.row
activecell.column

as a Worksheet formula
=ROW() will return the row number of the cell in which you type the
formula
=COLUMN() will return the column number

In a formula, I think you might be needing the Offset() function as
=CELL("address",C7), will return $C$7 but in that case, you have fed it
the location, so therefore you know the address.
=CELL("address"OFFSET(A1,2,6) will also return $C$7

can you give an example of what you are trying to achieve?

--
Regards

Roger Govier


"PaulW" wrote in message
...
Is there a formula that returns the address / row / column of the
active cell?

So i can use this to produce different results based on the active
cell.

PaulW

Hi Paul

Can you show the formulae you are using, with a sample of the data?

--
Regards

Roger Govier


"PaulW" wrote in message
...
Cell B2 = Postcode Search

Cell C2 returns the first line of address from vlookup / indirect
address
stuff like that. i have one cell that looks up the line of the address
from
another workbook that feeds which address to pull up. All this based
on the
postcode entered.

if the active cell highlights C2 i wish for that full address to
appear, if
C3 is highlighted full address for that also etc etc.

I'm trying to avoid VBA as there is no real trigger for the macro.

I'll take a look at what your suggsting below and let you know!!

Thanks Roger.

"Roger Govier" wrote:

Hi Paul

in VBA yes, you can use
activecell.address
activecell.row
activecell.column

as a Worksheet formula
=ROW() will return the row number of the cell in which you type the
formula
=COLUMN() will return the column number

In a formula, I think you might be needing the Offset() function as
=CELL("address",C7), will return $C$7 but in that case, you have fed
it
the location, so therefore you know the address.
=CELL("address"OFFSET(A1,2,6) will also return $C$7

can you give an example of what you are trying to achieve?

--
Regards

Roger Govier


"PaulW" wrote in message
...
Is there a formula that returns the address / row / column of the
active cell?

So i can use this to produce different results based on the active
cell.

PaulW

A2 = "S"
C2:C10 then provide a a list of buildings within the "S" postcode (this is
done mostly via Vlookup)
D2:D10 is the full address of the first building, dodgy drawing :p

A B C D
S Building 1 Building 1
Building 2 45 Some Street
Building 3 Random Town
Building 4 A City
Building 5 Tel number 1

What i'm wanting is a quick and easy way to change column D to give a
different address. All the information in Column D is from Vlookups, to look
at different columns, searching from C2.

I'm sure at one point I had a formula, or something, so the cell reference
of the active cell was displayed. Thus, if C2 was the active cell, then I
could have the Vlookup(indirect(M4),etc so the address can be changed
effortlessly.

So basically i'm wanting to know in a cell, what the address of the active
cell is, so I can use it in an INDIRECT withing a VLOOKUP

"Roger Govier" wrote:

Hi Paul

Can you show the formulae you are using, with a sample of the data?

--
Regards

Roger Govier


"PaulW" wrote in message
...
Cell B2 = Postcode Search

Cell C2 returns the first line of address from vlookup / indirect
address
stuff like that. i have one cell that looks up the line of the address
from
another workbook that feeds which address to pull up. All this based
on the
postcode entered.

if the active cell highlights C2 i wish for that full address to
appear, if
C3 is highlighted full address for that also etc etc.

I'm trying to avoid VBA as there is no real trigger for the macro.

I'll take a look at what your suggsting below and let you know!!

Thanks Roger.

"Roger Govier" wrote:

Hi Paul

in VBA yes, you can use
activecell.address
activecell.row
activecell.column

as a Worksheet formula
=ROW() will return the row number of the cell in which you type the
formula
=COLUMN() will return the column number

In a formula, I think you might be needing the Offset() function as
=CELL("address",C7), will return $C$7 but in that case, you have fed
it
the location, so therefore you know the address.
=CELL("address"OFFSET(A1,2,6) will also return $C$7

can you give an example of what you are trying to achieve?

--
Regards

Roger Govier


"PaulW" wrote in message
...
Is there a formula that returns the address / row / column of the
active cell?

So i can use this to produce different results based on the active
cell.

PaulW

Hi Paul

Maybe
D2=C2
D3=Vlookup(offset(d3,-Row(1:1),0),Address,row(1:1),0)
copy down as appropriate.

Address would be the named range containing your addresses, where I am
assuming it is set out as

A B C
D E
Building1 45 Some Street Random Town A City Tel
--
Regards

Roger Govier


"PaulW" wrote in message
...
A2 = "S"
C2:C10 then provide a a list of buildings within the "S" postcode
(this is
done mostly via Vlookup)
D2:D10 is the full address of the first building, dodgy drawing :p

A B C D
S Building 1 Building 1
Building 2 45 Some Street
Building 3 Random Town
Building 4 A City
Building 5 Tel number 1

What i'm wanting is a quick and easy way to change column D to give a
different address. All the information in Column D is from Vlookups,
to look
at different columns, searching from C2.

I'm sure at one point I had a formula, or something, so the cell
reference
of the active cell was displayed. Thus, if C2 was the active cell,
then I
could have the Vlookup(indirect(M4),etc so the address can be changed
effortlessly.

So basically i'm wanting to know in a cell, what the address of the
active
cell is, so I can use it in an INDIRECT withing a VLOOKUP

"Roger Govier" wrote:

Hi Paul

Can you show the formulae you are using, with a sample of the data?

--
Regards

Roger Govier


"PaulW" wrote in message
...
Cell B2 = Postcode Search

Cell C2 returns the first line of address from vlookup / indirect
address
stuff like that. i have one cell that looks up the line of the
address
from
another workbook that feeds which address to pull up. All this
based
on the
postcode entered.

if the active cell highlights C2 i wish for that full address to
appear, if
C3 is highlighted full address for that also etc etc.

I'm trying to avoid VBA as there is no real trigger for the macro.

I'll take a look at what your suggsting below and let you know!!

Thanks Roger.

"Roger Govier" wrote:

Hi Paul

in VBA yes, you can use
activecell.address
activecell.row
activecell.column

as a Worksheet formula
=ROW() will return the row number of the cell in which you type
the
formula
=COLUMN() will return the column number

In a formula, I think you might be needing the Offset() function
as
=CELL("address",C7), will return $C$7 but in that case, you have
fed
it
the location, so therefore you know the address.
=CELL("address"OFFSET(A1,2,6) will also return $C$7

can you give an example of what you are trying to achieve?

--
Regards

Roger Govier


"PaulW" wrote in message
...
Is there a formula that returns the address / row / column of
the
active cell?

So i can use this to produce different results based on the
active
cell.

PaulW