Gathering Data from various tabs

A

ask72883

Hi,

I have this massive file with some 60 odd tabs and I was wondering what
function would be the best way to approach gather data from each of these
tabs and putting it on the first sheet. Each of the tabs are structured the
same way so I would be grabbing simply the date and the data item (which will
always be in the same cell) for all 60 and putting them on the first sheet.
Any help is greatly appreciated.

Thanks,

Andy
 
M

Miguel Zapico

One solution can be the use of INDIRECT(). You may create a column with the
name of the sheets (let's say column A), and in other column(s) you create
the text reference to the cell(s) you want. For example, to get cell $D$5
you can use:
=A1 & "!$D$5"
Copying this formula along your list of sheets will produce cells with
something like "Sheet1!$D$5", "Sheet2!$D$5", etc. Now to refer to the values
on those cells, use the formula:
=INDIRECT(B1)

Hope this helps
 
Top