to had the value show in one cell if a different cell is clicked o

[Mudman]

I have 3 colunms of values, and at the bottom in different cells three cells
that represent the values and below that a formula cell.
I want to be able to click on any value in column C1 and have that value
show up in the cell below. The same with columns C2 and C3, if that value is
selected then that will appear in the formula cells. So if you click in the
column C1 on the 6 it will appear in the cell below and then can be used in
the formula. If you click on a box in C2 it will appear in the next cell and
same with C3.
I know it is possible, just cannot figure out how to get it done.
Thanking you for your help!

C 1 C 2 C 3

2 25 100
4 26 101
6 24 102
8 27 103
10 28 104
12 29 105
14 30 106
16 31 107




C1_______
C2_______
C3_______

equals _______

 

[Gord Dibben]

Columns are lettered across as A, B, C etc.

There is no column C1, C2, C3

C1 is the cell address of the intersection of column C and Row 1

C2 is the cell address of the intersection of column C and Row 2

With these facts in mind please re-post with a description of your layout and
what you need done.


Gord Dibben MS Excel MVP

 

[OssieMac]

Could you accept Double click the number required. If so, then the following
macro should do the trick.

Right click on the sheet tab name, select View Code and copy the macro into
the VBA editor. Close the editor by clicking on the X with the red background
top right of the VBA editor and then save the workworkbook. You will need to
have options set to allow macros to run.

The macro is fairly generic but read the comments within the macro where you
might need to edit it depending on the number of columns and the row number
where you want to start the copy to. You can use any number of columns.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Dim colNumber As Long

colNumber = Target.Column

'In the following adjust < 4 to to one greater
'than the total number of columns
If colNumber > 0 And colNumber < 4 Then
'Adjust the 12 to the row number where you want the
'Column 1 value to copy to.
'Adjust "B" to the column where you want the values
Cells(12, "B").Offset(colNumber, 0) = ActiveCell
Cancel = True 'turns off Edit mode started by double click
End If

End Sub

 

[Mudman]

Thanks Gord,
I have 3 columns A, B, and C and I have a different value in each of the
cells in each column.

for example in A4 I have 6 as a value. If I click on that 6 I would like
the 6 to appear in B64 cell,
and in B17 I have a value of 44, if I click on that 44 I would like the 44
to appear in B65 cell,
The same with C22, if you click on the 154 in that cell it will appear in
cell B66.
Then I can do the formulas in B66 to complete my work,
Thanks again,
Mudman

 

[OssieMac]

I saw Gord's post after I posted my answer. I interpreted the C1, C2 and C3
as column headers for columns 1, 2 and 3. If that is not correct then my
answer is probably garbage.

 

[Gord Dibben]

Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Excel.Range, Cancel As Boolean)
Select Case Target.Column
Case "1"
Cancel = True
Me.Range("B64").Value = Target.Value
Case "2"
Cancel = True
Me.Range("B65").Value = Target.Value
Case "3"
Cancel = True
Me.Range("B66").Value = Target.Value
End Select
End Sub

Right-click on the sheet tab and "View Code". Copy/paste the above into that
sheet module.

Alt + q to return to the Excel window.

A double-click on any cell in Columns A through C will do the trick.


Gord