You did do the right thing there. Sorry for messign that up on you.

Click the fx to the left of the formula bar where you have the =INIDRECT
formula. What is it showing as the values?

"merlin" wrote:

ok - this is the actual formula I'm using:

=INDIRECT(D$2&"!"&$AI4)

where D2$ is the heading at the top of the column on the summary sheet (e.g.
contains text 'JAN') and $AI4 is the cell on each sheet I need to total.

I was getting a formula error but I tinkered and added a " which seemed to
fix it but I'm still getting a 'REF#' error. The text in D2$ is identical to
the text on the sheet tab for JAN.

any thoughts?



----- Original Message -----
From: "Sean Timmons"
Newsgroups: microsoft.public.excel.newusers
Sent: Tuesday, May 26, 2009 1:38 PM
Subject: referencing another sheet and using auto fill


I believe the OP wants to see individual month numbers as well.

The first question is, where on the other sheets is the data we're
pulling?

Let's assume the data is in the same place for every sheet, cells B2
through
D2 for the below example, and in the same order: Peter, John, then Jane.

Please ensure your month names on the summary tab exactly match the names
of
the tabs in your workbook.

in that case, under B2 of your summary, enter =INDIRECT(B$1&"!$A2) and
paste
across and down.

"Eduardo" wrote:

Hi,
Assuming that all the sheets has the same format in the summary one go to
the first cell and enter the formula as follow

=SUM(Sheet2:Sheet3!A1)

Replace Sheet2 with the name of the first sheet in your workbook in your
case maybe January

Replace sheet3 with the last tab in your workbook

Replace A1 with the cell where you want to pull the information then copy
the formula

If this helps please click yes, thanks

"merlin" wrote:

Hi there.

I'm trying to create a summary sheet to summarise data from 12 sheets
(jan
to dec).

I have 84 lines each with a member of staff and I need to produce a
table
with the totals of thei leave in each month. Each seperate month sheet
has
84 lines and a total for each person already on it.

I need to end up with something like this on the summary sheet:

jan feb
mar etc total
peter's leave totals for month and year: 3 4 1
8
john's leave totals for month and year: 7 2 3
12
jane's leave totals for month and year: 4 0 0
4

my sheets are labelled "jan" "feb" etc and what I'm trying to do is use
a
formula which allows me to drag and autofill across and down on the
summary
sheet to avoid clicking all the workbook for everyone of 12x84 cells
by
using the labels at the top of each column in the summary sheet.

I've considered INDIRECT and ADDRESS but I don't really understand how
to
apply them.

Please could someone help?

TIA