Excel/Excel spresdsheet

Advertisement


Question
Example:  I have a workbook with 9 sheets, one being the "master" sheet. Each of 9 sheets contain items specific to items that we sell.  I would like specific cells on each sheet of the 9 sheets to feed into the 10th "master" sheet, to create a master list.  However, I want the info on sheet 1 to be entered on line 1 of the "master" and the info on sheet 2 to be entered on line 2 of the "master" and so on. How can this formula be set up to do this automaticly as info is entered on each sheet?

Answer
Ronald -

This is what we used to call "3-D formula": create formulas on the Master page that, instead of looking at cells within it's own sheet, looks at cells on OTHER sheets (or even other Workbooks).

Begin a calculation on the Master sheet - cell A1, " = "
Click on the tab for Sheet 1
Click on the desired cell - A1
Hit Enter to complete the function
Now Sheet 10 cell A1 reflects what is in Sheet 1, A1. Change what is in Sheet 1, A1, it is changed automatically in the Master sheet.

Here are the steps to sum across sheets - they will sound familiar:

Begin a calculation on the Master sheet - cell A1, " =SUM( "
Click on the tab for Sheet 1
Click on the cell you want in the calculation - A1
Type " : "
Click on the tab for Sheet 9
Click on the cell you want - A1
Hit Enter to complete the function
Now Sheet 10, cell A1 has the sum of all of the numbers in Sheets 1 through 9, cells A1.

You can use this to get any calculation you want - SUM, PRODUCT, AVERAGE... - just create the functions as you normally would. A few things to watch for:

- Your calculation is in the Master sheet, but since you're jumping around between sheets it's easy to get confused. Just take it slow and methodical.
- Just like with any function, anything you click on becomes part of the equation! You can always redo the calculation.
- When completing the function, DO NOT return to the Master sheet! Fact is, while you are creating the function, you are always in the Master sheet - "returning" to it can change your function.
- I would recommend locking the Master sheet, so you cannot accidentally type in it.

Hope this helps - Andrea
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Andrea Lynch

Expertise

I am very familiar with teaching excel concepts, and formulas. I am not as capable with VBA questions.

Experience

I currently tutor in this area. I worked for four years developing curriculum and teaching Microsoft applications to adult audiences. I also previously volunteered for allexperts.com.

Education/Credentials
BA, English, Western Washington University Certificates in C Programming, ASP.NET, VisualBasic.NET, University of Washington Extension

Awards and Honors
Rated in the top ten instructors (national), New Horizons Computer Learning Center.

©2016 About.com. All rights reserved.