putting a value in a formula based on what cell is "clicked" -- Excel 2010

morinia
morinia used Ask the Experts™
on
Experts,

I would like to create an Interactive spreadsheet (see attached workbook), where if you click on any "A2 through A8" the value in that field will be used in the compare in H5 where "12345" is currently coded.

Can someone tell me how to do this?
Sample-XlS.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
As formulas can't tell which cell is currently selected, you'll need to use VBA.

The code below gets pasted into the worksheets code module (right-click tab and select "View Code")...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    
    If Not Intersect(Target, [A2:A8]) Is Nothing Then
        Range("H5").Formula = "=SUMIFS(orders,FIELDA," & Target.Value & ")"
    End If    
End Sub

Open in new window


This will modify the formula in cell H5 whenever you select a cell in the range A2:A8.
moriniaAdvanced Analytics Analyst

Author

Commented:
Wayne,

Is there a way to put just the value of the selected cell into H5 so I can use it for other things also.
I think I did this before but don't have access to the spreadsheet anymore  

The formula then uses the cake in H5.
Wayne Taylor (webtubbs)

Commented:
Yes, use this instead...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    
    If Not Intersect(Target, [A2:A8]) Is Nothing Then
        Range("H5").Value = Target.Value
    End If    
End Sub

Open in new window

Rob HensonFinance Analyst

Commented:
It is possible with formula as well.

In the cell where you want the specific value use formula:

=INDIRECT(ADDRESS(CELL("row"),CELL("col"),1,1))

initially this will give a Circular Reference but when you enter a value in another cell, on recalculation the cell with the formula will also get that value.

Thanks
Rob H

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial