Matrix contains data un different sheets - H2?

[marcofaust]

Dear all,

I would like to display data from different sheets into a single array.
The question is long, but I hope I explain this problem accurately.

I have in my workbook a series of sheets with various names. The sheets
have exactly the same layout and thus on each sheet each cell contains
the same sort of information.

Suppose I am interested in viewing at the same time all the data placed
in the cell A1 of each sheet. I would like to display the data in a new
sheet, using a cell vector with the same length as the number of sheets
with data. Let's assume that there are four sheets called aaaa, bbbb,
cccc, dddd. The results must appear in the new sheet RESULT

I select four cells in a column in the sheet RESULT and type in:

='aaaa:dddd'!A1

then I press CTRL-SHIFT-ENTER.
I get the error #REF! in all four cells. Why is this?

If I try the formula =SUM('aaaa:dddd'!A1) then it works and I get four
times the sum of the A1 cells, but this is not what I need. I need the
four numbers, not their sum.

I understand that the : sign is needed by the SUM operator, but then I
ask myself what is the operator that tells Excel: "go from sheet aaaa
up to sheet dddd and for each sheet put in this matrix the content of
cell A1.

I would be enormously grateful to discover the answer to this question.
Thanks in advance

Marco Faustinelli - The Netherlands

 

[dvt]

I select four cells in a column in the sheet RESULT and type in:

='aaaa:dddd'!A1

then I press CTRL-SHIFT-ENTER.
I get the error #REF! in all four cells. Why is this?

I won't pretend to know why that doesn't work. I'll propose a
workaround. Would it be possible to populate the summary sheet like this:

A B
aaaa =INDIRECT(A1&"!A1")
bbbb =INDIRECT(A2&"!A1")
cccc =INDIRECT(A3&"!A1")
dddd =INDIRECT(A4&"!A1")

You could, of course, use worksheet functions to return the sheet names
(i.e. aaaa) and the cell location (i.e. A1).