Welcome to the web's most helpful Online Free computer tips and tricks website. This site contains all Latest and Cool Computer tricks as well as some Internet Tricks, Registry tricks, Orkut tricks, Windows customize and Optimization tricks, Computer Tweaks. Solve your computer problems by browsing the website for related Computer solution. All tips work well in both Windows XP as well as windows vista or win 7 operating systems. Need some Computer Help? Then this website suites your must need. These windows and internet Hacks Tricks are safe and free from any Spy ware. Learn All Basic and Advanced computer tips and tricks and be a computer master. All the computer tips at Hack2011 are very interesting and are mainly meant for education purpose. Visit my new Computer Tips Blog and leave your comments and suggestions there. Enjoy your stay and read all the articles posted here.

How to Create and Use Three-Dimensional Spreadsheets In Excel 2010

For the most part, most people think of a spreadsheet as a two dimensional object, because there are rows and columns, and that’s pretty much it; but spreadsheets can also be created that have a third dimension by using tabs, like this:
Initial Example
In this scenario, a tab can serve as a third dimension. Think of your sheet as being a cube of numbers instead of just a 2-dimensional array.
To better help illustrate this, think of an apartment building that has three  floors, with each floor having a 4×4 array of tenants. Then to make it as simple as possible, think of each room as having one person in it with just one name; you’d wind up with a cube full of one named people. With a spreadsheet, the floors would all be 2-dimensional representations of a floor map; the first floor would like this:
First Floor
The second floor like this:
Second Floor
… and the third floor like this:
Thrid Floor
Each of these are a separate tab in a single worksheet. To make it more clear, you can change the names of the tabs from Sheet1, Sheet2 and Sheet3 to First, Second and Third, like this:
Show Tab Names
Now to use the 3-dimensional sheet you’ve created, you need to think of each person living in 3-dimensional space, with an address. For example, somebody named Rena lives on the third floor, in cell B2, so her address would be ThirdB2, or in spreadsheet terms Third!B2 (the exclamation mark signifies addresses between tabs).
So to use such a sheet you might add to your sheet something useful, like asking “Who lives in room X on the Yth floor?” So that the apartment manager could for instance, simply type in the address and come up with a name. In the sheet it would look something like this:
Example Usage
The cells with the circles represent where a user of the sheet would type first the room number, such as d4, then the floor, and then the sheet would give the answer, like this:
Whole Example
Jill lives in room d4 on the First floor, which you can see by looking at the First floor tab above.
Of course, to make the sheet give you the answer as seen above you have to use some Excel functions, in this case, you would first need to build the address by concatenating the floor name with the the cell address by doing this:
In this case, because the floor name is in cell K10, and the cell (room) address is in I10, we can make the address by concatenating them together using the “&” and adding a “!” between them to arrive at this:
results of concantenation
…in cell K16, which we chose at random to hold the results of our concatenation. Next, in the answer cell, (where it says Jill above) we type =INDIRECT(K16) to cause the contents of cell K16 to be interpreted as a cell address rather than plain text, the result is the answer we were looking for, Jill.
This is all just one example of how to create a 3-D spreadsheet, but the principles are the same for other applications as well.

No comments:

Post a Comment