Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find address of active cell
Is there a function that returns the row, column or address of the active cell?
I'd like to build a formula in a column that displays a character if the active cell is in that row, such as: =IF(ROW()=ROW(ActiveCell),"X") Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find address of active cell
Use the CELL() function, CELL("row") will always return the row of the active
cell: =IF(ROW()=CELL("row"),"X") Note this will continue to populate if the active cell is in the same row on another tab as well. -- Regards, Dave "Dave" wrote: Is there a function that returns the row, column or address of the active cell? I'd like to build a formula in a column that displays a character if the active cell is in that row, such as: =IF(ROW()=ROW(ActiveCell),"X") Thanks. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find address of active cell
Thanks for the the suggestion but the formula seems to always return true
even when the active cell is no longer in the row with the fromula. I need to compare the row that contains the formula with the row of the active cell. If they are the same then display "X" else display nothing. "David Billigmeier" wrote: Use the CELL() function, CELL("row") will always return the row of the active cell: =IF(ROW()=CELL("row"),"X") Note this will continue to populate if the active cell is in the same row on another tab as well. -- Regards, Dave "Dave" wrote: Is there a function that returns the row, column or address of the active cell? I'd like to build a formula in a column that displays a character if the active cell is in that row, such as: =IF(ROW()=ROW(ActiveCell),"X") Thanks. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find address of active cell
It works fine for me. Try hitting F9... does that update your formula? If
so you have automatic calculation turned off, it's gotta be turned on for the formula to work. To turn on: <Tools <Options "Calculation" tab Check "Automatic" Post back if that still doesn't fix it. Also, if you want the formula to display nothing when the active cell doesn't equal the row the formula is in, then gotta add a condition to the IF formula: =IF(ROW()=CELL("row"),"X","") -- Regards, Dave "Dave" wrote: Thanks for the the suggestion but the formula seems to always return true even when the active cell is no longer in the row with the fromula. I need to compare the row that contains the formula with the row of the active cell. If they are the same then display "X" else display nothing. "David Billigmeier" wrote: Use the CELL() function, CELL("row") will always return the row of the active cell: =IF(ROW()=CELL("row"),"X") Note this will continue to populate if the active cell is in the same row on another tab as well. -- Regards, Dave "Dave" wrote: Is there a function that returns the row, column or address of the active cell? I'd like to build a formula in a column that displays a character if the active cell is in that row, such as: =IF(ROW()=ROW(ActiveCell),"X") Thanks. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find address of active cell
Billigmeier's formula works very well, but needs the F9 to refresh. if you
want to save wear and tear on F9, consider the following worksheet code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row = 5 Then Cells(5, 1).Value = "X" Else Cells(5, 1).Value = "" End If End Sub It will set/clear A5 depending upon the location of the selected code. -- Gary''s Student "Dave" wrote: Thanks for the the suggestion but the formula seems to always return true even when the active cell is no longer in the row with the fromula. I need to compare the row that contains the formula with the row of the active cell. If they are the same then display "X" else display nothing. "David Billigmeier" wrote: Use the CELL() function, CELL("row") will always return the row of the active cell: =IF(ROW()=CELL("row"),"X") Note this will continue to populate if the active cell is in the same row on another tab as well. -- Regards, Dave "Dave" wrote: Is there a function that returns the row, column or address of the active cell? I'd like to build a formula in a column that displays a character if the active cell is in that row, such as: =IF(ROW()=ROW(ActiveCell),"X") Thanks. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Find address of active cell
Thanks very much, guys
I used a combination of the solutions. Using the VBA code to change a value in a range forces a calculation and allows the formulas to refresh. Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("CurRow") = ActiveCell.Row End Sub =IF(ROW()=CELL("row"),"X","") "Gary''s Student" wrote: Billigmeier's formula works very well, but needs the F9 to refresh. if you want to save wear and tear on F9, consider the following worksheet code: Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row = 5 Then Cells(5, 1).Value = "X" Else Cells(5, 1).Value = "" End If End Sub It will set/clear A5 depending upon the location of the selected code. -- Gary''s Student "Dave" wrote: Thanks for the the suggestion but the formula seems to always return true even when the active cell is no longer in the row with the fromula. I need to compare the row that contains the formula with the row of the active cell. If they are the same then display "X" else display nothing. "David Billigmeier" wrote: Use the CELL() function, CELL("row") will always return the row of the active cell: =IF(ROW()=CELL("row"),"X","") Note this will continue to populate if the active cell is in the same row on another tab as well. -- Regards, Dave "Dave" wrote: Is there a function that returns the row, column or address of the active cell? I'd like to build a formula in a column that displays a character if the active cell is in that row, such as: =IF(ROW()=ROW(ActiveCell),"X") Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to find cell content in sheets and make sheet active | Excel Discussion (Misc queries) | |||
Find max number of character and return cell address | Excel Worksheet Functions | |||
Address of Maximum value in Cell | Excel Worksheet Functions | |||
cell address rather than range name | Excel Discussion (Misc queries) | |||
Formula to return ADDRESS of cell in range that meets criteria | Excel Worksheet Functions |