Forum Discussion
LINEST() function update when using filter on tables
Hello everyone i have been trying to figure out how to update my LINEST() range when i apply a filter to the data in my table. I have asked many forums and watched many videos, none seem to be able to address this problem?
=LINEST(_830E_AC[Al],_830E_AC[oilhours]^{1,2},TRUE,FALSE)
=LINEST(Offset(Sheet2!$AB$2,0,0,CountA(Sheet2!$AB:$AB),1)),(offset(Sheet2!$M$2,0,0,CountA(Sheet2!$M:$M),1))^{1,2},TRUE,FALSE)
None of the above options seem to work. Any ideas?
https://www.dropbox.com/scl/fi/0v4ej20p2o4qh44rvu785/Book1.xlsm?dl=0&rlkey=icmhad5vmhj52raorln2u48z3
2 Replies
I'm not a PT person. But does GETPIVOTDATA solve the problem?
If not, I suspect that it would help (someone else) if you provided an example Excel file that demonstrates the problem.
Ideally, click "browse" to attach the Excel file. Alternatively, upload an example Excel file that demonstrates the problem to a file-sharing website, and post a download URL that does not require that we log in. I like box.net/files; others like dropbox.com. You might like onedrive.live.com because it uses the same login as this forum.
If the forum does not allow you post URLs (yet), edit the URL so that it does not look like a URL. For example, the edited URL for this thread is
techcommunity dot microsoft dot com /t5/excel/linest-function-update-when-using-filter-on-pivot-tables/m-p/3739692#M180491
https://www.dropbox.com/scl/fi/0v4ej20p2o4qh44rvu785/Book1.xlsm?dl=0&rlkey=icmhad5vmhj52raorln2u48z3
Had a look at GETPIVOTDATA i am dont think that is the solution for this.
