dynamically linking cells to sheet, sorting independent

English
, ,
#1

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

#2

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.

#3

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…

#4

… 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).

#5

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.

#6

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

#7

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

#8

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.

#9

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.

#10

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.

#11

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.

#12

And I am still not sure what your original main concern was. Obviously my brain is inapt insofar. Do you feel sure you had understood the question if not in contact with the use-case in advance?
Did you aim at something like Update references when sorting range of cells? The option exists (since 4.3.4 ?), Or did you set this option inadvertently?
I am completely unable to understand in what way my real name and my nationality/location are of any relevamce here. Are yours?

#13

(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.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.