Look at me!

[Cheeky Charlie]

I did something in this thread that I've never seen done before, and I really like it. I'd like it if any of you would like to:

• Tell me I'm great
• Tell me why it's actually a ruddy stupid idea
• Verify/correct my assessment of the issues
• Make it work in newer XL versions (you'll see if you go)

It is a nice idea, but it's not a perfect solution, so although I quite want to share it, I would also genuinely like to see any suggestions/improvements.

CC

[ChemistB]

I had actually subscribed to this thread a while ago because I was interested in analyzing your solution. I have not had time yet to do that though. Nice job, Charlie

[Ron Coderre]

First,
Kudos on your approach. I can certainly appreciate the level of commitment and creativity
required to formulate that solution...(read on, though)

Second.
You might remember when I responded to Pike's post about giving author credits to some
of the great posts in this forum. I was against it because even though a posting in this
forum might be new, exciting, innovative, and brilliant...in all likelihood it probably
appeared someplace else first.

Now...not that I invented that approach (I'm sure others have used it, too) ...but...I have
had the below "canned" response in my solution cache for several years and have posted
it in the MS newsgroups. The example is for consolidating data from different workbooks,
but I amend it as necessary for consolidating within a workbook. I, too, thought it was
brilliant when I...(ahem).."invented" it, but for some reason it never caught on as a popular
approach. I'm glad that at least one other person on the planet uses it, too!


My canned response:----------------------------------------

You might be able to use MS Query to consolidate Excel ranges from your
multiple wkbks/wkshts. This also works for consolidating data from the active workbook (Just save it first so Excel can find it):

This example uses 5 named ranges in 5 different workbooks.
(Each range contain 4 columns: Dept, PartNum, Desc, Price)

Assumptions:
The data in each wkbk is structured like a table:
--->Col headings (Dept, PartNum, Desc, Price)
--->Columns are in the same order.

The data in each wkbk must be in named ranges.
--->I used rng1111Data for dept 1111's data, rng2222Data for dept 2222, etc
--->You may use the same range name in different wkbks.

(Note: MS Query may display warnings about it's ability to show the query
...ignore them and proceed.)

Starting with an empty worksheet:
1)Select the cell where you want the consolidated data to start

2)<Data><Import External Data><New Database Query>
• Databases: Excel Files

Browse to ONE of the files, pick the data range to import.
--->Accept defaults until the next step.

At The last screen select the <View data/Edit the Query> option.

Click the [SQL] button

Replace the displayed SQL code with an adapted version of this:

SELECT * FROM `C:\Dept1111`.rng1111Data
UNION ALL
SELECT * FROM `C:\Dept2222`.rng2222Data
UNION ALL
SELECT * FROM `C:\Dept3333`.rng3333Data
UNION ALL
SELECT * FROM `C:\Dept5555`.rng4444Data
UNION ALL
SELECT * FROM `C:\Dept5555`.rng5555Data

Note: Apostrophes in the SQL code ( ` )are located on the tilde key (~)

Return the data to Excel.

Once that is done....to get the latest data just:
Right Click in the data range
...<Refresh Data>

You can edit the query SQL at any time to
add/remove data sources and/or fields.

[martindwilson]

i've got this round thing and it rolls so i'm going to call it wait for it a wheel !!!!! only joking tho
it does look as somethings get re-invented not to say it's not worth re introducing again . i'd never heard of that approach and even if you'd never read it kudos to you for thinking of it!

[Cheeky Charlie]

Hey Ron, that's a great way of summarising my ... oh, I see.

We're like Newton and Liebniz... except, probably, for the one before you, and the one before them...



As has been alluded to, the journey, and the pleasure of discovery is all part of it.

Now I'm down from my cloud a bit, I think this approach could be employed more often and will endeavour to suggest it where appropriate.

Cheers all