Excel Formula to auto move & populate field

Hello,

I am builidng a spreadsheet that shows progress week by week on two sheets. Weekly Status and Project summary sheets. I am building it so you can select current week on Project summary sheet and it will populate the status for last week and current week in the progress column. This information will come from the Weekly status sheet where this weeks column is highlighted. I want a formula(s) that:

1. Automatically selects the current weeks column on weekly status sheet
2. Populates data highlighted in that column on the Project Summary tab...probably a ='Weekly Status"!columnnumber I presume.


Thanks