Active Cell Reference

P

pskwaak

Excel 2007 - I need a formula for cell K2 that references the cell value of
the active row, column C. I would also like it to include the format of the
referenced cell, if possible.
 
D

Dave Peterson

Formulas return values -- not formats.

=c2
or
=if(c2="","",c2)

would return the value from C2.
 
P

pskwaak

Thanks for the feedback. What I'm looking for is a cell with a formula that
reflects contents of the active cell as it moves throughout the spreadsheet?
 
D

Dave Peterson

How about a macro:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.Range("K2").Value = Target.Cells(1).Value
End Sub

This changes the value in K2 each time you change the selection.

If you want to try it, rightclick on the worksheet tab that should have this
behavior. Select View Code and paste it into the code window.
 
J

JMB

Since the OP will have to resort to VBA, he/she could also replicate the
formatting. The OP didn't specify exactly which formats - but perhaps just
use copy:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Cells(1).Copy Me.Range("K2")
Me.Range("K2").Value = Target.Cells(1).Value
End Sub
 
P

pskwaak

Thanks! I kinda figured I'd need a macro, but didn't know if there was
already a function for that.
 
P

pskwaak

Thanks! Actually, the referenced cell is conditionally formatted. Will the
macro below include the conditional format?

JMB said:
Since the OP will have to resort to VBA, he/she could also replicate the
formatting. The OP didn't specify exactly which formats - but perhaps just
use copy:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Target.Cells(1).Copy Me.Range("K2")
Me.Range("K2").Value = Target.Cells(1).Value
End Sub
Click to expand...
 
J

JMB

After a few more minutes to think about it - it will depend upon your
conditional format criteria. If your CF depends on a relative cell reference
(eg each cell in column A is CF based on the cell above it - when that cell
gets copied to K2, the CF applied to cell K2 will reference K1).

But, if your CF is based on the cell's value or a formula that does not have
a relative reference to another cell - I don't see why it will not work okay.
 
Top