Extract Multi-Sheet Values into a Summary-Sheet

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have many sheets with the same design, all on different "tabs" but on the same workbook. For example product1, product2, product3-sheet, etc.... I need to collect some data on a summary-sheet with a set of formulas that point to the same cells on each selected sheets. Looking for an automated way and easy way to extract values across sheets.

Any way to do it using the tab-name into a formula

Using Excel 200

Thanks in advance
 
Jorge, here are some easy ways:

To collect (that is, reproduce) data from another worksheet (ex. Cell A1 on
Sheet2):

1-On Sheet1, in any cell, enter =Sheet2!A1
2-Press Enter

or

1-On Sheet1, in any cell, enter =
2-Switch to the target worksheet
3-Click the target cell
4-Press Enter

To sum a range of cells, where that range spans worksheets (ex. Cell A1 on
Sheets2-4):

1-On Sheet1, in any cell, enter =sum(
2-Click Sheet2; click A1
3-Holding sown Shift, click Sheet4 (A1 is automatically highlighted)
4-Press Enter

Also works with other functions, of course.

To add up (or subtract, or whatever) cells on different worksheets (ex.
Sheet2 A1 and Sheet3 C4):

1-On Sheet1, in any cell, enter =
2-Switch to Sheet2
3-Click Cell A1
4-Type a plus sign (+)
5-Switch to Sheet3
6-Click Cell C4
[if necessary, type plus sign, repeat for other worksheets and cells, after
clicking the final cell]
7-Press Enter

Hope this helps.
--
DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com


Jorge B.Domic said:
I have many sheets with the same design, all on different "tabs" but on
Click to expand...
the same workbook. For example product1, product2, product3-sheet, etc....
I need to collect some data on a summary-sheet with a set of formulas that
point to the same cells on each selected sheets. Looking for an automated
way and easy way to extract values across sheets.
 
Back
Top