Excel online query function

%3CLINGO-SUB%20id%3D%22lingo-sub-1561134%22%20slang%3D%22en-US%22%3EExcel%20online%20query%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561134%22%20slang%3D%22en-US%22%3E%3CP%3EHello%3C%2FP%3E%3CP%3EI'm%20using%202%20excel%20online%20separate%20files.%20One%20is%20admin%20sheet%20and%20then%20on%20other%20excel%20file%20is%20public%20sheet.%20I%20want%20to%20select%20few%20columns%20from%20admin%20sheet%20and%20display%20them%20on%20that%20public%20excel%20file.%20I'm%20using%20importrange%20so%20I%20get%20in%20every%20data%20in%20and%20then%20I%20use%20query%20function%20to%20select%20the%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20problem%20when%20query%20function%20doesn't%20display%20proper%20values%20in%20certain%20columns%20.%20For%20example%20I%20have%20true%20%2F%20false%20value%20and%20under%20that%20I%20have%20this%20%22-%22%20value%20which%20is%20not%20displayed%20after%20query%20but%20in%20importrange%20it%20is.%3CBR%20%2F%3ESecond%20problem%20I%20am%20having%20is%20when%20I%20want%20to%20change%20those%20true%20%2F%20false%20values%20by%20inserting%20checkboxes%20so%20it%20is%20displayed%20more%20nicely.%20Sometimes%20I%20get%20query%20%23ref%20error%20because%20excel%20will%20apparently%20think%20checkbox%20is%20separate%20value%20but%20it%20works%20just%20fine%20in%20other%20columns.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1561134%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20for%20web%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1561610%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20online%20query%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1561610%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747175%22%20target%3D%22_blank%22%3E%40jurepisljar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20exactly%20do%20you%20mean%20under%20%22query%20function%22%3F%20Better%20to%20illustrate%20with%20sample%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1574697%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20online%20query%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1574697%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bsorry%20for%20late%20late%20reply.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20I%20am%20getting%20the%20information%20from%20another%20sheet%20and%20it%20doesn't%20allow%20me%20to%20put%20in%20the%20tick%20box%20over%20specific%20collum%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1574703%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20online%20query%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1574703%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F747175%22%20target%3D%22_blank%22%3E%40jurepisljar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat's%20Google%20Sheets%2C%20not%20Excel%2C%20correct%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1576035%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20online%20query%20function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1576035%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECorrect.%20I%20see%20my%20mistake%20there.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello

I'm using 2 excel online separate files. One is admin sheet and then on other excel file is public sheet. I want to select few columns from admin sheet and display them on that public excel file. I'm using importrange so I get in every data in and then I use query function to select the columns.

 

I have a problem when query function doesn't display proper values in certain columns . For example I have true / false value and under that I have this "-" value which is not displayed after query but in importrange it is.
Second problem I am having is when I want to change those true / false values by inserting checkboxes so it is displayed more nicely. Sometimes I get query #ref error because excel will apparently think checkbox is separate value but it works just fine in other columns.

4 Replies

@jurepisljar 

What exactly do you mean under "query function"? Better to illustrate with sample file.

@Sergei Baklan sorry for late late reply. 

 

As you can see I am getting the information from another sheet and it doesn't allow me to put in the tick box over specific collum

@jurepisljar 

That's Google Sheets, not Excel, correct?

@Sergei Baklan 

 

Correct. I see my mistake there.