Link worksheet to a row

[BuuHii]

Hello,

Sorry for the long text :)

I have a workbook which has 100 worksheets. The worksheets contains data of different rooms so that the data is always in the same place/cell within the worksheet. Each worksheet contains about 50 cells that have data. I would like to collect that data to a new worksheet so that all the data from the worksheet would be in the same row. So the row would always represent one worksheet and in the end I would have 100 rows where all the data from the different worksheets would be collected.

I know that I can copy the data from the worksheet and paste it as a link to the new worksheet and repeat that with every cell. Furthermore I think I have to do that with the first worksheet so that the data would go to a place in a row where I want it to be. The problem is that there are 100 worksheets so it´s a really big job.

Is there a way to do it so that after the first worksheet is linked to a row I could just copy the row and the excel would know that the data for the next row should be linked from the next worksheet?

I´ve gotten some general advice that I could use INDIRECT, INDEX and ROW -functions but don´t know how...

Thanks!

[#2]

@BuuHii 

See the attached demo workbook. The Summary sheet has the addresses of the cells you want to link to listed in B1, C1, etc.

Running the Listsheets macro will create a list of sheets in column A, and then create formulas in column B, C, etc. that link to the specified cells.

You'll have to allow macros.

[#3]

Hello and thanks! Unfortunately I´m using my workcomputer which doesn´t allow me to use the macros (it shows some risk warning). :(

[#4 · Accepted solution]

@BuuHii 

Here is a version of the workbook in which I have run the macro, then removed it. Perhaps the formulas will give you an idea of how you could do what you want. You need to enter the formula in B2 only, then fill or copy to the right and down.

[#5]

Thank you! This works just the way I wanted :)