Active Cell To Change Formula

[Guest]

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

 

[Guest]

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:

 

[Roger Govier]

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?

 

[Guest]

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.

 

[Guest]

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

Any other help gladly recieved!!!

 

[Roger Govier]

Hi Paul

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

 

[Guest]

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

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]

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