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 awesome!
https://hiyainc-my.sharepoint.com/:x:/p/josh_hornbaker/ESC_bO_CUCBOmEwXCHbHAKIBWQ-MQ5PZ4FkLB4H5d6hnEg?e=E4adqN
- 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.
5 Replies
- 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.- Awesome! Thank you.
I've updated the data to be a table and removed the redonkulous formula from AC (thanks excel 2003 brain 🙂 )
And by "delete everything else there", do you mean just all of the data? And then input the formula so it has blank cells to spill into?- OK so I updated the sheet and it got a little 'complicated' but at least it is table driven now. That said a couple of things that were done and should be done:
a) the output of an array formula can't be in a table (i.e. columns R:X should NOT have been defined as table 3)
b) tables 1 and tables 2 should be 1 table to make sure they stay the same size
c) I used R9# in cols Z:AB to continue with the automated ranges
- 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.
I do not see a problem since you captured all of the data for the open invoices.
Exactly 21 invoices.
