Compile list of data from same cell in multiple worksheets

Hi,

Maybe with a macro somewhat along these lines. First create a new sheet called "Summary"

Please Login or Register  to view this content.

But going forward why are you collecting data on all those 400+ sheets. May I respectfully suggest that's not the way to collect data, and certainly not if you need to be able to analyse or summarise it.

You should collect all the data on a single sheet. Probably with the same layout as your existing sheets (but we'd need to see in order to comment definitively) but with an extra column to record whatever each individual sheet currently represents.

..Of course. Cut down the workbook since we don't need to see all 400+ sheets - I'm assuming they are all similar and upload the workbook.

In your 2nd post you mention a couple of files. Is the second file the result you want to see when all J6 values have been listed?

No. I guess your uploaded sheet went unnoticed, that's all.

So you want a list of the content of J6 in every sheet in the workbook? Is that correct?

The easist way to do this is firstly to create a Named Range (see CTRL-F3 for details). Then generate a list of all sheets in the workbook (column A of list page). For the sake of neatness, I put the list page after your INDEX sheet. Then use INDIRECT and the sheet list to return the content of your chosen cell (J6). You can hide the list of sheets, but leaving it there enables you to see what sheet each of your returns comes from. Is this what you were after?

If you run into problems, just shout. If all's well, please mark the thread as solved (by selecting Thread Tools from the menu link above and marking as SOLVED). It'd be appreciated if you were to click the Add Reputation button, too! Thanks..

Hi there.

The difficult bit (to understand and explain) is the content of column A. The formula and the Named Range returns acurrent list of all sheets in the book in the form [filename]sheetname. The MID funtion slices that after the ] and returns only the sheetname.

That can then be used in column B. In that column, the INDIRECT function looks in every sheet listed in Col A and returns the content of J6. If there's nothing in the A column, it returns a blank.

Open the Named Ranges box in the sheet that I sent to you - CTRL F3 - use the formula AND name that are in my version...

You and me, both!!! Thanks for the Reputation.