Check out my full courses and ebooks here 👉 www.howtoexcel.org/courses/
@nogoogle6349
2 жыл бұрын
Formula isn't working
@victoriaplatt3126
10 ай бұрын
Thank you for this video. It worked brilliantly and instantly made a list of (52) sheet names which was very useful. Thank you. Much appreciated.
@nimrashabbir7001
Жыл бұрын
how do we create a link to the list created by macro?
@rks3488
5 жыл бұрын
List the sheet in one sheet and hyperlink that sheet was the thing i was searching...I was complete video..though i have a doubt...How can i name hyperlink to different name other than just GO TO SHEET ? Help will be appreciated
@HowToExcelBlog
5 жыл бұрын
Just replace GO TO SHEET with the text you want to appear!
@ansuyadav2780
5 жыл бұрын
Can anyone explain me the formula for second trik....
@ajaygaikwad5308
4 жыл бұрын
You just came out as a life saver with this video.. I work on a file which has 300sheets and I was doing this manually so far. Thank you very much
@HowToExcelBlog
4 жыл бұрын
Ha, I wouldn't want to be doing that manually 😂
@sohamsaha1422
Жыл бұрын
How can I list data from each sheet as well. Lets say I have listed all names, and adjacent to that, I wanna list specific cell from each work book. Please let me know
@erikguzik8204
2 жыл бұрын
Very cool trick, I've been looking for this idea for a long time. Update -- for those who now have office 365. To update what he put in Col(B) put in this formula =SEQUENCE((SHEETS())). This will give you a list from 1 to Number of sheets you have.
@vitarathiel
Жыл бұрын
what if for office 2013 ?
@tutsecret499
Жыл бұрын
The list with the vba method does not have hyperlink to jump to the selected sheet?
@gregoriusbayuaji7962
2 жыл бұрын
Hi! Excuse me, why my get.workbook(1) only shows the first cell and my Replace function is not valid? What's wrong with it? Thank you
@MelW999
4 жыл бұрын
Thank you, I actually got it to work! Can you add a button on sheets to take you back to index?
@HowToExcelBlog
4 жыл бұрын
Use the HYPERLINK function for this.
@sandyapujari5448
4 жыл бұрын
For criss cross Hyperlink do we have to individually give link
@HowToExcelBlog
4 жыл бұрын
What does criss cross mean?
@megabite3178
19 күн бұрын
how to add these to the ribbon
@dirkflanigan4511
2 жыл бұрын
Hello, I hit subscribe Thank you, May you please send me the code? this is exactly what I need. Thanks again.
@jessehing-mather3196
4 жыл бұрын
Is there a way to use the listed sheet names as a reference to get data from that sheet with formula? Say I want to get data from the sheet names Alabama without having to manually select it.
@HowToExcelBlog
4 жыл бұрын
Yes, use the INDIRECT function.
@emiroviski
4 жыл бұрын
is there a way to make this dynamic? i mean what if i wanted the list to update automatically after creating a new sheet?
@HowToExcelBlog
4 жыл бұрын
You have to manually recalculate the sheet.
@urjasanghvi3526
4 жыл бұрын
Hey! Need you help I tried doing it but a dialogue Box is displayed which says This Operation has been cancelled due to restrictions in effects on this computer. please contact your system Administrator . Can you please guide me further
@TSZ0111
2 жыл бұрын
question regarding the second method, I did the same thing but the result returns #N/A , how do i fix it ?
@palashmondal9088
4 жыл бұрын
Thank you so much! This video is very helpful.
@HowToExcelBlog
4 жыл бұрын
You're welcome
@tomronstone
3 жыл бұрын
Sir, you have changed my life 🙏
@HowToExcelBlog
3 жыл бұрын
Ha, Excel is life changing 😂
@gururajchawan
4 жыл бұрын
Hello! Hyperlinking didn't worked for me. Can you please help?
@terrydouglasjayasuriya5162
3 жыл бұрын
Great video, and thanks. I would like to provide a description of the listed sheets. Upon adding another new sheet, I would like the corresponding descriptions of the sheets to also shift. Pls kindly adv. Thanks in advance.
@tetocaloscojones8229
4 жыл бұрын
the download link does not work...no file is downloaded...no chance to do that ...I already signed up. How to do to download the file you are working on the video ? Thanks
@leomessi8098
2 жыл бұрын
Thanks for the very simple explanation. Very helpful in saving time
@shahbazraza8605
7 ай бұрын
Awesome
@parmeshwarcctv
5 жыл бұрын
Non VBA Trick was new for me, thanks for sharing John, You explained Hyperlink function too, I need your help in Hyperlink.... One of my project have drop down and according selection there is different web link create by hyperlink, but first link is work fine, but on change drop down selection hyperlink function not updating, could you guide me ?
@Larwai
3 жыл бұрын
Thanks, the VBA works for me.
@HowToExcelBlog
3 жыл бұрын
Great!
@ROHITKUMAR-cb9fs
4 жыл бұрын
I am not able see properly of (getworkbook formula) could you please write here so i could see property
@meghawalia3285
2 жыл бұрын
Can u please mention the formula over her so that we can directly write it down
@Samer5746
2 жыл бұрын
what if i want to list all sheets name that only lies between two specific sheet and dont show the rest of the sheets in the list??
@wayneedmondson1065
5 жыл бұрын
Hi John.. just viewed this again. Inspired me to create a UDF as follows: Function ListSheetNames(IndexNo As Integer) As String Application.Volatile Dim col As New Collection Dim ws As Worksheet For Each ws In Worksheets col.Add ws.Name Next ws ListSheetNames = col(IndexNo) End Function Can use the above like the XML Macro 4, but it is more dynamic with Application.Volatile. If you add.. Private Sub Worksheet_Activate() ActiveSheet.Calculate End Sub ..to the subject Index sheet, then the results auto-update with any changes you might make.. add/delete sheets.. change sheet names.. move sheet positions, etc. I find it handy for making those sheet indexes or a table of contents that you then link with the HYPERLINK function, as you demonstrated, but that might get morphed and modified in the future. Anyway, thought I would share this. Thanks for the tips and the inspiration to create something new for myself. Thumbs up!
@HowToExcelBlog
5 жыл бұрын
I think I remember reading that UDF can also spill. You should try one that returns an array of the sheet names.
@wayneedmondson1065
5 жыл бұрын
@@HowToExcelBlog Good idea.. will give it a try. Thanks!
@HowToExcelBlog
5 жыл бұрын
Let me know how how it goes. Sounds like it might make a good video if you get it working.
@jeremyh.1243
4 жыл бұрын
@Wayne sounds like what I need. Do you have a template file you could share or a video presentation of what you mention? I like the auto update feature you talk about.
@wayneedmondson1065
4 жыл бұрын
@@jeremyh.1243 Hi J. Hdz. - see my new post (on 8/1/20) to John M. I added the code for a UDF that will spill all visible worksheet names and is volatile and so will react to changes. If you have Microsoft 365, it should work for you. I don't have a video, but maybe John will make one. You should be able to copy and paste the code into a module in your workbook and then save as macro enabled and it will work, assuming you have MS 365. I don't have a web site with downloadable files, but if you really need it, might be able to do a share to a OneDrive folder. Hope the post helps you out. Good luck!
@jadawson1
5 жыл бұрын
Any ideas on how to get this to auto refresh or worst case include a button. Data refresh and auto calculate doesn't seem to update new sheets added. If I click on index formula and hit enter after a sheet is added it does update.
@chiragpatel1112
4 жыл бұрын
Hi john great video, i wanted to know what is in that A1, if I want to have the value of that cell A1 displayed in that cell? how may I display value?
@wayneedmondson1065
5 жыл бұрын
Hi John.. thanks for the tips. Was familiar with For Each method in VBA. Thanks for the tip on the XML Macro 4 method and hyperlink bonus.. very helpful. Like your style.. short.. sweet.. to the point. Thumbs up!
@manikandanpalanisamy8815
4 жыл бұрын
Sir When i save the file it show like that and i save on macro book format. if again open the file it will not showing
@TheAlangham
4 жыл бұрын
I have a sheet named "cover" and another named "data" that i want to exclude from the list. How would i go about that?
@suatalturk3639
3 жыл бұрын
great thank you much:)
@HowToExcelBlog
3 жыл бұрын
Welcome 👍
@sarahalkahery1061
2 жыл бұрын
Thank you! I needed to do this for work and it was going to take me hours
@frenchtranslator
2 жыл бұрын
Thank you for saving me thousands of hours.
@noah1gulfbreeze
5 жыл бұрын
Very nice...as always, on point and a clean presentation. Can power query provide a variation of the solution as well?
@HowToExcelBlog
5 жыл бұрын
I was planning on making the video 3 ways to list sheets, but it turns out you can't self-reference a workbook to get sheet names (only to get tables). Check out my blog on getting sheet names from a folder (current workbook can't be in the folder). www.howtoexcel.org/power-query/get-all-sheet-names-from-all-workbooks-in-a-folder/
@brianxyz
5 жыл бұрын
@@HowToExcelBlog I just tried doing this bringing in a folder and was able to get it work with the current workbook as part of the folder. After a bit of filtering I was able to load the file names into Excel.
@HowToExcelBlog
5 жыл бұрын
Hmm, I got an error. Maybe I'll try again tomorrow.
@gururajchawan
4 жыл бұрын
Agree! 😊
@BLUEPLANETJAZZ
2 жыл бұрын
This is brilliant--thank you!!!!
@allabout1135
5 жыл бұрын
WOW this was reall cool trick.
@HowToExcelBlog
5 жыл бұрын
Thanks Aleksandrs
@sabrinalimage166
4 жыл бұрын
Thank you for this video! I was wondering...Is there a way to use this method (non-VBA), but only return sheet names under a specific condition? For example, I would like to return a list of sheet names but only if the conditional formatting on that sheet turns yellow. If not then my list would remain blank (hope that makes sense!)
@nikden76
3 жыл бұрын
if I could leave a hundred of likes I'd do that!!! You literally saved me hours of life! Will go grab a drink now! ))
@HowToExcelBlog
3 жыл бұрын
Good to hear! If something is time consuming there's always a better way!
@LaLLeR33
4 жыл бұрын
Thank you! You saved me a lot of time :) Great video! Easy to use! Thanks again!
@Wizhka
2 жыл бұрын
Gracias. Me es de gran utilidad.
@halpwr
2 жыл бұрын
you rocked it, thank you!
@khemrajrana7322
5 жыл бұрын
Great sir & You upload video please provide excel sheet in descroption
@PALLAB58
3 жыл бұрын
Thank you
@HowToExcelBlog
3 жыл бұрын
Welcome 👍
@alpesh9666
3 жыл бұрын
thanks nice tutorial
@EmmanuelOlaleye
4 жыл бұрын
Great video. Helped me a lot but how do I handle it when i rename one of the older sheets.?
Пікірлер: 84