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.
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.
I'm having another problem with the second sheet. In the second sheet, next to the three columns imported from the main sheet (and therefore surname, name and tax code) I added another column ("course enrollment date") and entered some data. Subsequently, if I insert other rows in table 1 (i.e. the main database) present on the original sheet, I find this change also in the secondary sheet (and this was my main need, which we achieved with the LET function). However, after this step, I no longer have the correct correspondence between the person (identified in the first three columns) and the registration date present in the fourth column.
Is there a way to ensure that the cells in the "registration date" column always follow the position of the rows in table 1?As you have found, there is no link between the columns returned by the formula, and the extra data you add to the right. It'd be best to add those data to the first sheet, and to expand the range returned by the formula on the second sheet.
- Thanks, I tried the formula and it works perfectly.
The only drawback is that if I do a search for a surname in the second sheet (the one where I inserted the table using the formula) it doesn't find anything.If necessary, click Options >> in the Find dialog.
Then, select Values from the 'Look in' drop-down instead of the default setting Formulas.
- Thank you. Your contribution is truly valuable.
Don't be tempted to add further data fields to the output though. Data read by formula will move as changes are made on the first sheet so the additional data will no longer appear as part of the appropriate record. Additional fields need to be in a separate table and linked using XLOOKUP and a primary/foreign key combination (or Power Query and a join to combine tables).
