How to create a 3D array in Excel

[Psycrcyo]

Hi everyone,

I've come across a problem and been trying to solve/optimise it now since the start of the week... Hope one of you has an answer!
I am trying to develop a tool to analyse market trends where several brokers release reports with various formats but all have the same Information over the years from different markets.
What I am attempting is to write something where, say the years are in the columns, markets in the rows and the broker in a different worksheet.
Question is thus, how would I sum, average, multiply, etc... all the values in a summary sheet in an effective manner? The list gets updated every 3 months and at the end of the year it gets summed up and added to the existing records... New criteria can be added to almost anything. I am trying to avoid something along the lines of (BrokerA!A1+BrokerB!A1)/AllBrokers and instead (SumCellA1)/NoSheets so that it remains "agile" and patch-up work carried out quick.

I hope that actually makes sense!

Thanks already!
Psy

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Have you looked at the 3D features that there are in the worksheet? Try this: https://support.office.com/en-us/ar...rksheets-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6

I don't know if it lets you count sheets but you can do this: =AVERAGE(Sheet1:Sheet5!A2)

Regards,

 

[Psycrcyo]

That is a brilliant support page!
Guess I was a bit fixated on using arrays or simply the wrong name!
Thanks for the great tip and warm welcome!

 

[RickXL]

You are most welcome.

Excel is packed with stuff - it is just a question of finding it!

Regards,