Hey everyone, I’m stuck on a Google Sheets problem. I need to create a formula that checks if an order is canceled and then shows it with the related material on another sheet. I’ve got two columns (A and B) with order info, and I want to display the canceled ones in columns C and D on a different sheet. I’m not sure how to approach this. Has anyone done something similar or have any suggestions? I’ve been scratching my head over this for a while now. Any help would be awesome! Thanks in advance!
I’ve been in your shoes, danwilson85. One approach that’s worked well for me is using a combination of QUERY and IMPORTRANGE functions. This setup is particularly handy if your data is spread across different sheets or even different spreadsheets.
Here’s a formula you might find useful:
=QUERY(IMPORTRANGE(“spreadsheet_url”, “SheetName!A:C”), “SELECT Col1, Col2 WHERE Col3 = ‘Canceled’”)
Replace ‘spreadsheet_url’ with your actual spreadsheet URL and adjust the sheet name and column references as needed. This method allows for flexibility if your data structure changes or if you need to pull from multiple sources.
Just a heads up, you’ll need to authorize the IMPORTRANGE function the first time you use it. After that, it should update automatically whenever your source data changes. Give it a shot and let me know if you run into any snags!
I’ve encountered a similar situation in my work. A combination of QUERY and ARRAYFORMULA functions might be more efficient, especially if you’re dealing with a large dataset. Here’s a formula you could try:
=ARRAYFORMULA(QUERY(Sheet1!A:C, “SELECT A, B WHERE C = ‘Canceled’”, 0))
This assumes your ‘Canceled’ status is in column C. Adjust the column references as needed. The advantage here is that it’ll automatically update as new data is added, without needing to drag the formula down. Just make sure to place this in the top-left cell of your destination range on the other sheet.
Remember to replace ‘Sheet1’ with your actual sheet name. Let me know if you need any clarification on how this works.
hey danwilson85, i’ve tackled something like this before. you could use FILTER function to grab the canceled orders. it’d look something like:
=FILTER(Sheet1!A:B, Sheet1!C:C=“Canceled”)
just replace Sheet1 with your actual sheet name and adjust the column references. hope this helps ya out!