Excel: A Tale of Two tables. One sheet. One table beneath another without tears, with multiple external or internal data sources.

Automatically move rows down to allow for expanding list.

My video shows every step of the following and more, so can be viewed in conjunction with this text.
(available when processing complete May 3, 2013)

By an accountant - how's that relevant?
I'm the guy that uses my results

1
Why this subject?
I am enthusiastic about Pivot Tables and Power Pivot (a.k.a Genesis) and the power of Business Information analysis being given to us in the user base. However, I am finding that MS Query is relevant from long ago and to this day as a tool.  It provides "point and click" functionality without being quite such the "black arts" that are the other Table generators. Also, Pivot Tables require the data to be presented such that what are often fields across the top, must be entered as all entered in one field. Articles have been written about this, which once complied with does make the variety of output very interesting. That said, sometimes I just want an answer now and I do not want to redesign my data, especially when presented with it from financial institutions (usually retail banks) as comma separated value csv files.


2
Who is this for?
The Excel "point & click" community and indeed the VBA community where SQL can save engaging in re-inventing the logic for queries.


3
Where? Country:

This will work for anyone anywhere.

4
Is any special "tech" involved?

Not that the PC user will or indeed needs to see this, but this technique uses ODBC so Apple Macs will have not work unless an ODBC driver is installed. My leaning is towards the Actualtech offering because the OpenLink offering is far too complicated for me to understand, I think it is an Enterprise level thing. I spent hours with both. In the end ActualTech support got me working, whereas OpenLink wanted $200/hr for further assistance.

I am not aware of any other MAC ODBC drivers, by all means add comments with such info, it is like gold dust and why I have added this section.
In any case all this is NOT RELEVANT FOR THE PC as it is built in by Microsoft.
This article is based on using a PC and Excel 2010.

The technique will work in every edition of Excel going back to the 1990s.


VIEW POINT

From the point of view of an ordinary "point and click" user (i.e. me), which means no programming is required.

EXPERIENCE REQUIRED?

Minutes to learn, a lifetime to master?

None really, says me - having used spreadsheets since 1984. I suppose a novice user will be out of their depth because we will be delving in to deep and dark sub menus and dialogues, but if a novice sticks with my point and click guidance you may be "ok".

UNDER THE HOOD

- a quick look

If you want to move your files about between folders then it will help (but NOT essential) to be familiar with editing folder and file names, so for example being able to change C:\Documents\ to read J:\desktop\ in three places and to edit your file's name in two places. These "dark places" where this is done are shown here:
The clicks are: Data-Connections-Properties-Definition
Look for where it says J:\DocumentsJ\TEST.xlsx in two places and J:\DocumentsJ in one.

Where it says "Command text:" you could in fact type in a full blown SQL Query if you know how. You might be an SQL guru, in which case have fun! For us lesser mortals we need make no entries or changes here at all and simply stick with "point & click" which is what creates this "command text" for us, including folders and filenames. What we are doing here is having a quick look under the hood/bonnet at the engine, so we know where it is, but need not touch it - not ever if we do not so wish because these processes are very quickly created from scratch on demand. Indeed having practice and becoming the more adept is probably a good thing.

CONSTRAINTS

Permitted: MS Excel.
Permitted: Point & click only.
Programming VBA Etc is not required, although it can be used to glue things together when life gets complicated or repetitive. What we are doing here works just fine without it.

Once constructed, which takes only a couple of minutes (maybe five tops) the resulting file can be re-used forever (whatever that is).


REQUIREMENT

Find a way to make Excel create two tables of varying length, so dynamic like Pivot tables, which can be placed in the same sheet such that the lower one is always just underneath the first one regardless of how many rows are in the first.

Pivot Tables will refuse, saying the second table is in the way.

WHAT'S SPECIAL ABOUT THIS?

Quite a bit, for me.

Every other table I have come across in Excel will not flex so as to allow anything underneath it to exist without risk of deletion or hampering expansion of the table. The old List Manager was good, but it was for data input not for reporting to another sheet.

So what is special is the dynamic rows output.
When you eventually see it you will immediately spot that it achieves its result by hiding unused rows. At first I thought that might defeat the objective, but after reflection it does not at all. We as users do not have to do anything to hide the blank rows. No VBA, nothing. It is designed in. That's special in my book.

The other thing that is special is that for the first time I am using the same Excel file for both data input and for reporting, so it is both it's own data entry and reporting file. As such we are using a "get external data" feature to make a file report upon content within itself. I had thought that at the least we would have to save the file so it could read from the saved version of itself, but it seems not. Make a change in the underlying data and it refreshes into the report sheet. Use Data-Refresh or Refresh All.

This article is the antithesis of my previous one. This one is about taking data from two sheets and outputting two reports in to one sheet. The other one is about taking data from one sheet and outputting it in to two sheets (it uses two files, but may be able to do the same in one file using the technique in this article).

STRATEGY

Build two MS Queries in to one sheet in the same file as contains the data.

This is using an external data feature to interrogate a file internally.
I.e. a contradiction in terms, but it works.


CASE STUDY

I have been waiting for such an example and today now have one. Original here.

The Question runs as follows:

There will be a couple of data sheets that will supply data to a summary sheet. The summary sheet will have some permanent labels (designated by having Xs around them, just in my example below, like XcompanyX). A specific company name will be selected from a dropdown which will be used to select / fill in the rows, getting the info from the other sheets.

Xcompany product and contact reportX
Xcontact name       phoneX
Jim                           123-4567
sam                          212-5467
....more rows will follow depending on company selected....

Xproduct #             descriptionX
21344                      handle
23432                      knob
.....more rows will follow depending on company selected.....

The labels for the products will need to move down or up depending on how many contacts there are for the company selected.

The Question does specify being able to pull down a company name and have BOTH tables report based on that name. This article does NOT address that feature, because it would over complicate the illustration of producing two dynamic tables in one sheet. I do point out where in the process this might begin to be dealt with.

THE PROCESS

Here is our test data, each pair of columns will be in its own sheet:

I have created a video of this process here:
http://youtu.be/QFdJo5qvqHw

The above Question requires ALL the constraints I have described above, hence an excellent choice for this case study.

TUTORIAL INSTRUCTIONS

Open a New Excel file.

and here we go, all point and click.

Copy/enter (the) test data in to two separate sheets, name sheets Products, Contacts and a blank one called Reports. I make the Reports sheet the front most.

As such we have now in the same single file two input sheets with data and an output sheet to receive the results.

SAVE THE FILE ! I use the name ProductContact.xlsx The reason it is important (critical) to save the file is that we need a copy to be there when we look for it in later dialogues. (i.e. a copy needs to be on the hard disk.)

Excel 2010 Menus click as follows:

Data
From Other Sources
From Microsoft Query FEAR NOT! We will at no time depart from the Excel user interface.

then in

Choose Data Source dialogue:

Excel files
OK

Select Workbook dialogue*:

Select
productcontact.xlsx (in the video this file is called rows-to-sheet.xlsm (it can be xls too)
OK

Query Wizard - Choose Columns

The sheets within the productcontact.xlsx file you saved and then selected above are listed.
Select the sheet that contains the CONTACT data and the two headings named therein.

Click " > " which transfers all (both) the column headings in to the right hand list box.

Click Next

Query Wizard - Filter Data

We are not using a filter, but this is where you could enter selection criteria - which can be made user friendly, but that is for another article.

Click Next

Query Wizard - Sort Order

Click Next (no sorting)

Return Data to Microsoft Excel (pre-selected)

Click Finish (but it does NOT finish here, keep going)


to

Import Data dialogue:

Select target for report, choose A2 so as to allow a row for a heading for the sheet.

Click "ok"


SIDE NOTE STARTS HERE

- IF you were to want to edit the folder names and file name this is where you would enter that process as follows (IGNORE THIS OTHERWISE)

Click Properties

Connection Properties dialogue

Click Definition tab and you will see the two boxes containing the text that can be edited.

SIDE NOTE ENDS HERE

At this point we re-iterate the above complete Query process once more with the changes shown below.
It gets much faster with practice, such that creating these on-the-fly can become a trivial matter so that editing definitions isn't ever really going to be necessary: it's quicker to just rebuild the tables as needed and let Excel do all the complicated stuff behind the scenes

for the second query, the only difference being as follows:

1-Before starting each query creation process, select A 16 OR WHATEVER ROW IS AFTER THE END OF THE FIRST QUERY, LEAVING AS MANY ROWS AS YOU MAY WANT TO BE USED FOR HEADINGS in your desired report output sheet, this selection simply presets the Cell the end dialogue offers as a location for your report, does not really matter if you forget.

The video also shows a check process (change the data) to prove it is all working: time index 09:00

Save the Excel file as THE SAME NAME YOU FIRST SAVED IT AS AND DO NOT CHANGE IT AND ALSO SAVE IT TO THE SAME LOCATION. These are the two constraints of this method. You can however change both provided you edit the details as shown in the "side note" above to change the folder and the filename. That is the only potential complication. Indeed a bit of VBA might be able to do this for you, but it really is not a necessity. I do not want to put you off if you do not write VBA, on the other hand if you do, then this is a good point to consider it; aside from writing the entire process using VBA of course.

All done. No VBA.

Use Data Refresh to show new results.

The video shows every step of the above and more, so would best be viewed in conjunction with this text.

Good luck!

Anthony


P.S. I just had to add this from "avoorheis"

If you mean the path that's set up in the query connection, it would be nice if it just could figure out that it's the path where the workbook came from, especially if someone is going to d/l and use on a different computer or from the network.
Did a quick search and looks like that possible with VBA,
http://www.mrexcel.com/forum/excel-questions/352309-relative-ms-query.html