3

I have a spreadsheet, with this linking all the subsheets to a "main" sheet.

=QUERY({
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/<id>/edit", "custom!A:E");
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/<id>/edit", "hebrew!A:E");
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/<id>/edit", "sanskrit!A:E");
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/<id>/edit", "arabic!A:E");
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/<id>/edit", "swahili!A:E");
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/<id>/edit", "tibetan!A:E");
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/<id>/edit", "chinese!A:E");
  IMPORTRANGE("https://docs.google.com/spreadsheets/d/<id>/edit", "inuktitut!A:E")
}, "select Col1, Col2, Col3, Col4, Col5 where Col2 is not Null order by Col2")

It is showing validation errors properly, but only in the main sheet (not in the subsheets).

enter image description here

In addition to showing the "master" list with everything (which makes it easy to check for validation errors), I would like for the validation error to propagate back into the subsheets. This is because I work directly on the subsheets, not the master sheet. I want to know when I am editing a subsheet if I have encountered a validation error globally (without having to switch back and forth to the main sheet whenever I add a new row). So I would like the validation error to show up in the column with the error, in the subsheet.

How can I accomplish that?

Notice "pasal" in the image is highlighted. In the main sheet, it shows the validation error, in the subsheet (hebrew) it does not. I want it to show in both sheets somehow. If it's not possible, please explain why. If it is possible, just outline the general approach for me so I can figure out the code (or if you know what commands/code to add, that would be great too).

Improve this question
1

1 Answer 1

Reset to default
2

The easiest solution would probably be to work the other way around — do all the editing in the main tab. To make that work, add a language column in the main tab, fill it with the language codes you are using, and make the data in the main tab editable through Edit > Paste special > Paste values only. You can then use filter views to decide which rows to show at a time, and would not need the language specific tabs for editing at all. You can still display them by using query() "the other way."

Filter views let multiple simultaneous users sort, filter and edit the sheet without disturbing each other. For additional ease of use, you can insert links in the frozen section of the sheet to easily switch between filter views, instead of having to go to Data > Filter views to switch. See the Filter views example spreadsheet for an illustration.

If you absolutely need to continue copying data from many tabs to the main tab, put the validation formula in a column so that it gets written to cells in the sheet instead of just being shown visually as validation errors, and copy the validation result to the other tabs using the arrayformula(iferror(vlookup())) pattern. You can then use a conditional formatting custom formula rule to highlight rows where there is a validation issue.

Since all the data is in the same spreadsheet file, you should not be using importrange() in the first place. Use an { array expression } to copy data from one tab to another instead, like this:

=query(
  {
    custom!A1:E;
    hebrew!A1:E;
    sanskrit!A1:E;
    arabic!A1:E;
    swahili!A1:E;
    tibetan!A1:E;
    chinese!A1:E;
    inuktitut!A1:E
  }, 
  "where Col2 is not null order by Col2", 
  0 
)
Improve this answer
4
  • Can you elaborate on your second-to-last paragraph, about how to do the main-to-subsheet binding? I don't quite see how to implement it, maybe you could provide the code/commands?
    – Lance
    Sep 25, 2022 at 9:49
  • I would recommend filter views instead of fighting with the issues your current spreadsheet structure presents. The "look-back" pattern will cause circular references, which are always best avoided.
    – doubleunary
    Sep 25, 2022 at 11:36
  • Alright I set it up with filter views as per instruction. Pretty nice because the validation errors still show up in the filtered view. However, in my filter view I added "Sort A -> Z", which is fine. But when I edit a cell in that column, it should change its alphabetic sorting, I would expect. However, it just disables the sorting on the filter view. So I have to turn it back on again... How do I enforce sorting in a particular filter view?
    – Lance
    Sep 25, 2022 at 15:19
  • Filter views support sorting. Click the arrow in a column label.
    – doubleunary
    Sep 25, 2022 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.