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?