Pulling data from individual files to master list

[joshwimmer]

Hello,

I've just been entering the world of Excel for the past few months, as
I started a new job last year and my main duty is to bring the company
into the 21st century (or even the late 20th, at this point).

What I'd like to do sounds a little backwards, but I think it's the way
to go, if it's possible:

I'm creating individual sheets for our products, so that all the
relevant info for, say, product A001 is shown on one sheet named
"A001.xls".

But I would also like to create a "master" list. I say "master" in
quotes because it's not really a master. I don't want to update the
master list and have it update the individual sheets -- I could do that
pretty easily with VLOOKUP. Instead, I want to set up the master list
so that it's updated whenever I change an individual sheet.

I know how to do this the long way, but I'd rather not copy and paste
new formulas into the master list. Is there a simple way to set up the
master list so that if I entered "A001" in the first column, it would
know to pull data for the rest of the row from file "A001.xls"? And so
on, and so on?

Thanks!


Yours,
Josh

 

[Don Guillett]

Have a look at using INDIRECT. Or, just use a macro to get from all sheets
with one mouse click. Here is an idea using something from an earlier post
of mine today.

Sub copyonecelltosummary()
counter = 80
For Each ws In Worksheets
With Sheets("summary")
.Cells(counter, "a") = ws.Name
.Cells(counter, "b").Value = ws.Range("c7")
End With
counter = counter + 1
Next ws
End Sub