Active Cell Reference
 

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.

Active Cell Reference
 

Formulas return values -- not formats.

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

would return the value from C2.

pskwaak wrote:

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.

--

Dave Peterson

Active Cell Reference
 

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?

"Dave Peterson" wrote:

Formulas return values -- not formats.

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

would return the value from C2.

pskwaak wrote:

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.

--

Dave Peterson

Active Cell Reference
 

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.

pskwaak wrote:

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?

"Dave Peterson" wrote:

Formulas return values -- not formats.

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

would return the value from C2.

pskwaak wrote:

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.

--

Dave Peterson


--

Dave Peterson

Active Cell Reference
 

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

"Dave Peterson" wrote:

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.

pskwaak wrote:

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?

"Dave Peterson" wrote:

Formulas return values -- not formats.

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

would return the value from C2.

pskwaak wrote:

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.

--

Dave Peterson


--

Dave Peterson

Active Cell Reference
 

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

"Dave Peterson" wrote:

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.

pskwaak wrote:

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?

"Dave Peterson" wrote:

Formulas return values -- not formats.

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

would return the value from C2.

pskwaak wrote:

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.

--

Dave Peterson


--

Dave Peterson

Active Cell Reference
 

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

"JMB" wrote:

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

"Dave Peterson" wrote:

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.

pskwaak wrote:

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?

"Dave Peterson" wrote:

Formulas return values -- not formats.

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

would return the value from C2.

pskwaak wrote:

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.

--

Dave Peterson


--

Dave Peterson

Active Cell Reference
 

It did when I tried it.

"pskwaak" wrote:

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

"JMB" wrote:

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

"Dave Peterson" wrote:

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.

pskwaak wrote:

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?

"Dave Peterson" wrote:

Formulas return values -- not formats.

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

would return the value from C2.

pskwaak wrote:

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.

--

Dave Peterson


--

Dave Peterson

Active Cell Reference
 

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.


"JMB" wrote:

It did when I tried it.

"pskwaak" wrote:

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

"JMB" wrote:

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

"Dave Peterson" wrote:

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.

pskwaak wrote:

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?

"Dave Peterson" wrote:

Formulas return values -- not formats.

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

would return the value from C2.

pskwaak wrote:

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.

--

Dave Peterson


--

Dave Peterson