Can I create a 3-D reference from multiple workbooks

J

Jennifer

I have five workbooks with summary pages using 3-d references. I would like
to creat a worksheet to pull the information from each of the summary pages
into one worksheet.
 
S

Shane Devenshire

Hi,

The standard formula looks like this

=SUM(Sheet1:Sheet5!A10:A15)

Most of Excel's functions do NOT support this type of reference, but a few
do.

This formula means that you are summing A10:A15 for Sheet1 to Sheet5 based
on physical position of those sheets. If a sheet is between Sheet1 and
Sheet5 then it is summed, but for example, if Sheet2 is to the left of Sheet1
and Sheet5 is to the right of Sheet1, Sheet2 is not summed.

The easiest way to create this formula is to type =SUM( and then click the
sheet tab of the first sheet, highlight the range you want to sum and then
hold down the Shift key and click the last sheet you want to sum.
 
G

Gord Dibben

Did you mean to say "I have 5 workbooks each with a summary worksheet and I
want to create a new workbook with a summary sheet pulling from the summary
sheets of the 5 workbooks"?

"Pages" are what you print out.


Gord Dibben MS Excel MVP
 

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