Excel Multiple Worksheets

chateauneufdupape

I have an Excel 2003 workbook with multiple worksheets. The first worksheet is a summary page. I then create a new worksheet every day. On the summary sheet, I want to display the value from specific cells from each worksheet. e.g.

=Sheet1!$A$1
=Sheet2!$A$1
=Sheet3!$A$1

On the summary sheet, when I drag the formula to a new line, it continues to look at the last worksheet rather than updating to sheet 4. I can change this manually, but there are 8 cells that I am looking at from each sheet and I am sure that there must be a way of doing this automatically. Any ideas?

espresso

I don't believe that you can drag your formula down to a new line if it is referencing other worksheets, although someone else may know different!

You can however refer to the same cell or range of cells on multiple sheets by using a 3-D reference.

i.e. to calculate the sum of the contents of A1 cells across several worksheets.

1. Click the cell where you want to enter the function in your summary sheet.

2. Type = , enter the name of the function, and then type an opening parenthesis.

3. Click the tab for the first worksheet to be referenced.

4. Hold down SHIFT and click the tab for the last worksheet to be referenced.

5. Select the cell or range of cells to be referenced.

6. Complete the formula.

=SUM(Sheet2:Sheet8!A1)


HTH

:rudolf:

chateauneufdupape

Helped a bit thanks, but it is the problem in your first sentence that I am trying to resolve.

StephenB_2

I'm sure there are several ways of doing it, but one way is: set c1 to 1 (we will use this to reference sheet 1), set c2 to =c1+1 (this will reference sheet 2) & set d1 to

=INDIRECT("sheet" & FIXED(c1,0) & "!$a$1")

d1 now contains the value in cell a1 from sheet 1. Drag and copy d1 onto d2. Then drag and copy c2/d2 downwards to your heart's content.

SJB

tomstickland

You can write a VBA program to do it.

chateauneufdupape

StephenB, Not exactly sure what it all means, but has worked a treat. Thanks very much for your help.

tigermatt

You could record a macro that copies the values from the sheet and pastes them into the Summary page. (Instead of using formulas)

Macros are easily recorded in Tools > Macros > Record New Macro. Follow the instructions and then start copying the values from your daily sheet into the Summary sheet. The Macro will record and remember what you are doing. Use the toolbar that should appear to stop once you have done everything, or you can Pause the recording if you wish.

I hope this helps, tigermatt

tomstickland

dim count as long
for count = 1 to 5
sheets(0).cells(count,1)=sheets(count).cells(1,1)
next count

something like that in VBA editor.