Multiple sheets

[Wendy]

Hi

I have several workbooks with a sheet in for each day (W1Mo etc) which all
link to a daily totals sheet in a seperate workbook. What I've done is
named the total figure cell in each column and then linked that to the daily
sheet, but I have approx 120 named cells per workbook. Is there an
easier/better way?

Thanks

Wendy

 

[Pete_UK]

Why are you using named cells? This will take longer to set up than
normal cell references.

Pete

 

[Wendy]

I thought it would be easier linking to the totals sheets.

Wendy

Why are you using named cells? This will take longer to set up than
normal cell references.

Pete

 

[Pete_UK]

But now you are finding that it is a bit tedious to use the names. You
don't need to remove them, but I think it will be easier to use normal
cell references in the linking formulae - if there is some
relationship between the cells you want to link, then copy/paste can
adjust the references automatically.

Pete

 

[stew]

Wendy

If you have the worksheet names in your summary spreadsheet, say in
cells A1 to A120 then you can use the "INDIRECT" command in cells
B1:b120 to reference the other worksheets.


For example if you had

- a worksheet called "Jan",
- Your desired total on the Jan sheet was in A1
- cell a3 in your summary worksheet contained "Jan"
- Put the following in cell B3: =INDIRECT(A3&"!A1")

This will then bring in the value from cell A1 on the worksheet Jan.
With the power of drag and drop you can quickly fill in the remainder
formulae. You will need to have all the worksheet names on your
summary sheet though!

Stew

 

[Dave Peterson]

The bad news is that the OP said the data was in a separate workbook. And
=indirect() won't work if the "sending" workbook is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/

That includes =indirect.ext() that may help.