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.
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!
:-) 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.
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?
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)
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 😊
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.
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.
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.
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]) 🤗
It is always amazing to watch and learn from your videos. Thank you so much!..
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.
Nice work 👍
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!
It was great presentation, practical solution explained in a simple manner, thank you.
Superb technique...
I was just setting up a new workbook and this is about to solve all my issues I think. Thank you
Good one😊👍
Again a great video. Thanks a lot
:-) 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.
Super useful.. thank you very much
✅ 12.9 ❇️
Really helpful. Thanks a lot
Thank you
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?
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)
Not clear to me how you want it to work.
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 😊
Use XLOOKUP
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.
Sir how we links excel sheet with Google sheets same senerio
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.
That makes sense. I almost never use named ranges internally in the spreadsheet, I only end up using them from different spreadsheets or scripts.
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.
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.
seems like, i don't know anything about sheets 😂after seeing this
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]) 🤗
Sure, you can do that too.