1

Say I have a Sheet with people info, containing their names and some other info. I add a filter to this Sheet to be able to sort alphabetically by lastname, or any other column.

Now I want to add more columns, but adding them in the same Sheet makes it difficult to visualize since we might have already a lot of columns. Using a different Sheet for that seems like a good idea.

I create a new Sheet called "Positions" to add their work positions and use ARRAYFORMULA to copy the Names and Lastnames columns. Then I add a new column for Position.

The problem comes when I change the order in the original Sheet. If I set the order to be Z-A on lastname, the Position Sheet columns for Lastname and Firstname will change, but the Position column will not, and I will have a mismatch of the information.

Is there any way to achieve this?

Sample Sheet: https://docs.google.com/spreadsheets/d/1gtjKdaTrXG8IFGiwaW6Pv_eeYJbEJT2WHu5HeSTlDRY/edit?usp=sharing

1

This is not doable as you imagine it, because the only link/reference between C column and an array of A:B column is row order which upon filter change on the first sheet is lost.

The solution would be to have all data in hidden columns of the first sheet and then call for them from the second sheet with =ARRAYFORMULA({Names!A:B,Names!D:D})

0

0

0

  • 1
    Thanks. This means that if I want to use Sheets as a simple database, all the data for a particular entity needs to live in the same row. That does not seem very scalable, but I understand Sheets is not designed for this. – LocoMike Mar 10 at 23:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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