Forum Discussion
Three columns of a database in another sheet and always updated
HI. I'm new to this forum and I have the following need.
I have a database where I collect student data when they enroll.
I would like three of these data (surname, name and tax code respectively) to also appear in another sheet and to be updated whenever they are modified in the main database.
Furthermore, I would like that by adding or removing some rows in the main database (so if I add or remove some students), the same changes would also occur in the other sheet showing the various surnames, first names and tax codes. I tried with the Power query option but it doesn't seem to work.
Thank you.
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.
If possible, convert the data to a table.
You then need only a formula or formulas in a single row on the other sheet. These formulas should refer to the table columns.
Let me know if you need more detailed help.
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?
