Learn how to link to (refer to) data form other spreadsheets (files) in Excel & Google Sheets in a super dynamic way. #excel #googlesheets #sheetsexcellence
Пікірлер: 29
Жыл бұрын
It is always amazing to watch and learn from your videos. Thank you so much!..
@MehdiAghili-bq1el4 ай бұрын
It was great presentation, practical solution explained in a simple manner, thank you.
@TheDroberto87 Жыл бұрын
Just the usual quality. All of your videos are great and amazingly, almost any of your videos benefit me in some way! This and your other channel are brilliant! Keep it up!
@anvi84 Жыл бұрын
I use Importrange function frequently and had no idea I could use range name as reference instead of tab name and column range. This tip is very helpful. Thank you for sharing it with us.
@huzaifahshah3841 Жыл бұрын
Nice work 👍
@nickreynolds7935 Жыл бұрын
I was just setting up a new workbook and this is about to solve all my issues I think. Thank you
@HenrievanderMeer Жыл бұрын
Again a great video. Thanks a lot
@mustansirujjain7 ай бұрын
Superb technique...
@abinayas6151 Жыл бұрын
Good one😊👍
@KarolKarasiewicz5 ай бұрын
:-) easy, smart and surprising! I wander, how do You find such solutions?... Like puzzles - I knew ever block, but I didn't make such smart connection.
@azeez7675 Жыл бұрын
Super useful.. thank you very much
@khaledothman4324 Жыл бұрын
Thank you
@snwlms Жыл бұрын
Really helpful. Thanks a lot
@user-eh7xd1ne9t11 ай бұрын
✅ 12.9 ❇️
@vuppalarohitanjan Жыл бұрын
Thanks for the video, it's really helpful to me , as I am MIS emp I usually use the import range reference function quite often whenever some rows and columns are added it will be a headache to me to change the range again and again Now with the help of this video, I would manage to work easily and quickly with no headache 😂 One doubt is it works in the Vlookup cell ref If it works it will be very great, Kindly once you look into it , I will try my best from my side to work on the Vlookup use range name as shown in the video, if it does not work, I need help on it Once again thanks for the videos , your doing a great job Keep going 😊
@ExcelGoogleSheets Жыл бұрын
Use XLOOKUP
@impossible3611 ай бұрын
what if there have new values insert on the top end of column.... then the range will not work again... how can we also count in the upcoming values in future?
@101BangTons Жыл бұрын
13:16 what you demonstrated is in the cell. What if I want B1 to be a validation? Keep using your tutorial video. What I want to try is column B validating from another range spreadsheet file? So the user/me can only choose from the dropdown that has been defined in another spreadsheet. (In this case there is touch input from humans)
@ExcelGoogleSheets Жыл бұрын
Not clear to me how you want it to work.
@lpanebr Жыл бұрын
Great! I've been using this for a long time and found that when I use the copy sheet (tab) to another spreadsheet, the named ranges stop working and wherever they were used I get a !REF. The fix is to delete and recreate them and correct all !REF issues. So I just stopped using named ranges in sheets I plan to copy.
@ExcelGoogleSheets Жыл бұрын
That makes sense. I almost never use named ranges internally in the spreadsheet, I only end up using them from different spreadsheets or scripts.
@huzaifahshah3841 Жыл бұрын
Sir how we links excel sheet with Google sheets same senerio
@Electric-Bob Жыл бұрын
Teacher... =ArrayFormula(IFERROR(IF(MOD(ROW(C18:C29)-ROW(C18)+1,2)=0, C20:C29-INDEX(C19:C20, FLOOR((ROW(C18:C29)-ROW(C18)+1)/2)+1), C20:C29-INDEX(C18:C19, CEILING((ROW(C18:C29)-ROW(C18)+1)/2))))) The purpose of the formula is to calculate two running totals in one column, with the running total values alternating with each row. The formula achieves this by using the MOD(ROW(C18:C29)-ROW(C18)+1,2) function to check whether each row number in the range is even or odd. If a row number is even, the formula calculates the running total starting from that row by subtracting the value in the current cell from the value in the cell two rows below and then subtracting the value in the cell one row below from the result. If a row number is odd, the formula calculates the running total starting from that row by subtracting the value in the current cell from the value in the cell below and then subtracting the value in the current cell from the result. To retrieve the appropriate values from neighboring cells based on whether the row number is even or odd, the formula uses the INDEX function. The FLOOR and CEILING functions are used to calculate the appropriate index for the INDEX function, based on whether the row number is even or odd. To handle any errors that may arise in the formula, the IFERROR function is used to return a specific value in those cases. It should also be noted that the formula treats cell C18 as a distinct even absolute cell and cell C19 as a distinct odd absolute cell, while all other cells in the range are relative cells. This is because the formula depends on the position of the first row in the range to determine whether to start with an even or odd row number, and therefore the calculations for subsequent rows are relative to that initial row. In summary, the formula is an alternating formula that calculates two running totals in one column based on whether the row number is even or odd. It uses the INDEX, FLOOR, CEILING, and IFERROR functions to facilitate the calculations. The formula is dependent on whether the first row in the range is even or odd. If the first row in the range is even, the formula calculates the running totals by subtracting values in pairs of cells, starting with cell C18 and alternating between subtracting the value in the cell above and the value in the cell two rows above. If the first row in the range is odd, the formula calculates the running totals by subtracting values in pairs of cells, starting with cell C19 and alternating between subtracting the value in the cell above and the value in the cell below.
@yhavin11 ай бұрын
How can I make the named range have a dynamic ending reference in Excel? My source table is growing every day or week, and if I use the entire columns as the named range, it goes till the bottom row and includes blanks, and thus my destination file gets bloated up to 50MB with all the empty rows.
@ToniChirila10 ай бұрын
Yhavin you can achieve that by transforming the data in a table. Look at the message of Mr. JoseAntonioMorato. It is exactly what your need.
@classyaccessories2540 Жыл бұрын
seems like, i don't know anything about sheets 😂after seeing this
@JoseAntonioMorato Жыл бұрын
My dear. Instead of creating a named range, wouldn't it be better to turn the entire range into a table? The formula in the other worksheet would look like this: =SUM('Expense Business.xlsx'!Table1[Amount]) 🤗