Converting Google Sheets document with query to Excel

Hi,

I have a Google sheet document that is using Query in the formula. I would like to do the same thing in Excel but for the life of me can't figure out how to (there are many reasons I don't want it to stay in Google Sheet, but won't get into it). I tried exporting the document to Excel format, but because it uses query, it obviously doesn't work correctly.

I didn't create the original google document, which is not relevant. What is relevant is what I want it to do. The sheet label All has information for Lighting Cues and follow spot cues together. The other sheets label Spots, pull ONLY the information relevant to them including lighting cue number, who they are picking up what color etc. How can I recreate this in Excel without having to copy and paste the formula multiple times??? In the google document, the formula is listed only once in each sheet and populates all the information for the Spot sheets.

Attached is the formula from the original Google document (and a few screen shots). The formula was placed in the cell B3 of each sheet.

Re: Converting Google Sheets document with query to Excel

I am not familiar with Google sheets. I attempted to follow the sense of syntax in your post and “back-engineered” the attached.
This explanation is a bit “long-winded”. Please bear with me.
I used 4 formulas in the attached. Only 3 of them show.
The layout of sheets Spot 1, Spot 2 ----- Spot 4 are identical. I took advantage of that advantage by entering these formulas with sheets [Grouped]. If you are unfamiliar with Grouping sheets click on the Spot 1 tab and while holding the Shift key down click on the Spot 4 tab (or whatever the last sheet is that you want to include in the Group). You will know that they are grouped when you see [Group] in the title bar of your workbook. When you enter these formulas across Grouped sheets they will “drill” down through each of those sheets in their respective locations. That is how I entered the 4 formulas.
The first thing I did was to enter a helper row in the All sheet (F1:U1). No formula was used. I merely typed Spot 1 into F1, selected F1:I1 then grabbed the fill handle and filled across to U1. This was in order to get an exact match between the sheet names and define their relative boundaries in the INDEX range F4:U100.

With the Spot sheets grouped I entered this formula in cell B1 of Spot 1:

This returned each sheet name in its own sheet. These relate to the helper row in All. I then copied and Pasted Values back onto itself. Therefore you will not find that formula in the attached. I did this because the formula has done its job and is no longer needed and because (primarily because) the CELL function is volatile and all remaining formulas are dependent upon the contents of B1.

Again with sheets grouped I made a ‘helper’ column in I3:I40. The formula entered in I3 and filled down to I40 or as far as needed is:

Re: Converting Google Sheets document with query to Excel

That's AMAZING!!! Thank you so much! That does help. A couple questions as I can't seem to figure out how to correct the formula to reflect it ... what if I wanted the intensity column to be a mix of alpha numeric? I.e. 100 would be FL and 50 would be 1/2. Is that possible? These are know terms in the lighting world and I figured if I could get it to work why not. In the attached file notice that Spots 1- 4 are not counting any information from LX Q11 because the Intensity is written as FL as opposed to a numeric value.

Also, if I were to add a Notes column in the All sheet would I use the same formula

Re: Converting Google Sheets document with query to Excel

I haven't looked at the newly uploaded file yet. And I haven't examined the details of the new conditions / questions.

In the meantime:

Lastly, and this is purely curiosity because I love to know how things work, what is the information in the Helper column?

Super! The world could use more of that.

This is also a bit long-winded.

The information in the Helper column tests for whether "F>4 or G>4 or H>4 or I>4" through "F>100 or G>100 or H>100 or I>100" i.e any cell in each row / column intersection of the Spot 1 section of your data meets the condition ">1". If it does an INDEX number is assigned by the convenience of the ROWS function. Once that decision is made the summaries of each sheet "segregate" those INDEX numbers with the SMALL function, together again with the ROWS function, to the "top" of an array in memory in numerical order ascending. They in turn are fed to the corresponding section(s) in the All sheet and return the data from only those designated rows in that Spot # section.

The "F>4 or G>4 or H>4 or I>4" through "F>100 or G>100 or H>100 or I>100" part is for the Spot 1 section. "J>4 or K>4 or L>4 or M>4" through "J>100 or K>100 or L>100 or M>100" would be for the Spot 2 section, etc. etc. BTW each Spot # column section location is determined by the sheet name in each B1 matched against the helper row header F1:U1 in sheet All.

That's how the =IFERROR(INDEX(All!$F$4:$U$100,SMALL($I$3:$I$40,ROWS($1:1)),MATCH($B$1,All!$F$1:$U$1,0)+(COLUMNS($A:A)-1)),"")
rows argument and the columns argument of this INDEX function work together to return the qualified parts from All!$F$4:$U$100.

Re: Converting Google Sheets document with query to Excel

Also, if I were to add a Notes column in the All sheet would I use the same formula

I'm not certain I understand. If you mean an additional "Notes" column in each "Followspot #" section, then yes.

If you mean a single column then that could complicate things. I just don't know right now. I would need more information on how you would want to use a single column.

... what if I wanted the intensity column to be a mix of alpha numeric? I.e. 100 would be FL and 50 would be 1/2. Is that possible? These are know terms in the lighting world and I figured if I could get it to work why not.

Probably yes. I would need a complete list of the known terms to pull it off. BTW does this affect only the individual "Intensity" columns or will similar issues apply to the other columns as well. The more information you can supply the better.

Another BTW. How many rows do you anticipate using in the All sheet? I am going to modify (slightly) all of the query formulas so that they would reference a Dynamic Named Range (DNR) defined in the All sheet ---- something that grows or shrinks as data is entered / deleted. In the original formula I chose arbitrary last rows as 100 and 40 because they were enough for that WB and because they get the idea across. Looking at your new upload I am having second thoughts. So if you can tell me how many rows you can reasonably expect to use it will be helpful. I will be able to set an easily editable upper boundary for the DNR.

Last edited by FlameRetired; 07-27-2015 at 05:41 PM.

Re: Converting Google Sheets document with query to Excel

Seriously, thank you!!! Your explanation regarding the Helper cell was awesome, not to mention just the fact that you figured out how to reverse
engineer that whole mess is amazing! Thank you!

Originally Posted by FlameRetired

I'm not certain I understand. If you mean an additional "Notes" column in each "Followspot #" section, then yes.

If you mean a single column then that could complicate things. I just don't know right now. I would need more information on how you would want to use a single column.

Actually what I had in mind was adding a "Notes" column to each of the Spots section in the "All" sheet. This way I could enter the notes directly in the "All" sheet when I am entering the other information and have this information populate to the "Notes" column in the relevant "Spot" sheet. Does
that make sense? I was just trying to save time ... I'm not entering any information directly in the "Spot" sheets, so why should I go there to make the notes? That was my thinking.

Originally Posted by FlameRetired

BTW does this affect only the individual "Intensity" columns or will similar issues apply to the other columns as well. The more information you can supply the better.

It would only affect the "Intensity" column. The "Color" column doesn't exhibit any issues with either numeric or alpha numeric entries. So the only one is the "Intensity" column.

Originally Posted by FlameRetired

Another BTW. How many rows do you anticipate using in the All sheet? I am going to modify (slightly) all of the query formulas so that they would reference a Dynamic Named Range (DNR) defined in the All sheet ---- something that grows or shrinks as data is entered / deleted. In the original formula I chose arbitrary last rows as 100 and 40 because they were enough for that WB and because they get the idea across. Looking at your new upload I am having second thoughts. So if you can tell me how many rows you can reasonably expect to use it will be helpful. I will be able to set an easily editable upper boundary for the DNR.

Right now the show that started all this had 270 cues ... so 270 rows of entered data. That's on the larger side, so 300 is a safe bet as the number of Spot cues do not always mirror the number of actual lighting cue. Does that make sense?

Re: Converting Google Sheets document with query to Excel

Actually what I had in mind was adding a "Notes" column to each of the Spots section in the "All" sheet.

Good. Add them. They should be easily included in the existing formula provided they are the last column in each Followspot # section.

It would only affect the "Intensity" column. The "Color" column doesn't exhibit any issues with either numeric or alpha numeric entries. So the only one is the "Intensity" column.

That's doable. I just need a comprehensive list of those industry standard "Intensity" terms to include in the Helper column conditions. Do you have that?

Right now the show that started all this had 270 cues ... so 270 rows of entered data. That's on the larger side, so 300 is a safe bet as the number of Spot cues do not always mirror the number of actual lighting cue.

I went ahead and started defining some DNRs. I used 500 as an arbitrary upper limit. That is enough and the larger size won't hurt anything. I'll leave that alone.

In the meantime I'll be looking for that "Intensity" list.

Last edited by FlameRetired; 07-28-2015 at 06:28 PM.

Re: Converting Google Sheets document with query to Excel

Brilliant!!! Thank you! The Notes column performed as expected (yes they are the last column in each Followspot section.

As far as the terms goes, FL is really the big one. I would like to type in FL in the intensity column when meaning 100% intensity and have the formula run correctly. The rest fractions are more commonly used i.e. 1/2 = 50% intensity, 3/4 = 75% intensity and 1/4 = 25% intensity ... so as long as typing in a fraction wouldn't mess up the formula or return a date value then we're fine. Anything outside of that would be a special case which is not something that I would worry about in sheet like this. Actually, come to think of it, if I did a in-cell drop down would that take care of everything?

Last question, with the newly implemented "notes" section, would there be anyway for me to get the formula to not return a value of "0" if the section is empty? Can it just leave it blank? If not, it's not a big deal I could always go back and clean it up later by deleting the formula from any one that did not have information.

Again, thank you so much for all your help!

Originally Posted by FlameRetired

Good. Add them. They should be easily included in the existing formula provided they are the last column in each Followspot # section.

I just need a comprehensive list of those industry standard "Intensity" terms to include in the Helper column conditions. Do you have that?

Re: Converting Google Sheets document with query to Excel

Actually, come to think of it, if I did a in-cell drop down would that take care of everything?

Yes as long as the list above is complete and match exactly the items in drop-down I can include them in the conditions of the helper.

If you have the drop-downs defined and in place please upload that latest WB.

Last question, with the newly implemented "notes" section, would there be anyway for me to get the formula to not return a value of "0" if the section is empty?

Yes. The simplest way that I am aware of would also return your number values as text representations of those numbers. This means that if you try to

compare or match "55" (for example in your summary sheets) to 55 it will return FALSE or an error. As long as you are aware of this and coerce the text

back to their underlying numeric values if necessary you should be OK. The method I have in mind would also return blank cells for the other blank cells

instead of returning the 0s the formula currently does. Do you want me to suppress the 0s?

Re: Converting Google Sheets document with query to Excel

Thank you. Yes the drop downs were already in place.

Regarding the os, I don't think that will necessary ... I can always make changes as necessary.

Thanks, and here is the new work book with ... the "notes" section of spot 4 isn't receiving any information form the "All" sheet, so I am guessing it is an issue with the helper formula for that sheet. Correct?

Again, thank you so very much!!

Originally Posted by FlameRetired

Yes as long as the list above is complete and match exactly the items in drop-down I can include them in the conditions of the helper.

If you have the drop-downs defined and in place please upload that latest WB.

Yes. The simplest way that I am aware of would also return your number values as text representations of those numbers. This means that if you try to

compare or match "55" (for example in your summary sheets) to 55 it will return FALSE or an error. As long as you are aware of this and coerce the text

back to their underlying numeric values if necessary you should be OK. The method I have in mind would also return blank cells for the other blank cells

instead of returning the 0s the formula currently does. Do you want me to suppress the 0s?

Re: Converting Google Sheets document with query to Excel

Here’s the latest.
Another long winded “epistle”.
I gave up trying create the Dynamic Named Ranges I mentioned earlier. There were going to be too
many of them. They would be interactive. I doubt that I could give very helpful instructions for
maintaining them. DNRs are a topic in themselves. Editing the ranges of your 3 formulas will be tedious.
However editing the upper left most cell in each range will be sufficient. Just fill down and across as far
as necessary in each formula range. Just be sure to define all the ranges references equally. What used
to be row 100 in all cases (helper column included) is now row 300. I changed that for you.

I also changed the right column INDEX range from X to Y. That is why Spot 4 was not picking up the
Notes column.

I noticed that the fractions in the drop downs in the Size columns returned dates just like you described.
In the data validation I took the liberty of changing those. I hope you don’t mind. All I did was put a
leading apostrophe in front of the ‘1/2 and the ‘3/4. They behave now.

The biggest change in formula is in the helper column. Though it does not have to be array-entered
(Ctrl + Shift + Enter) it is still an array formula. Should you make changes to range references and of
course to how far down you fill it is going to take a while for Excel to complete the calculations for those
changes. Array formulas are resource hungry. You will likely be told that Excel in not responding and the
screen my slightly fade. Wait. It will all come back when calcs are complete. It takes my machine about
15-20 seconds --- earth time. --- longer in my mind. Once it’s done though it’s done.
This is the formula.

You’ll notice the {} curly
Braces around the items from your drop-down. These are hardcoded and shouldn’t need to be edited.
They are also the reason why it is now necessarily an array formula. You’ll also notice the “;” separaters.
Those are important. BTW it also includes the original “>1” qualifier just in case.

Except for the edits mentioned above the other formulas remain the same.

If you have second thoughts about the 0s in the Notes columns let me know. The fix is simple and easy
to undo if you don’t like it. It only has to be done in one formula filled down and across.

Oh yes. The headers in the Spot # sheets somehow got miss-aligned. That meant the formulas were
miss-aligned and not working. It’s the details that get you. That’s fixed.

Re: Converting Google Sheets document with query to Excel

Sorry for the delay in responding, but I was trying to wrap my head around all this great information!! Thank you so much for all your assistance! The work book functions as expected, no problems so far in my testing.

Now that you mention it though, If you'd like to give it a shot to remove the "0" in the note column, I wouldn't mind seeing what it would look like. However, that's not a must right now ... this is great for me to get working with.

Again, thank you so very much for all your help and explanations!

Originally Posted by FlameRetired

Here’s the latest.
Another long winded “epistle”.
I gave up trying create the Dynamic Named Ranges I mentioned earlier. There were going to be too
many of them. They would be interactive. I doubt that I could give very helpful instructions for
maintaining them. DNRs are a topic in themselves. Editing the ranges of your 3 formulas will be tedious.
However editing the upper left most cell in each range will be sufficient. Just fill down and across as far
as necessary in each formula range. Just be sure to define all the ranges references equally. What used
to be row 100 in all cases (helper column included) is now row 300. I changed that for you.

I also changed the right column INDEX range from X to Y. That is why Spot 4 was not picking up the
Notes column.

I noticed that the fractions in the drop downs in the Size columns returned dates just like you described.
In the data validation I took the liberty of changing those. I hope you don’t mind. All I did was put a
leading apostrophe in front of the ‘1/2 and the ‘3/4. They behave now.

The biggest change in formula is in the helper column. Though it does not have to be array-entered
(Ctrl + Shift + Enter) it is still an array formula. Should you make changes to range references and of
course to how far down you fill it is going to take a while for Excel to complete the calculations for those
changes. Array formulas are resource hungry. You will likely be told that Excel in not responding and the
screen my slightly fade. Wait. It will all come back when calcs are complete. It takes my machine about
15-20 seconds --- earth time. --- longer in my mind. Once it’s done though it’s done.
This is the formula.

You’ll notice the {} curly
Braces around the items from your drop-down. These are hardcoded and shouldn’t need to be edited.
They are also the reason why it is now necessarily an array formula. You’ll also notice the “;” separaters.
Those are important. BTW it also includes the original “>1” qualifier just in case.

Except for the edits mentioned above the other formulas remain the same.

If you have second thoughts about the 0s in the Notes columns let me know. The fix is simple and easy
to undo if you don’t like it. It only has to be done in one formula filled down and across.

Oh yes. The headers in the Spot # sheets somehow got miss-aligned. That meant the formulas were
miss-aligned and not working. It’s the details that get you. That’s fixed.

That also converts any "numbers" to their text representations.

One way to explicitly reconvert them back to numbers is pre-pend all references to their with a double-unary "--". So you would write =--E5 for example to convert "100" back to 100.

Any mathematical operation you perform on those cells will also reconvert them =4.75*E5 for example would return the expected result as if E5 were already a number.

Be aware that any lookup or match functions or any comparison operations referencing those cells will need for the comparison / match criteria to be of the same data type. The explicit coercion will probably be necessary.

One cautionary. Those 3/4 and 1/2 "fractions" will reconvert back to date values again if coerced with "--". If those are understood to have values 0.75 and 0.5 you'll need to do something like this =LEFT(E5,1)/RIGHT(E5,1) to coerce those decimal values.

Re: Converting Google Sheets document with query to Excel

Thanks!!!

One change I made was, instead of applying the formula to all the cells in the "Spot" sheets, I only applied the changed formula to the "Notes" column, this takes away all the worry of fractions re-converting etc. This seemed to work with out any issues ... do you see any issues with this set up?

Thanks.

Originally Posted by FlameRetired

The only change to the formula necessary to suppress the 0s is this.

That also converts any "numbers" to their text representations.

One way to explicitly reconvert them back to numbers is pre-pend all references to their with a double-unary "--". So you would write =--E5 for example to convert "100" back to 100.

Any mathematical operation you perform on those cells will also reconvert them =4.75*E5 for example would return the expected result as if E5 were already a number.

Be aware that any lookup or match functions or any comparison operations referencing those cells will need for the comparison / match criteria to be of the same data type. The explicit coercion will probably be necessary.

One cautionary. Those 3/4 and 1/2 "fractions" will reconvert back to date values again if coerced with "--". If those are understood to have values 0.75 and 0.5 you'll need to do something like this =LEFT(E5,1)/RIGHT(E5,1) to coerce those decimal values.

## Bookmarks