Auto fill across multiple worksheets

[richarmk]

Question regarding filling across multiple worksheets in one sheet:

I want to fill data in one cell in one sheet across multiple sheets; ie I
have different totals, in different sheets, but with the same row & Col. no.
(eg A10)

I want to create a list, in a separate sheet, of all these totals, by
autofilling across the worksheets.

It would take too long to select each cell that has the total in it
individually.

eg:
I have 4 Sheets: 1, 2, 3 and 4.

Total is in cell "A10" in sheets 1, 2 and 3.

I want to create a list in sheet 4 of the totals in sheets 1, 2 and 3,
without selecting each total individually.

(Similar to summing across sheets:"=sum(1:1!A10)" gives the sum of the
totals in cell A10 in sheets 1, 2 and 3)

Thanks,
Mark

 

[Max]

One way using INDIRECT ..

List the actual sheetnames in B1 across, eg: Sheet1, Sheet2, etc
List the target cell references in A2 down, eg: A10, K22, etc

Then you could place in B2:
=INDIRECT("'"&B$1&"'!"&$A2)
Copy B2 across and fill down as far as required

 

[challa prabhu]

Hi,

Do the following:

1. Click a cell in a worksheet.
2. Before enter any value, press Ctrl Key.
3. Click on all the sheet tabs. This will group all the sheet tabs to
perform the same operation that you do on one sheet.
4. Enter a value in the selected cell.
5. Now Auto fill by clicking and dragging the corner on the selected Cell.
All the sheet will display the value in the same position of the cell
location.

Challa Prabhu

 

[Bruce Sinclair]

Hi,

Do the following:

1. Click a cell in a worksheet.
2. Before enter any value, press Ctrl Key.
3. Click on all the sheet tabs. This will group all the sheet tabs to
perform the same operation that you do on one sheet.
4. Enter a value in the selected cell.
5. Now Auto fill by clicking and dragging the corner on the selected Cell.
All the sheet will display the value in the same position of the cell
location.

Caution. Once you have finished doing what you want to 'all sheets',
remember to select just one again. You can REALLY screw things up big time
by forgetting this apparently simple step. Believe me - sometimes close
without saving is your friend ... and of course, always have a back up ...
just in case.