Forum Discussion
Solved
Formula Error
Need guidance on the formula errors in the below workbook. Trying to capture all of the data for the "Open Invoices" section. Columns Q and R after row 29 are the issue. Any assistance would be ...
- I updated the formula. There were a few issues. As sort of noted by Detlef, your referenced range was limited to only a portion of all the data and hence only 21 invoices showed up. You also have a column AC that has a unneeded and overly complicated calculation. I changed col. Q to directly look at column M and look at the whole set of data and now it shows all the results.
That all said, all of this can and should probably be updated to use the new FILTER() function instead of the AGGREGATE to find the rows and multiple INDEX to copy each value. It could all be achieved with 1 formula in R9 something like:
=FILTER($B$9:$H$4320, $M$9:$M$4320>0)
but to do that I would have to delete everything else there and didn't want to mess with your sheet that much.
I updated the formula. There were a few issues. As sort of noted by Detlef, your referenced range was limited to only a portion of all the data and hence only 21 invoices showed up. You also have a column AC that has a unneeded and overly complicated calculation. I changed col. Q to directly look at column M and look at the whole set of data and now it shows all the results.
That all said, all of this can and should probably be updated to use the new FILTER() function instead of the AGGREGATE to find the rows and multiple INDEX to copy each value. It could all be achieved with 1 formula in R9 something like:
=FILTER($B$9:$H$4320, $M$9:$M$4320>0)
but to do that I would have to delete everything else there and didn't want to mess with your sheet that much.
That all said, all of this can and should probably be updated to use the new FILTER() function instead of the AGGREGATE to find the rows and multiple INDEX to copy each value. It could all be achieved with 1 formula in R9 something like:
=FILTER($B$9:$H$4320, $M$9:$M$4320>0)
but to do that I would have to delete everything else there and didn't want to mess with your sheet that much.
Oh and it would also help you you defined the original data table as a table and then the range would would automatically scale up and down as the size of the table increases/decreases.