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

not so excel-lent

New Member
Jul 3, 2008
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!

Some videos you may like

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

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics