Super Job on the worksheets, the technical stuff and the instruction. The only issue that I find could be different is that one or two sample worksheets being made available would really make these videos near-perfect. And this would be a great marketing move on your part that almost surely would generate greater business interest.
Hi, When you use "named ranges" is it needed to put $ to make the reference absolute ? Excel does it automatically when you create named ranges. For instance, when you use the named range for Vlookup without $-sign, does it work correctly when copying formula down?
*Thank you very much for such useful lessons! Best on RUclips on this subject!* Have a question about this topic: =IMPORTRANGE(someTable; someTab&"!E"&T3) - where T changes T1,T2,T3,... (numbers or IDs) instead of this I want to write =IMPORTRANGE(someTable; "someRange!"&T3) - (what is semantically wrong) where someRange is E:E but how to make it workable?
It's me again! I'm binged watching every lessons and they are all useful! Thank you! I have one question, is ={ } just the same as =ARRAYFORMULA? From the other episode I've watched, =ARRAYFORMULA also copies or import data from other tabs or sheets and in this video, you used ={ } which seems to have the same function, is it?
{} creates an actual single array. ARRAYFORMULA tells the spreadsheets that the formula should be interpreted as a a formula that has embedded arrays. This will make sense over time.
Great Videos! Helping out a lot. How would I use named fields if I want to make it a dependent field from a numbered indirect? The numbers from the indirect are weights of a product, and the field I'm trying to add is the quantity.. both drop downs. Can't use #'s in the named range so now I'm stuck lol . Hope you can help. Keep up the great vids!
Great video. I've been using named ranges to great effect. However, I have a spreadsheet where new data gets entered every morning. On my master tab I'd like to display whatever the latest input was. I, so far, have been unable to figure this out. Help!
Will naming a range still works if you remove the end refernece? So if the data for that range updates it woul be included under the Name range you setup? Or that's not possible?
Thats cool. Wondering if it's possible when working with 100 tabs where their names are numbers increased by 1 to not have to type all of the different ranges on the query by having a formula that increases the name tabs by 1 collecting their data.
Thanak a lot for that Just want to ask I’m using range name in my following farmula index(importrange)match(importrange). How can I make ranges dynamic for this task plz
I have a tab template for my Sales with a Named Range and when I copy this, automatically a new Named Range is created. This is perfect. Every new Range starts with Sales and then a year is added. Is there a way to query from all named ranges at once without naming them separately? So something like =Query({Sales*}, "select *", 0) instead of =Query({Sales2015; Sales2016; Sales2017;etc}, "select *", 0).
I have some sheets with lots of cells using importrange function. My problem is that each sheet have different information and if my first sheet which has names changes, the names on the other sheets will change and I will lose the information corresponding to each name on the following sheets. For example: my first sheet has a column with the names and some personal information for BOB, ADAM, DAVID, CLARK. Now my second sheet brought those names in in that same order and the second sheet will then have grades for each one of them: BOB 10, ADAM 9, DAVID, 5 and CLARK 8. Now, lets say, I decide to put the first sheet in alphabetical order: ADAM, BOB, CLARK, DAVID. And that's where my problem is, because now in the second sheet ADAM has 10 points, BOB has 9, CLARK has 5 and DAVID has 8. How can I make the following cells change automatically with the first cell when using importrange?
So I've got 2 named ranges now, one is "Sheet1!A1:A32" named as AllCommon, the other is "Sheet1!B1:I8" named as OtherCommon. When I do "={AllCommon;OtherCommon}" I get a Formula parse error. when i do "={AllCommon:OtherCommon}" it shows them correctly, but I also get cells B9:I32 included too. Do you know how to get rid of them? As I don't really want them in the range. I'll create a new document and add an example (Link Below). I've got most of it working, I'm trying to pick a random name from a list, but things mess up once I'm trying to pull 1 from multiple lists. (Sheet 2 is where the broken formulas are, Sheet 1 is just the list) docs.google.com/spreadsheets/d/1QNWVofSwuUe8S-raA9lHreWG2mB9TpPf-dkP-ox59qk/edit?usp=sharing
@@ExcelGoogleSheets Ooo okay :o Thank you :) I've found a couple of work arounds now too, I made each of B1:8, C1:8, ect to I1:8 as separate named ranges, and that seemed to work, a little messy but functional. But I'll defiantly give that one ago too :) Thank you. I like learning the different ways to do stuff. You never know when it'll come in handy hehe.
I misunderstood your question, I thought you were asking if you can reference a named range in importrange. Reference in importrange function is text, therefore it will act as an absolute reference all the time. You could do some trickery and concatenate text with row() function to make it work like a relative reference if needed, but otherwise no.
Thanks in advance for your help. I have 2 sheets: 1.-Master: Here I import a csv file and made calculation. 2.-Report: Here only use the importrange function. The imported data for the importrange function give me almost columns correct but some columns with: #value! or #N/A I checked my formula and I thinks is correct, I search in the Help Forum and I tried with some changes in format, formula, permission. But I can´t make it work. =iferror( importrange("1pp9f8uPyEOyitZY7UuB4sqQu-7ScukCttVqmxun0hz8/edit#gid=103974437", upper("Filtro_dia!A1:i300")), importrange("1pp9f8uPyEOyitZY7UuB4sqQu-7ScukCttVqmxun0hz8/edit#gid=103974437", lower("Filtro_dia!A1:i300"))) ¿can you help me?
What are you trying to do? Your formula just for importrange should be =importrange("1pp9f8uPyEOyitZY7UuB4sqQu-7ScukCttVqmxun0hz8", "Filtro_dia!A1:i300")
Is there a way to add up numbers in a formula when in between other words such as if they were part of email addresses such as user1@gmail.com, user2@gmail.com, user3@gmail.com ect
Hi, Thanks for the tutorial. I am trying to plot data in a graph where the range of data is dependent on selection. I've managed to do the application in MS Excel (with assistance from the following video: ruclips.net/video/sHfWRb2yUrM/видео.html) I would like to do a similar application in sheets? Please give me some guidance. Your assistance will be useful. Thanks. Regards, Ed
Best RUclips channel for Google sheets tutorials. Thumbs up
Thank you very much for your videos. You are the best source of google sheets training and tutorials I've found on the web.
Thank you so much, for the tutorial warm regards form Mexico!
ur gay shut up
That was amazing tutorial. I use lookup a lot, seeing it done this way is inspirational!!
Super Job on the worksheets, the technical stuff and the instruction. The only issue that I find could be different is that one or two sample worksheets being made available would really make these videos near-perfect. And this would be a great marketing move on your part that almost surely would generate greater business interest.
Learnt alot from you, thank so much! I created my own taxi calculation system from learning the stuff from this.channel.
Best videos on Google sheets. Thanks
:)
Hi sir...I love..this Chanel....I learning lot of things...tqq so much....👌👌
Good tip to combine named ranges. Thanks
Where can I find the sheet you used in this video? Performing the actions helps me to follow along.
Thank you for the great video, it is so good that you have so many useful videos for us to refer back to!!
:)
Really nice video. Helped a lot!
Hi,
When you use "named ranges" is it needed to put $ to make the reference absolute ? Excel does it automatically when you create named ranges. For instance, when you use the named range for Vlookup without $-sign, does it work correctly when copying formula down?
*Thank you very much for such useful lessons! Best on RUclips on this subject!*
Have a question about this topic:
=IMPORTRANGE(someTable; someTab&"!E"&T3) - where T changes T1,T2,T3,... (numbers or IDs)
instead of this I want to write
=IMPORTRANGE(someTable; "someRange!"&T3) - (what is semantically wrong) where someRange is E:E
but how to make it workable?
No way to answer without looking at your setup. Share a sample sheet.
Explaining could use some work
but good work, very informative...
Amazing! Thank you so much. 🙏
Great Initiative, thanks a lot.
You are awesome! Anyway, It would be much appreciated if you could drop the link for downloading workbooks to practice. Thanks.
Thank you for another great video!
SUMIFS saved me, thanks :)
very very effective. thanks
It's me again! I'm binged watching every lessons and they are all useful! Thank you! I have one question, is ={ } just the same as =ARRAYFORMULA? From the other episode I've watched, =ARRAYFORMULA also copies or import data from other tabs or sheets and in this video, you used ={ } which seems to have the same function, is it?
{} creates an actual single array. ARRAYFORMULA tells the spreadsheets that the formula should be interpreted as a a formula that has embedded arrays. This will make sense over time.
@@ExcelGoogleSheets ohh I see, thank you! ☺
Hello! Thank you for teaching this. May I ask if this can be used in Query instead of "selecting" each column?
That was amazing video, thank you.
thanks , it is useful
ty so much man
I'm learning a lot from these, well done and thank you.
Great Videos! Helping out a lot. How would I use named fields if I want to make it a dependent field from a numbered indirect? The numbers from the indirect are weights of a product, and the field I'm trying to add is the quantity.. both drop downs. Can't use #'s in the named range so now I'm stuck lol . Hope you can help. Keep up the great vids!
Great video. I've been using named ranges to great effect. However, I have a spreadsheet where new data gets entered every morning. On my master tab I'd like to display whatever the latest input was. I, so far, have been unable to figure this out. Help!
I think you should be able to apply named range for the whole column. So select columns and then try to define named range.
Will naming a range still works if you remove the end refernece? So if the data for that range updates it woul be included under the Name range you setup? Or that's not possible?
Doesn't work, at least for now.
Thats cool. Wondering if it's possible when working with 100 tabs where their names are numbers increased by 1 to not have to type all of the different ranges on the query by having a formula that increases the name tabs by 1 collecting their data.
It would be great to have a sheet file like in previous videos
Thanks man! Why did you use curly braces
@ 7:40? Was that some other programming language you can use in sheets? I only knew of SQL and VBA till now.
Array syntax. Watch my introduction to arrays video for more information.
Thanak a lot for that
Just want to ask I’m using range name in my following farmula index(importrange)match(importrange). How can I make ranges dynamic for this task plz
I have a tab template for my Sales with a Named Range and when I copy this, automatically a new Named Range is created. This is perfect. Every new Range starts with Sales and then a year is added. Is there a way to query from all named ranges at once without naming them separately? So something like =Query({Sales*}, "select *", 0) instead of =Query({Sales2015; Sales2016; Sales2017;etc}, "select *", 0).
Thanks!
I have a table where new entries come as new columns. How do I have it become a dynamic named range?
I have some sheets with lots of cells using importrange function. My problem is that each sheet have different information and if my first sheet which has names changes, the names on the other sheets will change and I will lose the information corresponding to each name on the following sheets. For example: my first sheet has a column with the names and some personal information for BOB, ADAM, DAVID, CLARK. Now my second sheet brought those names in in that same order and the second sheet will then have grades for each one of them: BOB 10, ADAM 9, DAVID, 5 and CLARK 8. Now, lets say, I decide to put the first sheet in alphabetical order: ADAM, BOB, CLARK, DAVID. And that's where my problem is, because now in the second sheet ADAM has 10 points, BOB has 9, CLARK has 5 and DAVID has 8. How can I make the following cells change automatically with the first cell when using importrange?
Is there a video that explains how to link an entire sheet to a certain cell? Almost like an web link ? Or can you only move data
Can I use the named range to create pivot table? I'm having troubles to use my named range to create pivot tables, but that works well with formulas.
No.
@@ExcelGoogleSheets Thanks anyway, your channel is the best one about the topic.
Thank you
Is there a way to add multiple defined ranges when using the query?
how to import a range along with formula in google sheets
Hi, I wonder if I could get the sheet file. Thank you
So I've got 2 named ranges now, one is "Sheet1!A1:A32" named as AllCommon, the other is "Sheet1!B1:I8" named as OtherCommon.
When I do "={AllCommon;OtherCommon}" I get a Formula parse error. when i do "={AllCommon:OtherCommon}" it shows them correctly, but I also get cells B9:I32 included too. Do you know how to get rid of them? As I don't really want them in the range.
I'll create a new document and add an example (Link Below).
I've got most of it working, I'm trying to pick a random name from a list, but things mess up once I'm trying to pull 1 from multiple lists.
(Sheet 2 is where the broken formulas are, Sheet 1 is just the list)
docs.google.com/spreadsheets/d/1QNWVofSwuUe8S-raA9lHreWG2mB9TpPf-dkP-ox59qk/edit?usp=sharing
use 2 separate formulas. ={AllCommon} and then int he next column do ={OtherCommon}
@@ExcelGoogleSheets Ooo okay :o Thank you :) I've found a couple of work arounds now too, I made each of B1:8, C1:8, ect to I1:8 as separate named ranges, and that seemed to work, a little messy but functional. But I'll defiantly give that one ago too :) Thank you. I like learning the different ways to do stuff. You never know when it'll come in handy hehe.
Thank you sir
u awesome bro
Can I use Named Ranges as source data for pivot table?
Not at the moment.
Why formulas not refreshing automatically?? Even i'm not able to find any option to refresh manually
check your settings under File->Spreadsheet Settings
@@ExcelGoogleSheets thank u sir
I created web app but blocked.
How should I do?
Can we use referencing in importrange function?
yes.
@@ExcelGoogleSheets.
Thanks for the reply.. I couldn't able to do that.. I tried absolute referencing, but it didn't apply. Kindly help me out.
I misunderstood your question, I thought you were asking if you can reference a named range in importrange.
Reference in importrange function is text, therefore it will act as an absolute reference all the time. You could do some trickery and concatenate text with row() function to make it work like a relative reference if needed, but otherwise no.
Sir Importange duplicate data problem...
={Year2017,Year2018,Year2019} seem not working, it just show the first array on the first tab :(
={Year2017;Year2018;Year2019}
@@ExcelGoogleSheets Not working :(
Thanks in advance for your help. I have 2 sheets:
1.-Master: Here I import a csv file and made calculation.
2.-Report: Here only use the importrange function.
The imported data for the importrange function give me almost columns correct but some columns with: #value! or #N/A
I checked my formula and I thinks is correct, I search in the Help Forum and I tried with some changes in format, formula, permission. But I can´t make it work.
=iferror( importrange("1pp9f8uPyEOyitZY7UuB4sqQu-7ScukCttVqmxun0hz8/edit#gid=103974437", upper("Filtro_dia!A1:i300")), importrange("1pp9f8uPyEOyitZY7UuB4sqQu-7ScukCttVqmxun0hz8/edit#gid=103974437", lower("Filtro_dia!A1:i300")))
¿can you help me?
What are you trying to do?
Your formula just for importrange should be =importrange("1pp9f8uPyEOyitZY7UuB4sqQu-7ScukCttVqmxun0hz8", "Filtro_dia!A1:i300")
Is there a way to add up numbers in a formula when in between other words such as if they were part of email addresses such as user1@gmail.com, user2@gmail.com, user3@gmail.com ect
To make it easy
It would have been better if you quick explain whats the function do then start the video
HOW TO DISPLAY IMAGE DYNAMICALLY IN GOOGLE SHEET
Hi,
Thanks for the tutorial.
I am trying to plot data in a graph where the range of data is dependent on selection. I've managed to do the application in MS Excel (with assistance from the following video: ruclips.net/video/sHfWRb2yUrM/видео.html)
I would like to do a similar application in sheets? Please give me some guidance. Your assistance will be useful. Thanks.
Regards,
Ed