0

The attached document is a time-sheet document. There are 2 tabs, TimesheetData and InvoiceData.

I want to pull through data from TimesheetData tab to the InvoiceData tab using a query.

The query I have set up in the InvoiceData tab is:

=QUERY(TimsheetData!A:BD, "SELECT* WHERE A = 'Yes' and B is Null ORDER BY F DESC")

However, I do not want to pull through all of the columns and column headers, I only want to pull through the columns and column headers that have data.

In my QUERY results in InvoiceData tab, I want to remove the columns and column headers that have no data and be left with columns and headers that have data.

In the link attached, in the InvoiceData tab, I have highlighted in red the columns and headers that need to be deleted.

https://drive.google.com/open?id=1SZwoN19ObVQyT5z7M6UJvnjTZl5m94KuJ1gUV0m1Ml8

  • What I would personally do in this instance is the following: create a helper column which parses through your columns to find blanks you can then filter the query on that. Is that feesible? – Datanovice Sep 25 '18 at 13:06
  • had another look at this, I don't think there is a way to meet your desired result with a query as every row has some data in it.. to remove column headers you can add 0 to your query at the end. – Datanovice Sep 25 '18 at 19:21
  • 1
    @Datanovice if i cannot do this with a query, then is it done through a script? To your point about the adding a 0, would that look like this? =QUERY(TimsheetData!A:BD, "SELECT* WHERE A = 'Yes' and B is Null ORDER BY F DESC" 0) – Alex Sep 25 '18 at 21:02
  • Close! Add a comma after DESC",0) im not 100% sure, as your desired output isn't clear to me. That said, you could chain multiple OR statements – Datanovice Sep 25 '18 at 21:29
0

The trouble is that you ARE wanting to keep some columns that have no data, but to omit others. So you'll have to directly list in your QUERY all columns that you DO want to keep, like this:

=QUERY(TimsheetData!A:BD, "SELECT A,B,C,D,E,F,G,H,I,J,K,L,M,N,S,T,U,V,W,X,Y,Z,AA,AB,AC,AI,AM,AN,AO,AP,BB,BC,BD WHERE A = 'Yes' and B is Null ORDER BY F DESC")

This is going to create a temporary mess with column widths, however, since you apparently created one sheet from another, including formatting. So once you use this QUERY, you'll want to select columns A:AG in the InvoiceData tab, right-click, choose "Resize selected columns," and then check "Fit to Data" and click "Done."

This isn't ideal, because if you later decide to add data columns in between existing columns in the TimesheetData tab, you'll have to go back through the QUERY and make sure the letters match all the new columns you want to bring over (i.e., the "Select" clause in a QUERY doesn't auto-update; it's hard-coded). But if you don't plan to add or remove columns often, it's not a big deal.

One additional note: you've misspelled "Timesheet" (as "Timsheet") in the "TimsheetData" tab. It'll work if you also misspell it in the QUERY, but you might want to fix that in both places. In my formula, I kept your misspelling.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.