Forum Discussion
just filter specific rows and columns
- Oct 30, 2022
Try this:
=LET(filtered,FILTER(Combined_tblFinancials[[itemDescTr]:[2021/6]],Combined_tblFinancials[Symbol]=B1),INDEX(filtered,XMATCH(RowHeaders,INDEX(filtered,,1)),SEQUENCE(,7)))
Try this:
=LET(pullrows,CHOOSEROWS(Combined_tblFinancials,IFERROR(XMATCH(Sayfa1!A2:A148,Combined_tblFinancials[itemCode]),0)),CHOOSECOLS(pullrows,SEQUENCE(,6,2,2)))
Without access to CHOOSECOLS/ROWS, you might use:
=INDEX(Combined_tblFinancials,IFERROR(XMATCH(Sayfa1!A2:A148,Combined_tblFinancials[itemCode]),0),SEQUENCE(,6,2,2))
- Patrick2788Oct 29, 2022Silver Contributor
Thanks for sharing the workbook.
Here is the formula. I kept your filter but added CHOOSEROWS to pull the rows that match your subtotals. The subtotals are stored as a constant in the named item 'rowheaders'
=LET(filtered,FILTER(Combined_tblFinancials[[itemDescTr]:[2021/6]],Combined_tblFinancials[Symbol]=B1),CHOOSEROWS(filtered,XMATCH(RowHeaders,TAKE(filtered,,1))))
- deniztopcuOct 31, 2022Copper Contributor
Hi Patrick2788;
Did you manually enter the contents of the "Rowsheaders" constant?
This is not a problem, but others need an address.
In this, it writes directly to the line headers.
Is there a way to do this or did you type it manually?- Patrick2788Oct 31, 2022Silver ContributorYou could run UNIQUE on the item column then select the formula in the formula bar and press F9. This will give you the constant to put in a named item.
- deniztopcuOct 30, 2022Copper Contributor
hi Patrick2788
Thank you for your efforts, but I think there are functions that are not supported by my excel.
in the code it is showing this message: "_xlfn."
#AD? = #NAME? error
described on this site:https://support.microsoft.com/tr-tr/office/sorun-bir-xlfn-%C3%B6n-ek-bir-form%C3%BCl%C3%BCn-%C3%B6n%C3%BCnde-g%C3%B6r%C3%BCnt%C3%BClenir-882f1ef7-68fb-4fcd-8d54-9fbb77fd5025
What path should I follow?
- Patrick2788Oct 30, 2022Silver Contributor
Try this:
=LET(filtered,FILTER(Combined_tblFinancials[[itemDescTr]:[2021/6]],Combined_tblFinancials[Symbol]=B1),INDEX(filtered,XMATCH(RowHeaders,INDEX(filtered,,1)),SEQUENCE(,7)))