Search code examples
google-sheetsfilter

Add a value to filter result in Google Sheets

I have the following formula that works in Google Sheets

    =Sort({IFERROR((FILTER({'My Log'!B13:B, 'My Log'!F13:F, 'My Log'!E13:E, 'My Log'!H13:H, 'My Log'!I13:I,'My Log'!J13:J, 'My Log'!K13:K,'My Log'!L13:L, 'My Log'!P13:P, 'My Log'!R13:R}, 
'My Log'!O13:O = "Y", 
'My Log'!N13:N =B1
)), {"","","","","","","","","","",""})
},1, TRUE)

This fills the cells as I would like However I now need to add a value to each row that is outputted Googling I should be able to simply add a cell reference that contains the additional value and it would be added e.g. as below 'My Newvalue'!C1, however when I do that the output is lost.

So how can I add a column to the output with the Text held at 'My Newvalue'!C1 ?

Example Sheet here

=Sort({IFERROR((FILTER({'My Log'!B13:B, 'My Log'!F13:F, 'My Log'!E13:E, 'My Newvalue'!C1, 'My Log'!H13:H, 'My Log'!I13:I,'My Log'!J13:J, 'My Log'!K13:K,'My Log'!L13:L, 'My Log'!P13:P, 'My Log'!R13:R}, 
'My Log'!O13:O = "Y", 
'My Log'!N13:N =B1
)), {"","","","","","","","","","",""})
},1, TRUE)
Solution

  • Replace this in your second test formula

    'My Newvalue'!C1

    with

    let(Σ,'My Newvalue'!C1,wrapcols(Σ,rows('My Log'!A13:A),Σ))