eirikdaude
Board Regular
- Joined
- Nov 26, 2013
- Messages
- 93
- Office Version
- 365
- Platform
- Windows
I have a spreadsheet in which I have several worksheets, each containing a table.
I then want to add another worksheet / table, in which I collate the data from the different sheets.
Atm, it looks something like this:
The formula in C13 is
As you can see, for each table I collect data from, I use the formula
which I then sum for all the tables I want to collect data from, and divide by the how many tables I collect from to get an average.
As you can see, this is already a pretty cumbersome formula when just collecting from four tables, as the number grows it'll get even more so. Is there some simpler way to handle this? Is Power Query an option (I have very little experience with it)?
I then want to add another worksheet / table, in which I collate the data from the different sheets.
Atm, it looks something like this:
The formula in C13 is
Excel Formula:
=(INDEX(Karbon[For.mnd.];MATCH([@Målepunkt];Karbon[Målepunkt];0);1)+INDEX(Anodemontasje[For.mnd.];MATCH([@Målepunkt];Anodemontasje[Målepunkt];0);1)+INDEX(Elektrolyse[For.mnd.];MATCH([@Målepunkt];PFA[Målepunkt];0);1)+INDEX(PFA[For.mnd.];MATCH([@Målepunkt];Tya[Målepunkt];0);1))/4
As you can see, for each table I collect data from, I use the formula
Excel Formula:
INDEX(Karbon[For.mnd.];MATCH([@Målepunkt];Karbon[Målepunkt];0);1)
which I then sum for all the tables I want to collect data from, and divide by the how many tables I collect from to get an average.
As you can see, this is already a pretty cumbersome formula when just collecting from four tables, as the number grows it'll get even more so. Is there some simpler way to handle this? Is Power Query an option (I have very little experience with it)?