I have a sheet that shows the results of data looked up in a table using the formula
=IFERROR(INDEX(Sheet5!$A$2:$A$37,AGGREGATE(15,6,((ROW(Sheet5!$B$2:$B$37)-ROW(Sheet5!$B$2)+1))/((Sheet5!$B$2:$B$37=$B$3)),ROWS($A$1:A1))),"")
Sheet5 contains a table that has A)Room, B)Person, C)Date, D)Supplies. So essentially the formula is matching a user inputted Person and listing all the rooms they have booked. The formula works great so that's not an issue. What I'm having trouble with is my userform. I created a userform to allow people to enter in their bookings and update the table in sheet5 and it works fine to add the new records. However the Table is now 52 rows of data but my formula doesn't update to 52, it stays at 37. If I manually insert a row in the table the formula auto updates. How can I get the Userform to do the same thing?
The userform finds the first empty row in sheet5 and adds the values from the boxes to the row .Cells(row, 1).Value = Me.Roominput.Value so on and so forth
=IFERROR(INDEX(Sheet5!$A$2:$A$37,AGGREGATE(15,6,((ROW(Sheet5!$B$2:$B$37)-ROW(Sheet5!$B$2)+1))/((Sheet5!$B$2:$B$37=$B$3)),ROWS($A$1:A1))),"")
Sheet5 contains a table that has A)Room, B)Person, C)Date, D)Supplies. So essentially the formula is matching a user inputted Person and listing all the rooms they have booked. The formula works great so that's not an issue. What I'm having trouble with is my userform. I created a userform to allow people to enter in their bookings and update the table in sheet5 and it works fine to add the new records. However the Table is now 52 rows of data but my formula doesn't update to 52, it stays at 37. If I manually insert a row in the table the formula auto updates. How can I get the Userform to do the same thing?
The userform finds the first empty row in sheet5 and adds the values from the boxes to the row .Cells(row, 1).Value = Me.Roominput.Value so on and so forth