I considered myself an Excel savvy, but I didn't know this trick! I only used named ranges as absolute references. Thank you very much for it, I will definitevely use it in my job.
I have been working in Excel 20+ years and consider myself to know my way around Excel but this I didn´t know was possible. It will be used on a daily basis from now on. Just brilliant.
I Love this, this is a feature that you look for new places to use it in. This really condenses VBA code when used to write formulas to the worksheet, it takes away that tedious R[ ]C[ ] structure.
Great tip. 👍👍👍 I use Named Ranges already from the beginning but wasn't aware to turn them into relative ones instead of absolute. This is certainly a game changer in spreadsheet with a lot of calculations, rows and columns.
Is the use of named ranges preferable to writing formulas such as "=SUM(C$3:C$14)" and "=AVERAGE($C2:$E2)"? You can still drag the SUM formula across the columns and the AVERAGE formula down the rows. Please advise. I truly look forward to your new posts by the way.🙂
Those would work as well, but I didn't want to over-complicate the example with "mixed references", so I kept it simple. But yes, some scenarios would demand the use of "mixed references".
What I can't seem to get a solid handle on are fixed and relative "structured" table references. Any plans for covering this in upcoming videos?@@bcti-bcti
That's a good topic. I'll put that on the list and push it to the top. The next 2 videos are already scheduled, but that one (relative structured references) will be my next. Thanks for the idea.@@hankgrimes1806
pretty cool stuff! didn't know about it. Allready using the fixed ones, not sure if I would use it... Recently, have been zsing the let function to structure my complex formulas for relative references and intermediate results with coding like indention. makin this up now, i.e let( principal, A1, interestRate, A2, payment, A3 interestAmount, principal *interestRate , interestAmount ) not sure, if this example hits the point but yeah ;)
I wish I could edit that. Once a video is posted, it can't be edited; it can only be replaced. Replacing a video causes it to lose all of its view counts and comments. You essentially have to delete the video and upload a brand-new video. At least I have the "F4" key pop-up on the screen when I mistakenly say "F3" to act as a "this was a mistake" video note.
@@bcti-bcti No worries, I appreciate the explanation! Your 'F4' pop-up is a great idea, haha. No worries, just happy to help! And thanks for the explanation.
Ain’t that a beautiful thing! Did not know it was possible but most likely I’ll use it profusely!!!
I considered myself an Excel savvy, but I didn't know this trick! I only used named ranges as absolute references. Thank you very much for it, I will definitevely use it in my job.
I have been working in Excel 20+ years and consider myself to know my way around Excel but this I didn´t know was possible. It will be used on a daily basis from now on. Just brilliant.
I've been doing this since 1989. I learn something new every single day.
I Love this, this is a feature that you look for new places to use it in. This really condenses VBA code when used to write formulas to the worksheet, it takes away that tedious R[ ]C[ ] structure.
I agree. I think this is pretty cool. Too bad it's not more widely taught. Thanks for watching!
Great tip. 👍👍👍
I use Named Ranges already from the beginning but wasn't aware to turn them into relative ones instead of absolute.
This is certainly a game changer in spreadsheet with a lot of calculations, rows and columns.
Never knew that name range could be used this way
You are always wonderful
This was a new trick to me. Thank you!
I was wondering how to do this for some time. -Thanks!
Glad to be of service (By your command). Thanks for watching.
Brilliant demonstration. Many thanks.
Thank YOU for taking the time to watch.
Best of the Best.
That feature IS really cool. One of the best!
@@bcti-bcti I mean that You are ever the best with your Profesional Examples.
Thank you. I assumed you were refering to the feature.@@raulirasuli
Very nice
Is the use of named ranges preferable to writing formulas such as "=SUM(C$3:C$14)" and "=AVERAGE($C2:$E2)"? You can still drag the SUM formula across the columns and the AVERAGE formula down the rows. Please advise. I truly look forward to your new posts by the way.🙂
Those would work as well, but I didn't want to over-complicate the example with "mixed references", so I kept it simple. But yes, some scenarios would demand the use of "mixed references".
What I can't seem to get a solid handle on are fixed and relative "structured" table references. Any plans for covering this in upcoming videos?@@bcti-bcti
That's a good topic. I'll put that on the list and push it to the top. The next 2 videos are already scheduled, but that one (relative structured references) will be my next. Thanks for the idea.@@hankgrimes1806
No, thank YOU, again, for your timely insights and your prompt response. Looking forward to more of your work.@@bcti-bcti
Your video request has been granted. Merry Christmas!@@hankgrimes1806
I love it
Used only few times as relative range.
This will be applicable to workbook not only worksheet I guess
pretty cool stuff! didn't know about it. Allready using the fixed ones, not sure if I would use it... Recently, have been zsing the let function to structure my complex formulas for relative references and intermediate results with coding like indention. makin this up now, i.e
let(
principal, A1,
interestRate, A2,
payment, A3
interestAmount, principal *interestRate ,
interestAmount
)
not sure, if this example hits the point but yeah ;)
)
I agree 100%. I would do it that way as well. This video is suited more for those who don't want to tumble down the rabbit hole of LET functions.
"Audio error: F4 key pronounced as F3 three times at 6:08"
"Typo: F4 instead of F3 (3x)"
"Please edit: F4 key pressed three times, not F3 (6:08)"
I wish I could edit that. Once a video is posted, it can't be edited; it can only be replaced. Replacing a video causes it to lose all of its view counts and comments. You essentially have to delete the video and upload a brand-new video. At least I have the "F4" key pop-up on the screen when I mistakenly say "F3" to act as a "this was a mistake" video note.
@@bcti-bcti No worries, I appreciate the explanation! Your 'F4' pop-up is a great idea, haha.
No worries, just happy to help! And thanks for the explanation.