Macro:Extract value from specific cells in different worksheet named in column A

Hi there, I apologize for the confusing title thread as I didn't know how to better explain it. I'm completely new to VB but know of its potential and have used simpler codes to enhance work performance. I'm working on a training costs spreadsheet. I'm looking for a code (macro) that would allow me to select data from a worksheet named in column "a" of the active worksheet and copy that value back into the active worksheet. E.g. Active worksheet is named "Summary". Second worksheet is named "Leadership". I am in column "c5" (Summary) and want to select the worksheet named in column "a5" (Summary) which happens to be "Leaderhsip". I want to the code to go to the "Leadership" spreadsheet and extract a value from cell g3 and return it to cell c5 in "Summary". I want to then have column d5 in "Summary" return a value from cell g4 in "Leadership" and so on.... I have various worksheets and want to run this individual maro on each row separately in the "Summary" worksheet as I add rows with worksheet names. The values being returned are calculated values and I want the values in the "Summary" worksheet to update automatically based on the values in the other worksheets. I do not want to manually input a cell reference formula each time.

I have attached a sample spreadsheet for reference.TrainingSample.xlsx


Any and all assistance would be greatly appreciated!

Thanks Arlette,

I googled index and match formulas but can't find an example where I can input a formula to refer to a worksheet name listed in column A. I tried to modify the sample formulas but still haven't been able to get it to work. Are you able to provide a sample formula I can input into cell c5 of the Summary worksheet that will have it refer to the worksheet listed in a5 (Leadership), cell g3?

I tried to commence the formula with index('a5:a6,1,1'!)...to attempt a formula to refer to the worksheet named in column A so it will change/update when I copy the formula down.

Thanks Arlette,

I found an "indirect" formula that works =INDIRECT("'"&A5&"'!g3")

Your response got me thinking outside the box and I was able to find a formula to suit my needs...