Question description:
This user has given permission to use the problem statement for this
blog.
I have an excel query on a gsheet that only works if a date is entered in to an unrelated cell.
Solved by V. A. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
07/06/2018 - 08:49
Hello, I understand that your query only works when there's a value in an unrelated cell, right?
User
07/06/2018 - 08:49
Hi, there is a query on the do not edit tab. It pulls in info from every tab. It all works except for info from the Barclays tab, which pulls all the info in to one cell.
User
07/06/2018 - 08:49
https://docs.google.com/spreadsheets/d/1UcU4zFGkqvhhKM509CEQDYJwQdK37eArGVKlo8NyyKk/edit#gid=1830709471
Excelchat Expert
07/06/2018 - 08:49
Alright, I'll look into this. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
User
07/06/2018 - 08:49
If I put a date on the Barclays tab, in cell I5, the query works. I don't want a date in that cell yet
User
07/06/2018 - 08:50
All the other sheets work fine
User
07/06/2018 - 08:50
If you cannot fix, do I still need to pay?
Excelchat Expert
07/06/2018 - 08:50
Can you share the sheet to everyone so I can access it?
Excelchat Expert
07/06/2018 - 08:50
The first session is free.
User
07/06/2018 - 08:50
Ok sent
User
07/06/2018 - 08:50
Not sent, meant shared
Excelchat Expert
07/06/2018 - 08:51
Can I have the shared link? The link above needs permission.
User
07/06/2018 - 08:51
https://docs.google.com/spreadsheets/d/1UcU4zFGkqvhhKM509CEQDYJwQdK37eArGVKlo8NyyKk/edit#gid=1830709471
Excelchat Expert
07/06/2018 - 08:51
Still needs permission.
User
07/06/2018 - 08:53
https://docs.google.com/spreadsheets/d/1UcU4zFGkqvhhKM509CEQDYJwQdK37eArGVKlo8NyyKk/edit?usp=sharing
User
07/06/2018 - 08:53
Ok try now please
Excelchat Expert
07/06/2018 - 08:53
Thank you. Alright, though we normally support Excel, let me try and figure this one out. I'll need a few minutes.
User
07/06/2018 - 08:54
Ta. So you should just need the Do not edit tab, the query formula is in A:2
User
07/06/2018 - 08:54
Then the Barc tab
Excelchat Expert
07/06/2018 - 08:54
Is the Do Not Edit tab supposed to have formulas?
Excelchat Expert
07/06/2018 - 08:55
I'm looking at the previously referred to cell of I5 in both sheets and see nothing in there.
User
07/06/2018 - 08:55
Yes, do not edit has a query in A2
Excelchat Expert
07/06/2018 - 08:55
The one you shared doesn't.
User
07/06/2018 - 08:55
And it works, pulling in info from every other tab, except the barclays tab. On do not edit, row 3, it pulls in all the info but only in 1 row
Excelchat Expert
07/06/2018 - 08:55
I only have View capabilities in your sheet.
User
07/06/2018 - 08:56
Ok try now :)
Excelchat Expert
07/06/2018 - 08:56
Got it now. So which cell should have a date before it works?
User
07/06/2018 - 08:57
So on the barc tab, its already how I want it, with only 1 date. But if I add a date to Barclays I5, the Do Not Edit query works
User
07/06/2018 - 08:57
but I don't want a date in I5 yet as the job isn;t complete
Excelchat Expert
07/06/2018 - 08:58
By Barc tab, you meant Barclays, right? So if there's not date in i5 then it stops working.
User
07/06/2018 - 08:58
Correct
User
07/06/2018 - 08:59
Adding a date to I5 makes it work. But I don't want a date there yet. This isn't an issue on any other tab.
Excelchat Expert
07/06/2018 - 08:59
Can you tell me what exactly happens when you have a date and no date?
Excelchat Expert
07/06/2018 - 08:59
What changes?
Excelchat Expert
07/06/2018 - 08:59
I mean, define "work".
User
07/06/2018 - 09:00
On the do not edit tab, you can see each row has some info in it. Each row relates to a separate row on each tab. However in row 3, it pulls in every row of the barclays tab, all in to row 3
User
07/06/2018 - 09:00
So when I add a date to I5 Barclays, it splits out the barclays info on the do not edit tabe, in to individual rows
Excelchat Expert
07/06/2018 - 09:01
Okay. I can see that. One moment.
Excelchat Expert
07/06/2018 - 09:03
A few more minutes.
User
07/06/2018 - 09:03
Sure
Excelchat Expert
07/06/2018 - 09:07
It doesn't look like it is the sheet that is causing the problem but rather the data in Barclay's.
Excelchat Expert
07/06/2018 - 09:08
If you copy the data from Barclay's and paste it in another sheet, keeping the tab name, it will do the same.
User
07/06/2018 - 09:08
Yup. I can't see why the data is bad though?
Excelchat Expert
07/06/2018 - 09:10
To be precise, it's the data in row 13 that's causing it but I can't spot what's different with that row either.
User
07/06/2018 - 09:11
Ok
User
07/06/2018 - 09:13
It's not just that row, I have just tried deleting that data and putting a date in the row below, and that breaks it also
Excelchat Expert
07/06/2018 - 09:13
It seems that it has something to do with the Date Started and Date completed column.
Excelchat Expert
07/06/2018 - 09:13
If you delete either of those values, it will work.
User
07/06/2018 - 09:14
Yup, but I need that info :) . And it works on every other tab
Excelchat Expert
07/06/2018 - 09:15
This is mind boggling.
Excelchat Expert
07/06/2018 - 09:16
Still looking though.
Excelchat Expert
07/06/2018 - 09:16
And no, it doesn't work on the other sheet. The other sheet has the same problem.
User
07/06/2018 - 09:17
What sheet?
Excelchat Expert
07/06/2018 - 09:17
Any sheet.
Excelchat Expert
07/06/2018 - 09:17
https://docs.google.com/spreadsheets/d/1APH5_dwMhEXDXimoNG2SUkbmS4WnOUzLwVT7G0ZlC60/edit?usp=sharing
Excelchat Expert
07/06/2018 - 09:17
Here's a copy I made so I can play with your file.
User
07/06/2018 - 09:18
:(
Excelchat Expert
07/06/2018 - 09:18
Did you see the data in Risk?
Excelchat Expert
07/06/2018 - 09:18
I made it so it looks exactly like Barclays and the same thing happened.
User
07/06/2018 - 09:18
Hmmm
Excelchat Expert
07/06/2018 - 09:19
So this confirms that the problem can appear even in other sheets.
Excelchat Expert
07/06/2018 - 09:19
The question remains as to why.
User
07/06/2018 - 09:19
Yup
Excelchat Expert
07/06/2018 - 09:19
It's going to be really difficult trying to narrow it down.
Excelchat Expert
07/06/2018 - 09:20
Are the invoice number supposed to be the same?
User
07/06/2018 - 09:20
Yup, 1 invoice covering a few jobs
User
07/06/2018 - 09:21
Changing doesn't fix
Excelchat Expert
07/06/2018 - 09:21
It actually does but it doesn't make any sense. Look at the Rizk tab Invoice number.
Excelchat Expert
07/06/2018 - 09:21
I changed it to plain numbers and the Do Not Edit sheet works.
Excelchat Expert
07/06/2018 - 09:22
I honestly don't think this is a problem with your formula or your data but I think we caught a very rare bug for Sheets.
Excelchat Expert
07/06/2018 - 09:22
Because, like what you mentioned, those columns aren't related to the query at all.
Excelchat Expert
07/06/2018 - 09:23
So it doesn't make sense that they affect the outcome of the query.
User
07/06/2018 - 09:23
Haha
User
07/06/2018 - 09:23
But it's still not working fully?
Excelchat Expert
07/06/2018 - 09:24
Changing the Invoice to letters doesn't work.
Excelchat Expert
07/06/2018 - 09:24
Changing them to numbers will work though so it doesn't make any sense.
User
07/06/2018 - 09:26
I have changed the barclays in my link to numbers and it still doesn;'t work
Excelchat Expert
07/06/2018 - 09:26
Which is why this is mind boggling.
Excelchat Expert
07/06/2018 - 09:26
It's not making any sense.
Excelchat Expert
07/06/2018 - 09:26
We can't even narrow down what the problem is because it randomly works.
User
07/06/2018 - 09:28
Putting letters in still works in Rizk, think it's more the location of the cell
User
07/06/2018 - 09:28
Do Gsheets have a contact?
Excelchat Expert
07/06/2018 - 09:29
https://gsuite.google.com/intl/en_ph/support/
Excelchat Expert
07/06/2018 - 09:29
This one is for my country, so you may want to change the en_ph part to yours
Excelchat Expert
07/06/2018 - 09:29
Or it may automatically do that.
Excelchat Expert
07/06/2018 - 09:31
So it doesn't look like there's anything we can do about this bug so I guess the solution is to contact Google Sheets support team.
User
07/06/2018 - 09:31
Ok thank you for trying! I will contact google sheets
Excelchat Expert
07/06/2018 - 09:31
Even the simplest one:
Excelchat Expert
07/06/2018 - 09:31
=query(Query(Barclays!B5:P50,"where B<>''" ))
Excelchat Expert
07/06/2018 - 09:31
Does not work as intendedn.
Excelchat Expert
07/06/2018 - 09:32
Maybe you can start with that.
Excelchat Expert
07/06/2018 - 09:32
Tell them that the query isn't working as expected.
Excelchat Expert
07/06/2018 - 09:33
Removing either the start date or end date fixes it but I don't really see the connection.
User
07/06/2018 - 09:33
Yup, I will remove the start date in the interim
User
07/06/2018 - 09:33
And contact Gsheet. Silly bug :)
Excelchat Expert
07/06/2018 - 09:34
Removing everything except those dates doesn't do anything so I guess we narrowed it down to those dates.
Excelchat Expert
07/06/2018 - 09:35
Now, you introduced another randomness haha.
User
07/06/2018 - 09:35
?
Excelchat Expert
07/06/2018 - 09:35
When it is a date, it stops on that row, if it's letters, it takes everything.
Excelchat Expert
07/06/2018 - 09:35
If it's a date, you'll notice that it only groups all rows that are before that row with date.
User
07/06/2018 - 09:36
Delete and reinsert the columnb?
Excelchat Expert
07/06/2018 - 09:36
I think that's worth a try.
User
07/06/2018 - 09:36
Nope
Excelchat Expert
07/06/2018 - 09:37
Oh wait.
Excelchat Expert
07/06/2018 - 09:39
Give me a few minutes. I'm just trying out something.
User
07/06/2018 - 09:39
Sure
User
07/06/2018 - 09:39
Good luck :p
Excelchat Expert
07/06/2018 - 09:40
Nevermind, it didn't affect it at all. Haha.
User
07/06/2018 - 09:41
Haha ok, Darn!
Excelchat Expert
07/06/2018 - 09:42
I don't know why it's working right now with dates.
Excelchat Expert
07/06/2018 - 09:43
It's working, right?
User
07/06/2018 - 09:44
Yeah...
User
07/06/2018 - 09:44
But for no reason
Excelchat Expert
07/06/2018 - 09:44
And as much as I'd like to take credit, I really have no idea why lol.
User
07/06/2018 - 09:45
Broke it :)
User
07/06/2018 - 09:45
Copied in my barclays tab
Excelchat Expert
07/06/2018 - 09:45
I copied from the Barclays tab as well and it is working.
Excelchat Expert
07/06/2018 - 09:46
But when I copied the entire row from the barclay's tab, it stopped working.
Excelchat Expert
07/06/2018 - 09:47
Our session is about to expire, by the way and there's nothing I can do to extend it from my end.
User
07/06/2018 - 09:47
What do you mean copied from the row?
Excelchat Expert
07/06/2018 - 09:47
I guess, the only real solution to this is to contact Gsheets support.
User
07/06/2018 - 09:47
Ok
Excelchat Expert
07/06/2018 - 09:48
Just try to give them the details of what we tried so far.
Excelchat Expert
07/06/2018 - 09:48
I'd appreciate a 5-star rating and your feedback if you think I still deserve it.
Excelchat Expert
07/06/2018 - 09:48
I'll be giving you high ratings as well so the other experts will know that you are a good client and should be taken care of.
Excelchat Expert
07/06/2018 - 09:48
Thank you for contacting Got It Pro. Have an awesome day!
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
