Connecting Files and Adding Columns

**TanyaMc** · 09-18-2018, 11:22 PM

I have two files that I download from a data warehouse. They have a column in common (Number). I am trying to automate the end result by connecting and loading files so all I have to do is download new data and open my report file to get updated results. I want to select most columns from one file, and then pull in two fields/columns from the other file based on the matching column (unique numbers).

Simplified example (there are several more columns in each of the actual files)

File one/Sheet1: Number Att1 Att2 Att3

File two/Sheet2: Number Att4 Att5

Report File: Number Att4 Att5 Att2 Att2 Att3

Is this possible to automate as described?

Thanks.

**JeteMc** · 09-24-2018, 01:05 AM

Perhaps this will help.
On sheet3 A1:A10 are populated using: =IF(Sheet1!A1="","",Sheet1!A1)
B1:K10 are populated using:

Formula:

Please Login or Register  to view this content.

Zeros are hidden using conditional formatting.
Let us know if you have any questions.

**TanyaMc** · 09-24-2018, 09:59 PM

Thank you!

How would this work if the files were connected, not pasted into worksheets? Or should I say how do I connect the files without loading into the worksheets?

**JeteMc** · 09-26-2018, 09:01 AM

I don't believe that formulas can accomplish this directly from the data warehouse files. It may be possible using VBA but I am not sure about that as I am not versed in VBA coding. I would suggest that you mark this thread as 'Solved', using the thread tools drop down in the ribbon above your first post, then open a new thread, addressing this question, in the Programming/VBA/Macros forum. Provide a link back to this thread so that contributors can see what has been done so far.
Let us know if you have any questions.

**TanyaMc** · 09-26-2018, 11:29 PM

My original question was how to do so using connected files. From my review of information available, it appears to me the query/connections components in Excel (we have 2016) should work without VBA. I am not sure our system even allows us to use VBA (my experience is that most anything like that is locked down for security reasons.) So I am reluctant to consider this answered or post in that forum since the solution would potentially not be viable for me.

**JeteMc** · 09-27-2018, 08:47 AM

We may be able to be of more help if you could upload small desensitized samples of the files from which you want the data extracted.
To upload a file click on the GO ADVANCED button below the Quick Reply window and then scroll down to Manage Attachments to open the upload window. You'll see the size limitations for the different types of files at the bottom of that sheet, please upload small files as only a few examples should be needed to determine how/if we can help.
Let us know if you have any questions.

Connecting Files and Adding Columns

LinkBack

Thread Tools

Rate This Thread

Display

Connecting Files and Adding Columns

Re: Connecting Files and Adding Columns

Re: Connecting Files and Adding Columns

Re: Connecting Files and Adding Columns

Re: Connecting Files and Adding Columns

Re: Connecting Files and Adding Columns

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] adding bullets to cells and connecting it with line?

New user, need help connecting connecting and "adding up" text cells and number cells

[SOLVED] Merging two excel files, by adding columns

Connecting two files with a master directory for sequential numbering

Adding columns from closed files based on cell reference

connecting Excel files with VLOOKUP

Excel 2007 : Connecting empty cells AND adding a trendline no longer possible in 2007?

Bookmarks

Bookmarks

Posting Permissions