Excel - "Smart" Sheet Names (I bet you've never seen this before)

แชร์
ฝัง
  • เผยแพร่เมื่อ 28 ม.ค. 2024
  • Learn how to have sheet names change when selected or deselected. This uses a simple lookup function and a pair of event-driven macros. Code can be copied from the sample file (link below).
    File download link:
    www.bcti.com//wp-content/YT_D...
  • วิทยาศาสตร์และเทคโนโลยี

ความคิดเห็น • 98

  • @GuillermoPalchik
    @GuillermoPalchik 2 หลายเดือนก่อน +3

    Every single video you post is awesome.

    • @bcti-bcti
      @bcti-bcti  2 หลายเดือนก่อน

      Thank you so much for saying so. I have a big smile! Cheers.

  • @ashleymoller5951
    @ashleymoller5951 4 หลายเดือนก่อน +5

    So Simple - so very useful! Going to add this to half a dozen workbooks now.

  • @maciejkopczynski55
    @maciejkopczynski55 4 หลายเดือนก่อน +4

    Absolutely peak scenario! I am simply amazed! Magnificent work - your content is so unique!

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +2

      Thanks for watching. I try to show things that haven't been shown by 1,000 other publishers.

  • @SndfOmar
    @SndfOmar 2 หลายเดือนก่อน

    Wow! Absolutely 💯 AWESOME! You are not only creative, but also generous dude! Just thinking of this idea!

    • @bcti-bcti
      @bcti-bcti  2 หลายเดือนก่อน +1

      Thanks. It’s a weird and mostly useless idea, but it sparks creativity for other things. Thanks for watching.

  • @joyflowmonger248
    @joyflowmonger248 4 หลายเดือนก่อน +1

    Extraordinarily creative! I love it, so much! Thank you for publicizing your discovery!

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      Thank you for wathcing!!!

  • @iankr
    @iankr 4 หลายเดือนก่อน +1

    Boy, this is genius! Why did I not think of this?! 😅
    Many thanks. I can think of a few workbooks I use that would benefit from this. 👌

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +2

      Fantastic!!! So glad to hear.

  • @tb9359
    @tb9359 4 หลายเดือนก่อน +1

    This is supercool. I am already thinking how I can use this at work for a couple of workbooks. :) Thanks. Great idea!!

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      Thanks so much for watching!!!

  • @robertw236
    @robertw236 4 หลายเดือนก่อน +1

    Awesome Sauce. Will be using as standard. Thanks for sharing.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      Thanks for watching!!!

  • @marrzeus6205
    @marrzeus6205 2 หลายเดือนก่อน

    This is some kind of black magic f...kery! JK. So simple and yet so elegant. I'm absoluetly loving this channel! The power query series has been incredibly helpful, thank you.

    • @bcti-bcti
      @bcti-bcti  2 หลายเดือนก่อน

      Thanks so much for taking the time to watch and respond. Cheers.

  • @querrythis
    @querrythis 2 หลายเดือนก่อน

    Indeed, never seen this before. Pretty slick.

  • @GuruDanny
    @GuruDanny 4 หลายเดือนก่อน

    Simple but powerful - Thanks for sharing

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      Thank YOU for watching!

  • @cablegurl328
    @cablegurl328 4 หลายเดือนก่อน

    I will subscribe and give you a thumbs up because this is the best! I adore it and want to use this approach into a couple projects.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      Thank you so much for the nice words and your subscription. It's always nice to see the number go up!!! Thanks for watching.

  • @T0NYD1CK
    @T0NYD1CK 4 หลายเดือนก่อน

    As soon as you described it my immediate thought was that would be so easy to do.
    Then I wondered why I had never thought of actually doing it!
    Very clever idea. Thank you. I know a spreadsheet where this idea will be implemented later today.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      I am amazed at the number of times I've had those, "Why didn't I think of that?" moments. Thanks for watching.

  • @LosoIAm
    @LosoIAm 4 หลายเดือนก่อน

    Brilliantly awesome, thank you!

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      So glad you enjoyed it. Thanks for watching.

  • @johanverkerk3260
    @johanverkerk3260 2 หลายเดือนก่อน

    Great job, I made it without any VBA experience and it works great, also helped other people.

    • @bcti-bcti
      @bcti-bcti  2 หลายเดือนก่อน +1

      Glad to hear.

  • @salahaldeen7924
    @salahaldeen7924 23 วันที่ผ่านมา +1

    insightful vid, Thank you for your time

    • @bcti-bcti
      @bcti-bcti  23 วันที่ผ่านมา

      Thank you for your time and thanks for watching

  • @rwfrench66GenX
    @rwfrench66GenX 4 หลายเดือนก่อน +2

    This is a cool trick. When I create a workbook with more than 3 tabs I create a sheet with an index and links that jump to cell A1 on each sheet. If I’m using cut off names I have a column with the full name on the Index sheet.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +1

      That is a much better idea. This was all just for an interesting bit of fun.

    • @rwfrench66GenX
      @rwfrench66GenX 4 หลายเดือนก่อน

      @@bcti-bcti I think it’s cool it changes the name automatically! I started using VBA back in Excel 98 and using a macro like this is very cool.

  • @777tmack
    @777tmack หลายเดือนก่อน

    What a find! An immediate subscribe and bell icon!

    • @bcti-bcti
      @bcti-bcti  หลายเดือนก่อน

      Thank you so much!!!!

  • @alexrosen8762
    @alexrosen8762 4 หลายเดือนก่อน

    Fantastic tutorial 👌

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      THank you so much. And thanks for watching.

  • @michaelt312
    @michaelt312 4 หลายเดือนก่อน +2

    This is incredibly useful. Too bad a lot of businesses don't allow Macro. But for personal use, absolutely!!

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +3

      I agree. Macros are incredibly useful. Good thing businesses aren't putting the "smack down" on Power Query.

    • @michaelt312
      @michaelt312 4 หลายเดือนก่อน

      @@bcti-bcti, agree there. That's where I live. But I did recently discover the CELL function. Nesting MID, CELL and FIND has been useful.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +2

      Knowledge is POWER!! Especially when using Excel.@@michaelt312

  •  4 หลายเดือนก่อน +1

    Thanks Great example.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +1

      Than you for taking the time to watch!!!

  • @harikc872
    @harikc872 4 หลายเดือนก่อน

    Great Brain Job....excellent....

  • @willbedeadsoon
    @willbedeadsoon 4 หลายเดือนก่อน

    Although I usually jûst right click on the sheet navigâtion tab, I think your trick is amazing. Code is isimple, but the idea is not so obvious.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      Yeah, the obvious is sometimes the most elusive.

  • @redhaakhund1271
    @redhaakhund1271 4 หลายเดือนก่อน +1

    Excellent 👍👍👍👍👍

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +1

      Thanks for taking the time to watch.

  • @alamiralshafay
    @alamiralshafay 4 หลายเดือนก่อน +1

    Very Nice Idea

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +1

      Thanks. And thank you for taking the time to watch.

  • @Malakawaka
    @Malakawaka 4 หลายเดือนก่อน

    Nice!

  • @gregorytaylor2749
    @gregorytaylor2749 4 หลายเดือนก่อน

    Genius!!

  • @bryanlane31
    @bryanlane31 4 หลายเดือนก่อน +1

    The only “bad” thing about this for me is that I retire on March 1st and won’t have the need to use it after that! I have always been a fan of tricks like this all the way back to Lotus 1-2-3.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +2

      Best of luck in your retirement. I'm sure there's a part of all this that you'll miss. Cheers.

  • @brendanmoran9313
    @brendanmoran9313 4 หลายเดือนก่อน +2

    I’m afraid I disagree with all the comments based on the two click rule.
    It should only take you two clicks to get from any one sheet to any other sheet - regardless of size.
    You should have your first sheet as a Main Menu with internal navigation to all sheets.
    Each sheet should also be linked back to the Main Menu.
    Two clicks take you from any sheet to any other sheet.
    This avoids infinite scrolling.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      You are absolutely right. I couldn’t agree more. This was merely an exercise in creativity and brining the concept of event-driven macros to people’s attention. Thanks for taking the time to watch and comment.

  • @wmfield152
    @wmfield152 4 หลายเดือนก่อน

    Super!

  • @7bettyblu
    @7bettyblu 4 หลายเดือนก่อน

    No doubt about it, this is a great. Would be perfect if I can refer to a Table Col instead of a cell range so new sheet names can be automatically populate without having to modify the code repeatedly. How do we specify Table Col in "Visual Basic" code?

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      I agree, but you could just refer to either the entire column (ex: “List!A:A”), or just set the range to something you don’t ever expect to reach (ex: “List!A1:A1000”). Thanks for watching.

  • @hankgrimes1806
    @hankgrimes1806 4 หลายเดือนก่อน +1

    How, exactly, did you get the full sheet name to appear in the first couple of rows of each sheet? Is this done with the VBA code you shared or, is there another step needed to accomplish this? I like keeping sheet names as short as possible for the sake of clearer formula writing, so this technique would really come in handy, especially if I can get the full sheet name to display in the 2nd or 3rd row as a title.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      You can use the SHEET function to return the sheet's number in the workbook. ex: =SHEET()
      This could then be used in an INDEX function to return a word from an existing list. ex: =INDEX(List!A1:A100, SHEET())
      That's just an idea off the top of my head. I'm sure there's a lot of creative ways to use this.

    • @hankgrimes1806
      @hankgrimes1806 4 หลายเดือนก่อน +1

      Thank you for your reply and, like so many of my fellow followers of your work, I think you're amazing, and I will definitely start "retro-fitting" my workbook with this feature! 🙏@@bcti-bcti

  • @user-PHB
    @user-PHB 4 หลายเดือนก่อน

    Is there a kind of MouseOver event, so you could change the tab name before even clicking it? Would be even cooler.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      Unfortunately, no. I looked into that as I thought it would be neater to have the tab name changed just by hovering over it. Excel has no mechanism to detect the presence of a mouse pointer when hovering over a sheet tab. Thanks for watching.

  • @barrywood7741
    @barrywood7741 4 หลายเดือนก่อน +1

    My sheet names are codes which are C01 through to C30. I have created a list C01 to C30 in column A, and a list of the different names in column B. Copied and pasted your code and altered slightly the row numbers as suggested. Mostly it works but on several nothing changes and on others it shows the names, not the codes. I'll do some debugging here but very odd. Liked the idea very much and once I've sussed this issue out I will use in many of my spreadsheets.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      If you can post your file so that I can download it, I'd be happy to take a look and see what I can figure out. Thanks for watching.

    • @barrywood7741
      @barrywood7741 4 หลายเดือนก่อน

      How would I post this file?@@bcti-bcti

    • @barrywood7741
      @barrywood7741 4 หลายเดือนก่อน

      I thought I answered, but can't see my comment. It was, how can I send you my spreadsheet?

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      You can send it as an email to training@bcti.com @@barrywood7741

  • @JJ_TheGreat
    @JJ_TheGreat 4 หลายเดือนก่อน

    2:26 And wouldn't it also help if we used Power Query to get a list of the "tab"/sheet names?

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +1

      There you go! Now you’re getting creative!!!

  • @LarsV62
    @LarsV62 4 หลายเดือนก่อน

    This trick has a potential major flaw of the hidden list is subject to changes in the future. For that, it would be better to format the list as a table and refer to the column names directly in the formulas. Any new or deleted rows in the list works then not require modifying the code, and moving the table itself by inserting leading rows or columns would not matter to the formulas.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      One would think that using a proper Excel Data Table would be a better strategy, but I think you'll find, with a bit of testing, that this introduces an entirely new set of problems and shortcomings.
      My guess is that using a formula that discovers the length (i.e., height) of the table would be needed. Like another MATCH function that searches the entire column (A:A) for something that will never be found (99^99), and performs this as an Option 1 (closest without going over) type of search.
      Thanks tor contributing to the discussion.

  • @Dexter101x
    @Dexter101x 4 หลายเดือนก่อน +1

    Cool

  • @Nonplused1
    @Nonplused1 4 หลายเดือนก่อน

    That's great unless you have macros that refer to sheet names. I suppose you can use the sheet objects instead, but who does that?

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      Yeah, it has it's issues. It's a just for a bit of fun (but limited in its practicality.)

  • @kevinkohut5096
    @kevinkohut5096 4 หลายเดือนก่อน

    This will break any formulas that reference the sheet name

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      Yes, you are absolutely correct. It's just a neat thing, provided you don't have that sort of dependency. Thanks for watching.

  • @brendanmoran9313
    @brendanmoran9313 4 หลายเดือนก่อน +1

    …and no macros required. File can be saved as xlsx.

    • @bcti-bcti
      @bcti-bcti  22 วันที่ผ่านมา

      Unfortunately, not. But an alternative might be an .XLSB file (if .XLSM files are blocked by your organization.)

  • @mightydrew1970
    @mightydrew1970 4 หลายเดือนก่อน +13

    Or you could leave the sheet names and just right-click on the tab navigation buttons to get a proper list... I'm a fan of vba and have been for years, but as soon as you introduce it (in a corporate environment) you're in for a world of hurt

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +3

      Agreed. I really love the “right-click” option for the quick list. The point of this was just to show something creative. Thanks for watching.

    • @robinherrick2177
      @robinherrick2177 4 หลายเดือนก่อน +2

      I thought exactly the same. This is a terrible idea. Creativity isn't solving problems that don't exist.

    • @whoslacks
      @whoslacks 4 หลายเดือนก่อน

      100% on the corporate world of hurt 😂

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      Yes, quite likely.@@whoslacks

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +1

      I agree that it's not exactly a Nobel Prize winning idea. It was just an idea that popped into my head one day and I wanted to see if it could be done. The idea may, however, spark someone else's imagination in solving an actual problem. Thanks for watching.

  • @RichardJones73
    @RichardJones73 4 หลายเดือนก่อน

    Yes you are a genius and yes it is worthless lol. No I am just kidding, this is a useful idea

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน +3

      HA! Yes, I recognize the value of this feature, but it is an interesting way to learn about event-driven macros (for those who are unfamiliar.)

    • @JJ_TheGreat
      @JJ_TheGreat 4 หลายเดือนก่อน +1

      ​@@bcti-bctiYrs, this video was quite interesting - especially for me who is a VBA newbie... It is interesting to see what can be done with it!

  • @rdatta
    @rdatta 4 หลายเดือนก่อน

    Be very careful as this can mess up formulas that aggregate across sheets by changing sheets names. Esp if the workbook is used and edited by multiple users concurrently. This looks more like a tool in search of a problem. You could always put an index page with links to each sheet and whatever meta data you want as the 1st page sheet in a book. Ctrl+Left click jumps to that sheet. Will not mess with formulas.

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      I agree. This was just for fun.

  • @OnlyNuBeingSoule
    @OnlyNuBeingSoule 2 หลายเดือนก่อน

    Didn't work. Guess it's for the desktop version of excel and not the 365 version of excel 🤷🏽‍♀️

    • @bcti-bcti
      @bcti-bcti  2 หลายเดือนก่อน

      I don’t believe there is any reason why this wouldn’t work in non-365 environments. Double-check your efforts. Thanks for watching.

  • @thinkIndependent2024
    @thinkIndependent2024 4 หลายเดือนก่อน

    Logic is never " Snakeoil" skills have utility = Value as in "valuable"

  • @ericxls93
    @ericxls93 4 หลายเดือนก่อน

    Not sure, love the idea, but the second you click on the tab, you have a massive title with the estate name, why bother?

    • @bcti-bcti
      @bcti-bcti  4 หลายเดือนก่อน

      THis was all just an exercise in creativity. I can't say it's something I'd use often, either.