Google Spreadsheet Help w/array formulas from other sheets

[iilman92]

Hi everyone, please excuse my ignorance i am not sure if this is allowed here. I am seeking some advice or help on how to communicate between two different google sheets and how to keep consistent information across both. Essentially, we are working with a recruiting agency and I have created them a workbook in sheets that takes all of our information and dumps it into a tab via IMPORTRANGE that is hidden and protected that has all of our data. I then created a new tab and used the FILTER function from the hidden tab to display only the information relating to the agency. I have the FILTER function in between columns A-L, and O-R. Columns M,N,S,T & U are columns where the agency is entering information pertinent to the req. My issue is that since i'm using the FILTER function (as an array), when there is a new requisition added to the list automatically, columns M,N,S,T & U's data doesn't stay consistent with the rows. If information is added, the row will move down but the data in the blue highlighted areas remain the same. The same principle if you have a column that is 'outside a filter range'. Does anyone know what would be a better remedy to this?

Screenshot 2025-02-20 143949.png

[Bernie Deitrick]

You have a few options:

A)

NEVER remove data from your data source, and have the FILTER function return a repeat-ably sorted list that adds the newest values to the bottom.

B)

1) Select your formula results
2) Convert them to values
3) Add a FILTER formula _below_ the data set that you just created, and return data for the agency BUT only records that do not already exist in the table above

optional: remove entire rows from the constant set of data to keep the returned data set in sync with the data source

When that formula returns additional values, rinse and repeat the above 3 steps.

C)

Create a separate table with a column of key values - as constants - and have the extra information entered into that table. Then add those values into your other table by using formulas.

[iilman92]

Thank you for the help!! I will try these and hope to get a solution!

[iilman92]

You have a few options:

A)

NEVER remove data from your data source, and have the FILTER function return a repeat-ably sorted list that adds the newest values to the bottom.

B)

1) Select your formula results
2) Convert them to values
3) Add a FILTER formula _below_ the data set that you just created, and return data for the agency BUT only records that do not already exist in the table above

optional: remove entire rows from the constant set of data to keep the returned data set in sync with the data source

When that formula returns additional values, rinse and repeat the above 3 steps.

C)

Create a separate table with a column of key values - as constants - and have the extra information entered into that table. Then add those values into your other table by using formulas.

Hi Bernie,

What formula could i write in order to do this:

Add a FILTER formula _below_ the data set that you just created, and return data for the agency BUT only records that do not already exist in the table above

[Bernie Deitrick]

The filter formula would be of the form of this, entered into cell A8 (with values in A1:D7), where your original data is in column A to D of sheet 'Data Source', with key values in column A:

=LET(d,FILTER('Data source'!$A:$D,ISERROR(MATCH('Data source'!$A:$A,A1:A7,FALSE))),FILTER(d,INDEX(d,,1)<>""))