3D Excel / Multiple values in single cells.

Hello everyone!



I have struggled with this problem for days and searched for the answer, I've found similar sounding things, but none of those solutions has helped.
I think this is the right place to create this thread.


So, my problem is, that I have already 2 dimensional sheet which has coordinates, but I need the third dimension because there are multiple values for each coordinate.

I have an idea what kind of solution might work, but I can't find the right way to express it.

My idea is somehow this kind formula for every cell:
=<the expression I don't know>(value 1;value 2;value 3;...;value n)

Which should show in the cell as:
value 1
value 2
value n

Or only by the first value.


The thing is, that I don't know how to express that.

Also I should be able to count formulas outside the coordinates by specific values, for example:
SUM of value 2's in coordinates 001:001 (A1) to 100:100 (CV100)


Because this is quite hard to explain, if you need any more details, I'll happily give more info.


BR
-Mike

Have you thought about just creating multiple tables of the same coordinates, but one for each value? Or is there a specific reason you need them to be together? You can join the values, but to separate them back apart and work with them would be difficult.

Yes, I have thought about that, but if there is formula for this problem, it's a lot more easier and convenient to do in one sheet.
That is, because there are various amount of values in cells, from 5 to 12, so the 6th and more sheets would be half empty from spots.

And also I don't find the browsing of tables in order to look values for one coordinate handy, as this way you would see everything important from specific coordinate at one glance.

But so the excel wouldn't understand that I want to sum for example the value 2's from specific column then, even if we managed to put all values in single cells?

-Mike

In that case, what if you did data entry (I assume you're manually inputting these values- if not, we can work with that too) in the form
value1;value2;value3;...;valuen
And that way you'd have an easy time entering/searching for data visually.

Then to pull specific values out for use in calculations, create separate tables (on another worksheet if that's better visually for you, and you could even hide it) using the SEARCH function to find the semi-colons and MID function to return the value based on the SEARCH formula result, so the values are available separately for use with your calculations. If you decide you like this idea and need help setting that up, let me know.

I didn't quite get that all you said.
I tried a couple of hours yesterday to find out how to work with that, I think I maybe did what you explained, but then it won't work in my case.
If it would work, then I didn't do it correctly.

The thing is, that when I tried to do the data entry, it uses another columns for the same row to store the values, when the all should be put in one single cell.

Did I get that right that your suggestion is to put all the values in separate sheets, and then somehow show and calculate with the values in the first sheet?

-Mike

IMO, a spreadsheet works best when you have only one piece of information per cell.

I haven't used them, but Excel does have the ability to work with 3D references over multiple tabs. http://office.microsoft.com/en-us/ex...010102346.aspx

Long term, I would think this could be a better solution than trying to cram multiple pieces of information into one cell.

Thank you Shorty, I'll try that, I'll post the results later.

-Mike

I would actually do the opposite: data on one page, extract and calculate on separate pages. I agree with MrShorty though: I like one piece of info per cell as well. I am not familiar with the 3D references, but I would try that route first!