Can somebody advise me how I can do this:
I have three identical spreadsheets detailing peoples work activity and I need to pull them all into one master spreadsheet. I can easily do it for one spreadsheet to the master, but how do I do it for all three?
Any tips and help would be most appreciated.
Many thanks
Last edited by Jon Thurston; 08-26-2010 at 12:53 PM.
Hi Jon,
Please title your thread with something useful to those searching the forum.
Then we'll be able to offer some help.
Cheers
CC
If you feel really indebted please consider a donation to charity. My preferred charity is ActionAid but there are plenty of worthy alternatives.
Cheers Charlie
Thanks for retitling your thread.
The best way of doing it is to work it the other way around - people input to one spreadsheet which you then filter out for reporting purposes (you'd add a column for which sheet each row belongs to).
Another way is a quick macro, but how well this works and how hard to get right depends on the details of the situation.
A third way is calling data import calls against the spreadsheets with clever use of SQL. This is clever, and very effective but is not for the faint-hearted...
A bit more detail about what you're doing and an upload example would help, then we could pursue one of these things.
Last edited by Cheeky Charlie; 08-26-2010 at 01:05 PM. Reason: Thanks
Ok, thanks for the tips, attached is what I'm trying to achieve.
I have three workbooks (Book1 etc), and the master sheet, and then the desired results.
So as you can see, I want to have a function whereby the information automatically feeds from the 3 workbooks to the master.
This would work:
http://www.excelforum.com/2234005-post18.html
Give it a go, let me know if you get stuck.
That's brilliant, thanks!
Unfortunately I'm falling at the first hurdle. To define the table, I'm highlighting the relevat cells, then selecting Insert, Name, Define, calling it Data and then clicking OK.
But, when I go to my master sheet to Import External Data, New Database Query, I select Book1 as the source but it says that no visible table is defined.
Sorry if this is a really obvious thing, as you can probably tell this is the first time I've done anything like this with Excel
Ignore my last post, I've sorted that bit, I'll give the rest a go now.....wish me luck!
I've managed to get stuck.
I've defined the tables and now just trying to get the code correct. I have followed the steps you kindly provided, but when I try to OK the code I get the attached error.
Have you come across this before? I've copied and pasted my code into the document.
Thanks
Sadly not,
What happens if you remove the code back to the first query, before the union?
Obviously, this did work for me in the other thread (but not for the OP) and is quite a robust way of unioning data, once established... but we may have to fold and go for VBA
It comes up with the same error message. I tried putting all the worksheets into one work book and then pull it to the master one from there but it still displays the same error.
oh, duh, I thought I'd checked your SQL more thoroughly - you don't want a comma after the last column in each query, the comma tells MSQuery - 'there's more columns to come' when the next thing is 'FROM' it can't find the 'FROM' column.
Obvious when you see it.
Bin the commas at the end of each select clause - you shouldn't need the sheets to be in the same workbook - that is part of the power of this solution - but keeping them together may be more practical until you've got the details right.
You could do some maths within the query if you felt so inclined... this really is a very clever solution.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks