samedi 24 juillet 2021

Query not returning all values, also adding extra if conditions

I'm having an issue with the spreadsheet linked below. I've chucked some dummy data in 'testing summary' sheet, and on the project and quarter sheet I've got a query set up so I can pull out the results per project for each quarter. It keeps weirdly missing the last column I want it to pull out (P and AD are the only ones with data so far) and I can't work out why. I'm using:

=iferror(if(I4 = "Quarter 1", query('Testing summary'!A6:BC,"select B,E,F,G,H,I,J,K,L,M,N,P where E = '"&$J$4&"'"),if(I4="Quarter 2",query('Testing summary'!A6:BC,"select B,R,S,T,U,V,W,X,Y,Z,AB,AC,AD where R = '"&$J$4&"'"),if(I4="Quarter 3",query('Testing summary'!A6:BC,"select B,AE,AF,AG,AH,AI,AJ,AK,AL,AM,AN,AO,AP where AE = '"&$J$4&"'"),IF(I4="Quarter 4",query('Testing summary'!A6:BC,"select B,AR,AS,AT,AU,AV,AW,AX,AY,AZ,BA,BB,BC WHERE AR = '"&$J$4&"'"))))),"No results found")

I think the quarter conditions have to be at the start because of the way the sheet's set up, they tell the query where to actually import from.

I'd also like to be able to pull out data by quarter, project and region, and can make that work with an AND at the end of each query (in the 3rd sheet, just added for Q1 so far), but if so I really need an 'all' version to work for regions, any idea? I am thinking I can add an extra set of if conditions somewhere to say where K4 = all then ignore the other options and pull everything that matches the other 2 conditions, but I have no idea where to start, to be honest.

I feel like I probably should have done something with filter rather than query, but I've never used filter!

https://docs.google.com/spreadsheets/d/1YtMLH4YDdqeHEnw2TnKZhZwVZ8DwYJ_CNd8XRe-H7wg/edit#gid=15127497

Thanks for any help :)

Aucun commentaire:

Enregistrer un commentaire