Is it possible to use formulas instead of pivot tables to call data from a worksheet?

[wedd]

Hi excel experts!

I have a 2 part question...

Is it possible to use formulas instead of pivot tables to have the ability to call data from a worksheet to a blank worksheet?
I would like to be able to use formulas to do this rather than pivot tables.

The second part is that I would like to sum up data in my worksheets into monthly quarters and years which don't show absurd movements. I am a moderate user of excel 2007. I
but I'm not sure how to do this...would using the feature consolidation be a solution? Or would there be another solution to these interesting problems? I know this may be a question for an advanced user...

for both of my questions, is it possible to do this, if so how can this be done?
Would you know of any websites, blogs, books, examples you may of had resolving a similar problem or any other resources I could use t help me find a solution for both questions?

Thanks for your contributions :friends:







P.S unfortunately I cannot use macros for this particular problem.

[Capungo]

Please watch the video below to get an idea about the array functions. Watch the other related videos in the playlist too. You should know how to use at least Sumproduct function to get data to a blank sheet from another sheet...

http://www.youtube.com/watch?v=vxpeEf8MYaY

[wedd]

Thanks!

[Capungo]

You are welcome. I hope it helps...

I did a lot of data manipulation as you want to. The main course is just like;

1. Get a unique list of key values to a blank sheet by the advanced filter.

2. Use Sumproduct, Sumif, Countif, Offset, Address, Index, Match, Indirect and other required functions to pull the related data as you wish to compose.