Forum Discussion
Three columns of a database in another sheet and always updated
OK, so I'll assume that you have converted the entire data range to a table. By default, Excel names it Table1, but you can change this if you like. I'll use Table1 in the following.
When you want to add new rows, add them to (or directly below) the table.
Copy the headings Surname, Name and Tax Code to A1:C1 on the other sheet.
Enter the following formula in A2:
=LET(r, Table1[[Surname]:[Tax Code]], IF(r="", "", r))
If necessary, change Surname and Tax Code to the real column headings in your table.
The formula will spill to 3 columns and as many rows as needed.
Hi. Yes, it is possible to convert the data to a table. Do I need to convert all the database data into a table or even just the three columns that interest me?
Also, once I've created the table, what formula should I use in the other sheet?
I tried to achieve my goal by selecting the three columns in the database, copying and proceeding to the other sheet with "paste link". However, in this way, when I inserted a new row in the database, it was not reported in the other sheet, and when I deleted a row in the database, in the corresponding new sheet I found the #RIF error.
Convert the entire data range into a table.
Are the three columns that you want to see on the other sheet adjacent (next to each other), or are there other columns in between?
- The three columns are adjacent.
- I use Office 365
OK, so I'll assume that you have converted the entire data range to a table. By default, Excel names it Table1, but you can change this if you like. I'll use Table1 in the following.
When you want to add new rows, add them to (or directly below) the table.
Copy the headings Surname, Name and Tax Code to A1:C1 on the other sheet.
Enter the following formula in A2:
=LET(r, Table1[[Surname]:[Tax Code]], IF(r="", "", r))
If necessary, change Surname and Tax Code to the real column headings in your table.
The formula will spill to 3 columns and as many rows as needed.