Auto fill across multiple worksheets

R

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
 
M

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
 
C

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
 
B

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. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top