I'm kinda new with formulas in Google Sheet and was wondering how can I pull this. I have a sheet where I get to list down different tables with different Keyword on a single row. For example, Keyword:Here, or on another table Keyword:There. I wanted to be able to filter all this titles with 'keyword'on it and be able to list it all down in another sheet.
For reference, I have prepared a Practice sheet.
I was only manually adding this together with their data. Means, from one sheet, I'll just put = then refer to the cell I wanted to import the value. I was wondering if we have any formula we can use that even if I add new table with "Keyword:xx" everyday, it'll automatically imported to another sheet.
You may try:
=iferror(choosecols(filter('Sheet 1'!$27:$27,scan(,'Sheet 1'!$2:$2,lambda(a,c,if(c="",a,c)))=A6),2,3,4))
=reduce(tocol(,1),'Sheet 1'!A2:index('Sheet 1'!2:2,match(,0/('Sheet 1'!4:4<>""))),lambda(a,c,vstack(a,if(c="",tocol(,1),hstack(c,offset(c,25,1,,3))))))
'Sheet 1'!A2:index('Sheet 1'!2:2,match(,0/('Sheet 1'!4:4<>"")))
dynamically translates to A2:L2
because that's where the table data ends as of nowtocol(,1)
is a null value within vstack()
] → Otherwise output the values present at a certain offset offset(c,25,1,,3)