| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Active cell as a reference to open a worksheet | Excel Worksheet Functions | |||
| value of the active cell | Excel Worksheet Functions | |||
| HOW TO COPY 480 ACTIVE E-MAIL ADDRESSES CLM "G" ON AN ACTIVE EXCE. | Excel Discussion (Misc queries) | |||
| problem with cell reference in =sum(offset(cell reference,x,y,z,a)). Want cell ref to be variable. | Excel Worksheet Functions | |||
| Problem with =sum(offset(cell reference,w,x,y,z). I want cell reference to be variable | Excel Worksheet Functions | |||
Linear Mode
