updating cell formula

[AK]

I have a need to define cell A1 like so :
=CONCATENATE("System Name: ",D2," ",D3)

but instead of a fixed D2 & D3, I would like to replace it with E2 & E3 if I
click on column E, F2 & F3 if I click on column F, etc... i.e. cell value
will be dependent on which column I click on.

Is there a way to do this ?

Thanks,
ak

 

[Tom Hutchins]

One way...

Paste this user-defined function into a VBA module in your workbook:
Public Function CurrCol() As Long
'Returns number of active column.
CurrCol = ActiveCell.Column
End Function

In the ThisWorkbook module, paste this event code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Calculate
End Sub

Finally, here is the formula for cell A1:
=CONCATENATE("System Name: ",INDIRECT(ADDRESS(2,CurrCol())),"
",INDIRECT(ADDRESS(3,CurrCol())))

The first argument in each ADDRESS function is the row to use, so you can
easily change them if needed. If you are new to macros, david mcritchie has
some instructions on his site for navigating the vba editor and how to
copy/paste macros into your project.

http://www.mvps.org/dmcritchie/excel/excel.htm

Hope this helps,

Hutch

 

[AK]

Thanks for the help.
I do get a #NAME? error & when using 'show calculation steps', it seems it
doesn't understand CurrCol(). Not sure what to do next.
My cell is actually A5 & my worksheet is actually Sheet2 - I'm assuming it
doesn't matter.
Also, I'm curious, what does the code that I put into ThisWorkbook do ?

ak

 

[Tom Hutchins]

Did you paste the CurrCol function into a new VBA module, or into the
ThisWorkbook module or the code page for one of the worksheets? To be visible
to Excel, a user-defined function has to be in a VBA module. In the Visual
Basic Editor, select Insert >> Module and paste the code into the empty
window that is created (I'm assuming that only the workbook we're working on
is open). Then CurrCol should be visible to Excel.

The event code in the ThisWorkbook recalcs the worksheet (equivalent to
pressing F9) every time a different cell is selected. I have seen some
user-defined functions be sluggish updating the cells from which they are
being called; this code prevents that problem.

If you still have trouble, let me know via this thread.

Hutch

 

[AK]

Yes - I have fixed the problem.
On the computer that I was working on, when ever I started VB Editor, it
tried to install Studio .NET Enterprise Architect 2003.
Apparently I already have that on my home machine, so I used your code on it
& just copied the xls file into the target computer.

Thanks for the help !

ak