Archive of Mr Excel Message Board


Back to Pivot Tables in Excel archive index
Back to archive home

4D data?

Posted by Jozef Marak on February 27, 2001 4:09 AM
Hi everybody,
I have the problem with the Excel data evaluation. Why? I would like to describe my problem in the example. I created Excel file containing my data arranged in the following way:
1. I have several worksheets (12, one for each month) in my file;
2. each worksheet contains 3D data arranged like this

Item Paid Tax
car 85 12
food 41 8
beverages 15 2

My problem is that I would like to see my data from this point of view
"How much did I pay for the food through the whole year?"
It means it is necessary to pick let say only one value (cell crossing "food" and "Paid") from each sheet (month) and to create the graph. I was not able to do this with Excel.

Is there any solution of the problem like this? I don't want to rearrange the table because the number of items is hundreds.

Any advice is very appreciated.

Thanks.

Joe

possibilities


Re: 4D data?

Posted by David Hawley on February 27, 2001 4:14 AM

Hi Jozef

I'm not sure I fully understand you but, If you have Excel 2000 a Pivot Table with a Pivot Chart amy suit your needs..

Dave

OzGrid Business Applications


Re: 4D data?

Posted by Jozef Marak on February 27, 2001 5:56 AM
Hi Dave,

thank you for your help.But when I have read your answer (and once again my description) I realized that one very important fact is missing (or is not explained in details). It is that I would like to see the "trend" in my data (in the whole budget). It means, the "month" is my X data and "Paid" (from each worksheet) is my Y data. I'm not interesting in the SUM (how much I spent during the whole year!!!) or AVERAGE (how much I spent monthly) or anything like that.
Pivot Table is OK if I would be intersting in that but I'm not. So your help is not solving my problem. But anyway, thank you.

I hope that this is now better explained what I'm looking for in Excel.

Thank you for any advice.

Joe


Re: 4D data?

Posted by GregA on February 28, 2001 7:45 AM

Joe,

I believe that you are going to have to create a new workbook page that contains all the data from each of the sheets, along with a "Month" field added, and then from that you will be able to sort/filter and create your chart.

This should't be hard to do. Just copy and paste each worksheets data to the new sheet, and as you do that create and copy the month for the data you just transferred. Then you can sort by category and month, and create the graph.

Greg


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.