When you need to have a sorted list, you have a separate sheet that also references the master table. With that, you can use a LOOKUP formula to locate the right data row no matter what order the data are in.Īnother approach, that uses only references, is to have a master data table that never has the order changed, so references to that table will always work. The usual way this is managed (using a database) is to assign each data row some unique ID: a serial number or unique name. I'm not sure having the spreadsheet manage this is a good approach: as Lupp already pointed out, it's too easy for the spreadsheet to do the wrong thing. The result wa a new option allowing the user to choose their own preference with this respeect. The way back, however was blocked now by those meanwhile having developed sheets depending on the "update behaviour". After some discussion the "update party" gave in. Of course, many a user considered the new behaviour a bug destroying the functionality of their well designed sheets. ![]() They first entitled a developer to change the behaviour to the "Update references when sorting range of cells". (The sister software LibreOffice Calc got through a campaign regarding this topic in times of their vesrions V4.2. In addition the other behaviour, even with variations, is easier (imo again) to simulate with the help of formulae (helpers). ![]() And secondly because it is the behaviour the majority of Calc users expect. This firstly (imo) because it is - once accepted and understood - less error-prone in everyday use. AOO Calc does not change ("update") references when the source range is sorted. Software developers have to decide whether to do it the one way or the other. Next time the same user may look differently at the question becasue he is working on a situation where the different behaviour is "right" with respect to his intentions. This because he wants to "quote" it elsewhere.īoth these users will - in a given situation - judge that what he wants also is to be expected generally - and should be judged the right behaviour. And his reference is made to show the data of "top ranked" item again.Īnother user will want the reference to be stable under the aspect of the item it refers to. This because he is sorting now and then to make sure that the top ranked item again is in the first data row after some parameters were changed. One users may want a reference to be stable under the aspect of the referenced cells. It may depend on the intentions of the user. relative addressing.īut: Concerning sorts and references into the range that got sorted it isn't that simple to tell what are the right references and which are wrong. I don't think this is related to absolute vs. ![]() It can't be too complex to get a reference to track to the contents of the referred cell, can it? I'll need to successfully sort Sheets 2 & 3 as well. ![]() That makes Sheets 2 & 3 useless, and it's not practical to return to the same sort every time I want to use Sheets 2 & 3, because Sheet 1 is constantly being edited. The trouble is that when I perform a Sort on Sheet 1 (to see all the seed from a particular supplier, or of a kind of crop, etc), the reference sticks on the cell and row location, instead of moving with the variety name. Some seed has the same name, but is from a different supplier, or a different year, so I need to be able to track which exact packet I'm making notes on. My thought was that I could reference the variety name from Sheet 1 on Sheets 2 & 3. On Sheet 3, I have field tracking, and so on. On Sheet 2, I have my greenhouse tracking, so I need to see each seed variety again, as well as have information about greenhouse production. On Sheet 1 I have all the seeds I own, and assorted information about the variety and source. Howdy! I bet this is a simple one, but it's right in that spot where the tutorials don't have it, but it's too simple to come up in the forum enough for me to find.Īnyhow, I'm a small farmer, trying to do my seed inventory.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |