2 Data Connections, 1 Table

[Hillsy7]

Hi,

Quite a specific question, might even just be a 1 word answer (if it is, hopefully it's "no", as just answering "Yes" won't be that helpful).....

So currently I run 3 reports from 3 different systems that don't talk to each other. Previously, I would take the output from each and combine them in my working spreadsheet essentially as a single data source (the three reports are formatted so they all have the same fields and data types)...so far so good. I recently found I can make Data Connections so I can import the outputs directly into my working spreadsheet automatically as soon as I open it!! Awesome!!

However, so far I've only got this working for 1 output per sheet, whereas I need the 3 outputs to exist stacked atop one another so it acts as a single combined table on the same sheet.

.....Is this Possible??

....if so, How?

Any help is gratefully appreciated.

 

[RoryA]

Yes, and no.

You can put three tables one below the other but they will not actually be one table. Whether that is actually an issue or not depends on what you do with the data.

If you have 2010 or later, and the right version of Office (Professional Plus, or standalone Excel) you could use Power Query to merge the three tables on separate sheets into one true table (you wouldn't even have to have the three source tables on worksheets)

 

[Hillsy7]

Our IT here area bit aggressive so I'm pretty sure I won't be able to get an add-in past the admin privileges.

However, no it doesn't need to be a true table. My formulas use SUMIFs mainly, so as long as they are stacked and aligned, that's enough for me. So how do I do that??

Thanks a bunch!

 

[RoryA]

You just locate one beneath the other. The default settings for tables will allow the tables to expand and contract as necessary, as long as all the tables have the same number of columns.

 

[Hillsy7]

Ok Great - I'll give it a ding!....I use relative rather than absolute references then I presume?

 

[Hillsy7]

Worked like a charm!

Cheers RoryA