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.
*Thank you very much for such useful lessons! Best on RU-vid 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?
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?
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!
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
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.
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).
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 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?
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?
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: ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-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
