1

Topic: EXCEL sorting with saving of formulas

There is here such label:
,A, B, A C, D, E
1, , in days, date ., date a game., the total
2, Ivanov, 4,43132, =C2+B2,100
3, Petrov, 2, =D2+1, =C3+B3,300
4, Sidorov, 7, =D3+1, =C4+B4,200
Essence of formulas that is set in days, the period beginning (it is anchored to the termination of the previous period + one day) and the period termination (the period beginning + set in days)
Prompt how correctly to do sorting that the binding of formulas remained on a place?
For example, if to sort across the field the Total that Sidorov's date started remained it is anchored to Petrov's expiry date.
Now at sorting all formulas remain on places. If before row number to add a sign "$" (D$2+1) as all formulas remain on places.
And it is necessary, that at formula sorting were moved after the line, as well as all remaining data this line.

2

Re: EXCEL sorting with saving of formulas

It is necessary to sort a copy of this table

3

Re: EXCEL sorting with saving of formulas

Dimonka so to me then it is necessary to work with the initial table and all its formulas. And if I create a copy, there and formulas will not be, and there are only values.
Or you another mean something?

4

Re: EXCEL sorting with saving of formulas

So if I correctly understand, you want, that sorting changed formulas (a dial-up of operations), and not just arguments of formulas.
That is that the formula
=D2+1
Became the formula
=D8
Or
=D2-5
So?
Well you want much. Sorting will not change operation in formulas. So it is necessary to reconsider the approach

5

Re: EXCEL sorting with saving of formulas

Though is not present, forget, I confused it
Simply give an example result

6

Re: EXCEL sorting with saving of formulas

Shocker. Pro,
In my example if it is sorted by the total the result should be such:
,A, B, A C, D, E
1, , in days, date ., date a game., the total
2, Ivanov, 4,43132, =C2+B2,100
3, Sidorov, 7, =D3+1, =C4+B4,200
4, Petrov, 2, =D2+1, =C3+B3,300
I.e. formulas should be moved with "the" line

7

Re: EXCEL sorting with saving of formulas

987;
What for such complexities? Transform formulas into values (to select a range, Ctrl+c, right cliques - Values) and sort normally.
If needed the new column of serial dates is easy for creating autocompletion ("extension").

8

Re: EXCEL sorting with saving of formulas

wrote:

If needed the new column of serial dates is easy for creating autocompletion ("extension").

At it there not serial dates, with different intervals.
Can use the pivot table?

9

Re: EXCEL sorting with saving of formulas

And so, of course, structure of the data the bad. Is positional-dependent

10

Re: EXCEL sorting with saving of formulas

Shocker. Pro wrote:

And so, of course, structure of the data the bad. Is positional-dependent

That here the bad?
After all if I will interpose a line into the table all will be : formulas creep after the lines and all will as work.
And here sorting spoils all table.
Why the insertion works normally, and sorting (the same relocation of lines) - is not present?

11

Re: EXCEL sorting with saving of formulas

987 wrote:

Why the insertion works normally, and sorting (the same relocation of lines) - is not present?

Because these are principles of operation of formulas. When you interpose a line in available structure of the table the new element is added, and for remaining (lower) there is an offset of cell references on 1. It is the fixed value of shift which is easily enough implemented. And when do sorting - there is a mash of existing elements in table structure (it is added nothing) - someone upwards, someone downwards... And to trace correlation of formulas in such mash rather . If it has been implemented - even 100 lines most likely would be sorted unreasonably long. But hardly someone tried to implement generally it since initially tables are calculated for an accurate data structure and on that formulas in one column were identical (if to look at them in style R1C1). And if formulas differ - that arises an error "Nesoglasujushchajasja the formula" which shows that such formula can incorrectly work in certain situations. Which including table sorting concerns also.

12

Re: EXCEL sorting with saving of formulas

The_Prist , thanks for the torn answer
With the text I will more low disagree:

The_Prist wrote:

And to trace correlation of formulas in such mash rather . If it has been implemented - even 100 lines most likely would be sorted unreasonably long.

Expenditures of labor not such the big. Any jandeks-cards at the miscalculation of an optimal route expend much bigger efforts, but with it weak phone (they had a function an offline of creation of a route, truth, without corks) consults even. And here the whole table computer.
But you . places points over :

The_Prist wrote:

But hardly someone tried to implement generally it since initially tables are calculated for an accurate data structure and on that formulas in one column were identical (if to look at them in style R1C1). And if formulas differ - that arises an error "Nesoglasujushchajasja the formula" which shows that such formula can incorrectly work in certain situations. Which including table sorting concerns also.

It is possibly really connected to "abnormality" of such data, therefore there is no implementation.
On the other hand I can take any cell and drag it in any place. Thus all formulas referring to it as will be corrected.

13

Re: EXCEL sorting with saving of formulas

987 wrote:

expenditures of labor not such the big. Any jandeks-cards

It is not absolutely correct to compare in my opinion the navigator and the tabular editor. You do not compare skis and a saw by efficiency of maintenance? smile And expenditures of labor and  PC resources - different things.
Under expenditures of labor productivity for task implementation meant not, and complexity of implementation since at sorting of a line mix up and to trace for each cell of dependence very difficult and it would demand more likely operation not with an array of values, and directly with objects of cells (as it happens at the mentioned drag and drop) that would be on the order more slowly. Though it only my assumption. But even logically - for every line at each iteration of sorting it would be required to search and redefine dependences of all cells anew. I do not think that it would be quickly.
Plus, I so suppose that to anybody even in a head did not come to sort the formula on a level with values since formulas initially reflected for other purposes and are a little special property of a cell.
Generally, for the sake of interest simply look on the Internet for "fast methods of sorting VBA". Look, as there all is twirled (a vial it is not counted, it is the slowest algorithm). Then try  the same VBA to reveal for a cell with formulas (not only simple, and any formulas - difficult, by array formulas, etc.) to spread out everyone on cells all participating and dependent on it and for everyone also to define - it is fixed or not. Can then the judgement concerning simplicity of implementation hardly changes smile

14

Re: EXCEL sorting with saving of formulas

The_Prist , let's stop that all is known to me about sorting methods, links, pointers, arrays, etc smile
And I precisely know that it is absolutely simple to computer to be anchored to a cell where it would not be moved. And even to thousand such moved cells.
Most likely, in a microsoftware simply considered that in such sorted table too many formulas should be highlit green  (inconsistency of formulas)
Or that is even more probable: such sorting is, but as to use it we do not know. About what this subject is created

The_Prist wrote:

Plus, I so suppose that to anybody even in a head did not come to sort the formula on a level with values since formulas initially reflected for other purposes and are a little special property of a cell.

Here the task such: there is a table with a certain data. And in this table is auxiliary fields with formulas (calculation of the beginning and the end of the periods, and as communication of the beginning of one periods with the termination of others. I.e. something bearing a faint resemblance ProjectManager).
The table is necessary for sorting and filtering on different fields, thus that the periods remained in the lines as though there there are formulas, but only no value.
And thus, if period or a date started, or the terminations in any of lines that remaining lines were correctly enumerated changes.
The table small - all on one hundred-two lines and from tens fields. And  it is selected as the convenient tabular tool which ALREADY is present on the computer.
And to fence kitchen gardens with setting ProjectManager' or any DBMS, or still something - well it is simply silly.

15

Re: EXCEL sorting with saving of formulas

987 wrote:

The_Prist , let's stop that all is known to me about sorting methods, links, pointers, arrays, etc smile
And I precisely know that it is absolutely simple to computer to be anchored to a cell where it would not be moved.....

Oh-it...
Yes...
If so not less all can be known to you about a difference between the data and algorithms of operation with them.
Sort the data. And lines of operating algorithm, except the compiler, nobody rearranges.
And even when the compiler rearranges them, it is not called as sorting.
The guy than to wipe pants in fabrications without a wheel and sails on a subject
Savings of topological identity of space computing algorithm
At its arbitrary geometrical conversions;
Is better would copy the data, a current state of the space computational model;
On a single sheet.
And time released from topological thoughts you can spend
On a filtration and sorting of the data, instead of algorithms.
Sorting the data , in comparison with sorting of algorithms;
Essentially more simple task in itself, moreover and with built in in Excel implementation.
PS
It not zoom-in. Simply council. And - kind.

16

Re: EXCEL sorting with saving of formulas

booby wrote:

It not zoom-in. Simply council. And - kind.

Judging by your text above, you thought that I so  when wrote that am known to me about pointers, sorting methods, etc.
No, not  (here at a forum every second programmer or the manager of bases, or something else, with a head much more mine). It was simply strange to hear judgement that  it is difficult to sort formulas. To it it is not difficult, I answer.

booby wrote:

would copy the data <...> on a single sheet is better. And time released from topological thoughts you can spend for a filtration and sorting of the data, instead of algorithms.

I thought that in  it somewhere on a surface and me now prompt, what button to push to receive the wished.
But or nobody knows about this button, or it is not present. Then yes, it is necessary most to copy the data, to sort, and then hands to write formulas reversely.
All as during age-old times with punched cards and the big calculators

17

Re: EXCEL sorting with saving of formulas

987 wrote:

...
All as during age-old times with punched cards and the big calculators

Here after all ...

987 wrote:

it was strange to hear judgement that  it is difficult to sort formulas. To it it is not difficult, I answer.

To me are not interesting neither surprise, nor conviction.
It generally not a question.
Take, yes teach him as it is necessary.
For personally your, small a case, it does not make complexity even especially for you.

987 wrote:

Then yes, it is necessary most to copy the data, to sort, and then hands to write formulas reversely.

Here about hands - unconditionally I trust.
Without demanding proofs.
PS
You will be returned to a topic, do not forget to re-read about pants.

18

Re: EXCEL sorting with saving of formulas

booby wrote:

Take, yes teach him as it is necessary.

Yes taught already
Added crutches....
Added a field filled in sequence at sorting
In the field "date " (which it is considered as date . + 1) wrote such formula:
=INDEX (D:D;  (<a column with ordinal value of the sorted line>-1; B:B; 0)) +1
Thought that  itself normally makes. The subject can be closed.
Well or who knows, write  a normal method of sorting with formulas

19

Re: EXCEL sorting with saving of formulas

Use the relative formulas.
Use notation R1C1.
Eliminate columns of a C and D from a sorted range.
All.

20

Re: EXCEL sorting with saving of formulas

987;
Select in the table for example 3 line of the data, Ctrl+x, select the first cell in the first a line of the data, right cliques - to Interpose the cut cells. Communication of formulas was saved? It's cool! It is necessary to write a macro which defines row order after sorting in the necessary column and moves a line this method.

21

Re: EXCEL sorting with saving of formulas

987 wrote:

Dimonka so to me then it is necessary to work with the initial table and all its formulas. And if I create a copy, there and formulas will not be, and there are only values.
Or you another mean something?

You create a copy of the table which will refer to the original table. It will perfectly be sorted.

22

Re: EXCEL sorting with saving of formulas

Dimonka wrote:

You create a copy of the table which will refer to the original table. It will perfectly be sorted.

No, it not that
It is necessary to sort the table by one of fields (on , for example) to change any dates or in days, and then to return sorting reversely
Thus formulas of dates refer to the previous cells in the column
In general, it not so simply to explain and the excellent decision would be presence of possibility of sorting from a binding of formulas to moved cells.
But time  so is not able or we do not know about it, crutches with . and "an index ( have been invented (...".
Problem though and crookedly-slantwise-with-crutches, but it is solved. I think, if  was able to sort correctly formulas, we here would learn for a long time it.

23

Re: EXCEL sorting with saving of formulas

Means it is necessary to add at the left unique key (organic number for example therefore as  nonunique), and to replace direct reference formulas =D2+1 with formulas of the dynamic link on a key = (the organic; a column of the organic; 5; LIE) +1
will be noticeable to consider more slowly on the big array.