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

Hi Mark,

To fill data in one cell in one sheet across multiple sheets, you can use the "Fill Across Worksheets" feature in Excel. Here are the steps to do it:

1. Select the cell that contains the data you want to fill across multiple sheets.
2. Right-click on the selected cell and choose "Format Cells" from the context menu.
3. In the "Format Cells" dialog box, go to the "Alignment" tab and check the "Merge cells" checkbox. Click "OK" to close the dialog box.
4. Now, select all the sheets where you want to fill the data. You can do this by holding down the "Ctrl" key and clicking on the sheet tabs.
5. In the first sheet, enter the data in the merged cell.
6. Right-click on the sheet tab and choose "Select All Sheets" from the context menu.
7. Go to the merged cell in the first sheet and click on it to select it.
8. Press "Ctrl+D" to fill the data across all the selected sheets.

To create a list of all these totals in a separate sheet, you can use the "SUM" function with the "3D reference" feature in Excel. Here are the steps to do it:

1. Go to the sheet where you want to create the list of totals.
2. In the cell where you want to display the total, enter the following formula:
   Formula:

   "=SUM(1:3!A10)" 

   .
   Note: Replace
   Formula:

   "1:3" 

   with the sheet names where you have the totals and
   Formula:

   "A10" 

   with the cell reference where you have the totals.
3. Press "Enter" to calculate the total.
4. Now, you can copy the formula to other cells to display the totals for other cells.

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"richarmk" wrote:
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

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

"richarmk" wrote:

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

In article , ?B?Y2hhbGxhIHByYWJodQ==?= wrote:
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. :)


"richarmk" wrote:

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