Creating a field and a button to highlight a building in a scatter plot

[mvirgilio53]

I work for an agency that manages buildings, and I have a nice looking distribution scatter-plot of the all of the building's energy performances. What I would like to do is add a field where you can enter the name of the building and then press a button and excel will highlight the building on the scatter-plot. I think it would be nice for building managers to be able to see where their building falls on the distribution. If that doesn't make sense please ask for clarification. Thanks.

 

[Stephane Nolf]

You do not need to use a button or any macro type manipulation.


In one cell insert a list (via data validation) with all the possible buildings. Then create a dummy that will indicate #N/A if the building is not the one selected and the XY value of this building if it is the good one.


It should give you something like that (here the "BuildG" has been selected):


Label X Y X2 Y2

BuildA 5 4 #N/A #N/A

BuildB 8 4 #N/A #N/A

BuildC 10 3 #N/A #N/A

BuildD 3 5 #N/A #N/A

BuildE 8 7 #N/A #N/A

BuildF 6 9 #N/A #N/A

BuildG 7 6 7 6


the formula used to return the #N/A is quite simple (for cell D2):

=IF($A2=SelectedBuilding;B2;NA())


I hope it helps.