Excel Help Please

[Entai]

I have numerous excel workbooks all titled with a year (1982.xlsm, 2001.xlsm, 2018.xlsm etc) there is about 50 in all.

I am trying to get data from all these and compile into one table.

The title of columns in the table is the year, I'm then compiling various data into rows under that column title

I am using an array formula like this so it will compile from closed workbooks,
{[=SUMPRODUCT(--('I:\Financial Data\Tax[1982.xlsm]Accounts'!$E:$E="Motor"))}

I was wondering if it is possible at all to use the column title as part of the formula?

Something like
{[=SUMPRODUCT(--('I:\Financial Data\Tax[TAKE TITLE OF COLUMN AND PUT IT HERE.xlsm]Accounts'!$E:$E="Motor"))}

I know its probably very basic but my google fu is being defeated currently.

Thanks in advance for any help

 

[bremen1874]

https://business.tutsplus.com/tutorials/how-to-link-data-in-excel-workbooks--cms-28950

Edit: I don't think I read your question properly so that probably isn't very useful.

 

[Entai]

https://business.tutsplus.com/tutorials/how-to-link-data-in-excel-workbooks--cms-28950

Edit: I don't think I read your question properly so that probably isn't very useful.

Thanks for the quick reply, but yes I do not think that is quite what I am after.

Basically I have around 50 excel work books all called by a year so 1982.xls, 1996.xls, 2000.xls, 2019.xls, etc etc

I then have a table with a number of columns names 1982, 1996, 2000, 2019, the data in each column comes from the workbook with that year as a title.

I am currently using the formula array

{[=SUMPRODUCT(--('I:\Financial Data\Tax[2019.xlsm]Accounts'!$E:$E="Motor"))}

But instead of manually altering each reference to the workbooks by manually changing 2019.xlsm, to 2017.xlsm each time I was wondering if there is a simple way of making it do something like this

{[=SUMPRODUCT(--('I:\Financial Data\Tax[AUTOMATICALLY INSERT CONTENT OF CELL B17 or C17 or D17 HERE.xlsm]Accounts'!$E:$E="Motor"))}

Then I can simply highlight the cell containing that formula and drag it to the right filling in all the cells to the right using the contents of the column title in each column to fill in the years within the workbook name to reference the different workbooks.

Hope that might be a little clearer.

EDIT: More I read up on it, I am beginning to think the only way will be by using some VBA in a macro, but that just seems a little over complicated for what in essence is a simple thing.

 

[chroniclard]

Would CONCAT inside your formula work?

=CONCAT("I:\Financial Data\Tax", B7, ".xlsm")

Where B7 is the year.

results in

I:\Financial Data\Tax1990.xlsm

Might have an issue copying to the right, years would have to be left to right columns

 

[Entai]

Would CONCAT inside your formula work?

=CONCAT("I:\Financial Data\Tax", B7, ".xlsm")

Where B7 is the year.

results in

I:\Financial Data\Tax1990.xlsm

Might have an issue copying to the right, years would have to be left to right columns

Sorry nesting is a real bugbear of mine, how would I nest the CONCAT within the SUMPRODUCT to get it to work

have tried

{[=SUMPRODUCT(--(CONCAT("I:\Financial Data\Tax",B7, ".xlsm")Accounts'!$E:$E="Motor"))]}

But keep getting errors, think it is not seeing the arrays within the SUMPRODUCT function properly but am at a loss as to where the different sorts of parenthesis need to be.

 

[chroniclard]

hmm, had a bit of a play using concat and indirect, but indirect only works if all the workbooks are open. Suspect may need to utilise some VBA for this.......unsure.

Is it too much to create one giant spreadsheet, one tab per year?

 

[Grrrrr]

Have you considered using power query to import data from multiple files?

 

[Entai]

Sorry been away for a while didn't notice the last two posts in here

hmm, had a bit of a play using concat and indirect, but indirect only works if all the workbooks are open. Suspect may need to utilise some VBA for this.......unsure.

Is it too much to create one giant spreadsheet, one tab per year?

Not possible to make one giant spread sheet as each workbook under year name has multiple work sheets within it, we are looking at around 63 work books each with between 18 and 24 work sheets inside, hence trying to come up with something simpler than copy paste by hand which it is currently.

Have you considered using power query to import data from multiple files?

Possibly but am a bit of a novice and would not know where to star with that.


Basically what I have is this

{[=SUMPRODUCT(--('I:\Financial Data\Tax[2019.xlsm]Accounts'!$E:$E="Motor"))}

Which is copy pasted into different cells where i manually adjust the year and the worksheet name (such as Accounts) in each cell to pull in the data.

The cells I am copying into are row titled by Accounts etc

And Column titled by Year

What I woudl like is a formula or something that can be highlighted and dragged to the right or dragged down to auto fill other cells and where it automatically adjusts said formula or whatever to incorporate the column year and the row title to pull data from that year workbook that row name worksheet into that cell.

 

[Grrrrr]

What you're trying to achieve isn't possible, I'm afraid. I've tried and failed myself. The closest you'll get is indirect, but, as you found out, all the source files need to be open.

If the files are all the same layout then importing data from a folder using power query is the way to go:

1. Go to Data > Get Data > From File > From Folder
2. Choose your folder
3. Choose: "Combine and Load to"
4. Choose the sheet you want to import from and tick the box that says ignore errors
5. After clicking ok you should be able to output to a table

There is a bunch of stuff you can do in PQ to process and tidy the data but hopefully just doing the above will get you something you can use. Just "RefreshAll" to pull through any new data.