Ask Your Question

dynamically linking cells to sheet, sorting independent

asked 2018-08-06 10:26:02 +0100

Tass gravatar image

updated 2018-08-06 18:08:51 +0100

Hi all,

I am not sure if I just can't see the forest for the trees but I am trying to link a particular subset of one sheet to another and can't figure out how to do it. So, step by step:

I have sheet 1, which has my complete dataset. The dataset has about 90 observation in columns and about 9000 cases in rows. All of these 9000 cases have data entries for 30 observations, but for the remaining 60 observations (columns) only one quarter has data entries. Or in other words, 1/4 has a complete set of observations and 3/4 only have some variables measured.

I want to keep this dataset intact because while cleaning and modifying data it is convenient to have them all in one place, especially as some data depend on each other (calculated observations). But I want to create linked sheets that have the respective subgroups (those with all observations and those with only some) listed separately so that I can work with them for analysis while not having to change all sheets separately in case I find another issue or want to add another variable.

I tried to do that using an array but that obviously creates a huge mess once you sort data in the complete set as the array links to a fixed block. Linking more than 2000 lines manually is also not an option. In the end I need to link the specific cell content independent from its location. However the problem does not look so difficult, so I have the feeling I am missing something quite simple. Could you please offer some solution?

Best, Tass

edit retag flag offensive close merge delete


The issue has more than one aspect, and the explanations aren't as clear and complete (to me) as you may think.
To get useful advice you should prepare and attach (by editing your question) a reduced example containing about 20 typical data rows of the different types and the formulas or whatever you tried without the expected success. Add the expected results, too. Don't miss to exemplify also some different sorting you want to apply.

Lupp gravatar imageLupp ( 2018-08-06 11:35:21 +0100 )edit

Well, if I could post an example I would not need to ask how to do it. I think the nature of the data row seems irrelevant, so a typical data row can be any. It is about the principal of having some rows out of a dataset linked to another sheet and make sure that in this second sheet always the same content is mirrored independent of the sorting of the sheet of origin. If I have 3 cases A, B and C which I want to be shown in my second sheet I want them to be shown also when I sort descending...

Tass gravatar imageTass ( 2018-08-06 16:15:05 +0100 )edit

... and not have then the cases Z, Y and X shown. It is just that. Does that make it clearer? I want that the "address of the content" is copied, not the "address of the cell" in which the content is written (e.g. A2).

Tass gravatar imageTass ( 2018-08-06 16:16:30 +0100 )edit

Ok I found some other threads on similar topics. Man, this is a mess. It seems the major problem is related to the impossibility of creating exact mirrors of a data set if this set contains empty cells and 0. As it makes a difference in statistics whether a cell is empty (missing value) or the value is 0 I think I have no choice in this matter and have to adjust all later changes to "children" of my "parent" spreadsheed manually. That is really a bummer.

Tass gravatar imageTass ( 2018-08-06 19:20:45 +0100 )edit

Really surprising that nobody came up with a function that allows the exact mirroring of values.

Tass gravatar imageTass ( 2018-08-06 19:22:38 +0100 )edit

Do you just want to whine or would you prefer to get advice?
If the latter you should privide the needed info.

Lupp gravatar imageLupp ( 2018-08-06 20:30:10 +0100 )edit

Mr. Jäger, I have followed your contributions on this forum and where others seem to understand you need to insist on following certain self-made standards in a pedantic manner. It is nice that you try to offer advice, but how about if you stepped down from your pedestal and tried to understand also what people tell you instead of trying to force everyone to follow your thought patterns. Believe it or not, people outside Germany also have brains.

Tass gravatar imageTass ( 2018-08-07 09:03:55 +0100 )edit

Solved the problem partly by using =IF(ISBLANK($'sheet'.cell),"",$'sheet'.cell) in the target sheet. The sheet of origin cannot be sorted now, but the target sheet can. Good enough.

Tass gravatar imageTass ( 2018-08-07 09:08:14 +0100 )edit

This surely wasn't meant very friendly. Never mind!
I still would like to understand in what way the question was related to sorting.
As you stated I have contributed to this forum for a long time now, and there were isues concerning references to cells from ranges that got sorted. I just wanted to start my considerations concerning this question with sufficient information insofar.
In addition you mentioned the usage of arrays and a mess you got. Still not clear to me.

Lupp gravatar imageLupp ( 2018-08-07 14:58:12 +0100 )edit

The partial solution you mention seems to be about avoidiing to get a zero value (number) returned for a simple reference to a blank cell.
The way you do it is mostly sufficient. If you ask the cell containing the formula =ISTEXT(secondaryCellRef), it will answer TRUE while the primaryCell would answer FALSE on the same question if being blank. {=OFFEST(prmaryCellRef;0;0;1;1)} entered for array evaluation would avoid this. There are new disadvantages, however.

Lupp gravatar imageLupp ( 2018-08-07 15:30:12 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-08-07 15:58:48 +0100

Lupp gravatar image

(This is not an answer but a "bump".) I reopened the question because it obviously not was answered.

Since I did not understand the question clearly enough, and didn't get the information I requested with my first comment, I would like to invite someone else -not bound by my pedantic standards and not needing to step down from any podest- to try an answer.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-08-06 10:26:02 +0100

Seen: 111 times

Last updated: Aug 07 '18