pick data from a specific row/column (eg 10/B) related to active cell

Hi guys, hopefully the title makes sense, but I'm not sure it does!

I have a spreadsheet with my Periods along row 10. e.g. C10: "1", D10: "2", E10 "3", F10: "4", G10: "5" etc. (green on the attached sheet)

I have my departments along column B, e.g. B11: "Baked" B12: "Fresh" B13: "Frozen" (yellow on the attached sheet)

what I need and cannot work out is some VBA code that will populate two variables (lets call them Period & Department) when I click on one of the figures. For example if I click on cell:

if I click E14: Period would have the contents of cell E10, and Department the contents of cell B14.

if i click G14: Period would have the contents of G10, and Department the contents of cell B14 again.

I know how to get the click on the cell to work properly etc, and I have code to slot these variables into that works very nicely, I just can't get this bit to work!!!!


thanks in advance guys...

Hi Zonino, you might try the following SelectionChange macro for the worksheet with your data (or add the structure to your existing macro):

Please Login or Register  to view this content.