How do I extend a series of data referenced from another worksheet?

not so excel-lent

New Member
Joined
Jul 3, 2008
Messages
14
I'm using Excel '03.

I have a workbook with 21 spreadsheets. The first 20 are days of the month (Oct 13, Oct 14, etc) and contain job duties and the total number of hours for each job duty. The last spreadsheet is titled Summary. On the Summary sheet I have the job duty titles to match the other spreadsheets (these are in A3 through A41 on the Summary spreadsheet). The header in Columns B through U are the same names as the other spreadsheets (Oct 13, Oct 14, etc.).

What is the easiest way to pull data from each of the individual spreadsheets to the summary spreadsheet? I know I can reference each cell individually but this will take forever. I did this for the first few cells in Column A and then tried to use the fill function but Excel doesn't recognize the pattern.

I am trying to pull the data from Column AN (total hours for each job duty for that day) in each of the other spreadsheets. I want to pull the data for AN2, AN9, AN16, etc (every 7 cells in Column AN) so here is what my formulas look like on the summary sheet:
Cell B3 = 'Oct 13'!AN2
Cell B4 = 'Oct 13'!AN9
Cell B5 = 'Oct 13'!AN16

Is there a that Excell can recognize the 7 cell pattern and continue the sequence using the fill function? Any other suggestions. I have to do this for all 20 columns so it would take a while to do it manually.

Thanks for the help!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,224,558
Messages
6,179,512
Members
452,919
Latest member
krismmmitch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top