Another Excel Issue (Summary Sheets?) 1

[LizzyAnn]

Ok, so I just got through the wonders of doing a query (from thread68-1527412) Worked out quite lovely. But apparently, it's not good enough for the higher-ups (My partner-in-crime and myself found it to be quite wonderful.)

So here's the situation: I now have one workbook with multiple tabs that all update from their corresponding workbooks. (Which everyone decided was brilliant. Thanks, Skip.) The question is, is there anyway to create a new tab in the "master" workbook that will list the data of all the "linked" tabs? I've tried consolidating, and I can't get a query to line up all the columns.

Extra info: There are no numbers, just names, addresses, etc. And each spreadsheet has the same columns.

 

[SkipVought]

It seems that that sheet may have DIFFERENT heading values.

Please post your SQL for THAT sheet.

"I can't believe there's not some button somewhere that does this for me."

No, it can be a tedious process in some cases.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

Ah ha! New mystery..
Alright then, when I pull sheet one I get this:
SELECT `Sheet1$`.`Address `, `Sheet1$`.City, `Sheet1$`.`Company Name `, `Sheet1$`.`Customer Name`, `Sheet1$`.`Phone Number`, `Sheet1$`.`Sales Rep`, `Sheet1$`.Zip
FROM `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet1$` `Sheet1$`, `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet2$` `Sheet2$`, `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet3$` `Sheet3$`


sheet two:
SELECT `Sheet2$`.`Address `, `Sheet2$`.City, `Sheet2$`.`Company Name `, `Sheet2$`.`Customer Name`, `Sheet2$`.`Phone Number`, `Sheet2$`.`Sales Rep`, `Sheet2$`.Zip
FROM `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet1$` `Sheet1$`, `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet2$` `Sheet2$`, `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet3$` `Sheet3$`

and sheet three:
SELECT `Sheet3$`.`Address `, `Sheet3$`.City, `Sheet3$`.`Company Name `, `Sheet3$`.`Customer Name`, `Sheet3$`.`Phone No#`, `Sheet3$`.`Sales Rep`, `Sheet3$`.Zip
FROM `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet1$` `Sheet1$`, `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet2$` `Sheet2$`, `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet3$` `Sheet3$`

 

[SkipVought]

SELECT `Sheet1$`.`Address `, `Sheet1$`.City, `Sheet1$`.`Company Name `, `Sheet1$`.`Customer Name`, `Sheet1$`.`Phone Number`, `Sheet1$`.`Sales Rep`, `Sheet1$`.Zip
FROM `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet1$` `Sheet1$`

union

SELECT `Sheet2$`.`Address `, `Sheet2$`.City, `Sheet2$`.`Company Name `, `Sheet2$`.`Customer Name`, `Sheet2$`.`Phone Number`, `Sheet2$`.`Sales Rep`, `Sheet2$`.Zip
FROM  `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet2$` `Sheet2$`

union

SELECT `Sheet3$`.`Address `, `Sheet3$`.City, `Sheet3$`.`Company Name `, `Sheet3$`.`Customer Name`, `Sheet3$`.`Phone No#`, `Sheet3$`.`Sales Rep`, `Sheet3$`.Zip
FROM  `C:\Documents and Settings\ewatson\Desktop\THE TEST\MASTER`.`Sheet3$` `Sheet3$`

the phone heading is different in sheet3. paste this one in and see if it executes.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

Back to "Syntax error in FROM clause

 

[SkipVought]

So what you do if isolate each select...from SQL for each sheet and execute to see which one has the problem, first sheet1 then sheet2 then sheet3, each one at a time.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

Well I'll be ...
I changed the name of the column on sheet 3 to match 1 and 2... and it worked.

Now I wonder what will happen with 9 sheets. (argh.)

 

[SkipVought]

Its a one-time thing, but sometimes it takes a bit of sweat to get something this complex set up. But from then on, it works just fine, doing a Data > Refresh.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

I hope so, I'll have to set up the other sheets and make sure that they all do that.

...and then hope I can explain it to my boss..

 

[LizzyAnn]

.... They want to know if it's possible to color code...


On the bright side... I was able to add all 9 sheets and files, and host them on the server. =D

 

[SkipVought]

Color code WHAT?

Do you have the data on the sheet to make that determination?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[LizzyAnn]

They want to color code based off of who's list it is.

Basically, they're lists of contacts for our reps, so each sheet had the column with the rep's name in it.
So on the main lists that pulls from all the others, it would need to color the row based off of what the name in the column was.
And, as far as I can tell, conditional formatting doesn't give me enough variables. (ie, at least 9.) So, I'm assuming this is a VB problem??

 

[SkipVought]

yes VB. You can post in Forum707.

You will need to include a column in each SQL, for the SOURCE, as a literal.

SELECT `Address `, City, `Company Name `, `Customer Name`, `Phone Number`, `Sales Rep`, Zip[b], 'Sheet1' as 'Source'
[/b]
FROM `Sheet1$`

UNION

SELECT `Address `, City, `Company Name `, `Customer Name`, `Phone Number`, `Sales Rep`, Zip[b], 'Sheet2'
[/b]
FROM `Sheet2$`

UNION
....... etc....

or whatever literal value you want for each source.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}