Formula or macro to get value of active row and a specific column upon change of active cell

Hello experts,

I have a large pivot and I am trying to build a chart that will change the values depending on what row is selected. I am feeding the chart off a series of cells in A1 through F1. I need a formula or a macro that will assign the value to these cells based on the active cell the user selects. For instance, if the user clicks on cell D5, A1 = A5, B1 = B5, C1 = C5, D1 = D5, E1 = E5, F1 = F5.

Even if the user clicks on Z5, the value of A1:F1 would still behave like above, always feeding from columns A:F but the row will vary based on what the user selects. There is a limitation to the rows from 5 to 200. If the user selects row 4 which in this case has titles, the cells in A1:F1 would remain empty.

I hope this makes sense and it is possible. I searched and searched but I don't think there is a formula that does this, I probably need a macro.

Thx much!
AndreeaStart Free Trial