N
N
NIZKIYZALIV2020-07-22 15:10:24
Google Sheets
NIZKIYZALIV, 2020-07-22 15:10:24

Reverse sync Google Sheets?

Good afternoon! There are 4 tables in Google Sheets. The first three tables (let's call them budget) contain projected expenses and the fourth table (let's call it final), in which all the data from these three tables fall and are synchronized into one table by conditional departments.
It was possible to transfer data from 3 tables to 1 general using the formula:

=QUERY({IMPORTRANGE(" ";"Registry!A2:G1000");IMPORTRANGE(" ";"Registry!A2:G1000");IMPORTRANGE(" ";"Registry!A2:G1000")};"select * where Col1 is not null order by Col2")

Thus, it turns out that all data is pulled into the final table from the budget ones, sorted by date from A to Z.
5f18296da6083628401032.png
5f1829805aa2f169617990.png
In the screenshots, red cells are cells that are filled in manually, blue cells are cells that are automatically transferred from one table to another. Tell me how you can write the reverse synchronization formula logically:
Information about the payment of a particular need indicated in one of the budget tables is entered in the final table and the formula transfers the cells with the payment date, status and month of payment to the budget table?
The difficulty here is that the order of the data is violated when transferred to the final table, since on the side of the final table sorting by date occurs and the data from all three tables are mixed with each other. I can’t find a formula in any way that will select the matching of the date + amount of the operation between the total table, where the data is entered and the budget table, where the information needs to be transferred, and only on the basis of this data rows H№-J№ are already transferred.
Below I am attaching screenshots of what should eventually turn out according to the table:
5f182c5d7154a530410367.png
5f182c6763509960852609.png
Tell me, is it possible to implement such functionality at all, and if so, with what formulas?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
M
mityayka1, 2020-07-22
@mityayka1

Implementation is possible only with scripts and with identification of fields by unique keys.
You can enter data directly into the cell, then transfer it to the right place and clear it in order not to interfere with the query function to output an array. But it is better to call the data entry window with hot keys when the line in which you need to enter data is activated.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question