Google Sheets: Get List of all Sheet Names and Reference Cells on other sheets
VloĆŸit
- Äas pĆidĂĄn 2. 08. 2024
- Google sheets function to get a list of all sheet names. This sheet names formula automatically generates a list of the google sheet tabs in a worksheet. Programming the automatically updateing index(array) of all the sheets in the workbook. How to use reference a sheet name in google sheets using simple code. I show the coding on how to get data from a cell in another sheet in google sheets using the indirect function google sheets.
đ° Join the members only chat room: / drawbridgefinance
đ Purchase Custom Spreadsheets: www.etsy.com/shop/drawbridgep...
đ° All my links: www.linktr.ee/drawbridgefinance
⏠BROKERAGES I USE âŹâŹâŹâŹâŹâŹâŹâŹâŹ
âșInteractive Brokers: www.interactivebrokers.com/mk... (Paid Link)
âșWealthsimple Trade for CANADIANS: Get the cash equivalent of two free stocks my.wealthsimple.com/app/publi...
⏠Discount to Seeking Alpha Only $99/annually âŹâŹâŹâŹâŹâŹâŹâŹâŹ
âșwww.sahg6dtr.com/2RSPJ5/R74QP/
⏠CONTENTS âŹâŹâŹâŹâŹâŹâŹâŹâŹâŹ
0:00 Intro to Google Sheets
1:10 sheetNameArray function make a list of tab names
2:14 Reference a Cell to Refresh the list
3:02 Edit the Apps Script
3:46 Variable value skip sheets
4:12 Indirect Reference a cell in another sheet
5:18 Reference another cell relative to the array
⏠GREAT FINANCIAL BOOKS âŹâŹâŹâŹâŹâŹâŹâŹâŹ
âș Unlucky Investorâs Guide to Options Trading(Pre Order): amzn.to/3ETWQpa
âș Think and Grow Rich: amzn.to/2t285sL
âș The Wealthy Barber: amzn.to/2sW9XTM
âș The Millionaire Next Door: amzn.to/2HB6DTk
âș Rich Dad Poor Dad: amzn.to/2y5rD4S
âș Getting Started in Options: amzn.to/2LEJzWe
âș Smart Couples Finish Rich: amzn.to/2Eu1qgr
⏠Trading Computer âŹâŹâŹâŹâŹâŹâŹâŹâŹ
âș LG OLED 48â 4k TV/Monitor: amzn.to/31lJPH8
âș Samsung 28â 4k Monitors: amzn.to/32QZL4r
⏠Live Stream Gear: Video âŹâŹâŹâŹâŹâŹâŹâŹâŹ
âș Atem Mini Pro: amzn.to/3zRhJjA
âș Glide Gear Teleprompter: amzn.to/3FSYnNl
âș Elgato Stream Deck XL: amzn.to/3FMYV7t
âș Iphone Lightening HDMI Adapter: amzn.to/3qLoVKh
âș Joby GorillaPod (Bendy Tripod): amzn.to/3JsQ5xQ
âș Manfrotto Tripod with Video Head: amzn.to/3bi67tr
-- As an Amazon Associate I earn from qualifying purchases --
Subscribe to Drawbridge Finance on CZcams: czcams.com/users/Drawbridgef...
My primary investment strategy is long term high yield dividend investing, index funds and reducing risk and exposure using options. I have been actively trading the stock market for over 25 years and have built most of my wealth by reinvesting my dividends and following my 14 Personal Rules of investing. I actively trade options on both the American and Canadian Stock exchanges using options strategies and buying and holding high yield dividend paying stocks.
I generate monthly income in two ways. Averaging more than an annual 7% return by collecting dividends on high yield dividend stocks that I hold. The second income stream comes from the selling of option premium and taking advantage of theta decay. I love trading strangles, Iron condors and diagonal spread for maximizing returns. Delta neutral strategies allows me to make money in both bull and bear markets and limits my risk. Both of these strategies are suitable for passive income and create a stable predictable safe passive monthly income.
Want to learn how to trade stocks and make more money? This channel is dedicated to increasing investment returns and building wealth by passively trading in the stock market. If youâre tired of poor performance then make sure you subscribe to Drawbridge Finance czcams.com/users/Drawbridgef... I produce weekly videos for the beginner trader and use simple explanations to learn how I buy and sell stocks for profit.
Letâs Get Rich Together
Levi Woods
Disclaimer: I am not a financial planner and am not offering investment advice. This is an opinion channel only and should not be taken as any form of financial advice. I receive a small commission from the purchase of any item from using the links listed above. There are financial risks involved in taking on any monetary transaction that I discuss in my videos.
#investing #googlesheets #LetsGetRichTogether
The function is entered in the Apps Script and looks like this:
(It's impossible to post this fully here so it's missing the "less than, angled bracket" in the forth line of code between "i" and "sheets.length") but here is the code:
function sheetNameArray() {
var out = new Array()
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
for (var i=6; i sheets.length ; i++) out.push( [ sheets[i].getName() ] )
return out
}
Amazing. Thank you
Glad you liked it!
This was the final piece of the puzzle to compile stats for a tennis score sheet I was making. Thank you so much!
Thanks for the education, Levi. I've been using your sheets for the last couple years and they have helped tremendously. Look forward to the new sheet every month as a Patreon. Totally worth it just for the sheets alone.
Thanks James!
Your script to reference the sheet names is amazing, thanks for being a life saver!
Glad it worked for you!
Thank you very much for your help! really informative video!
Glad it was helpful!
Thank you for sharing this! It will save me a ton of time!
Glad it helped.
thank you so much for posting this. did exactly what I wanted, once I figured out I was missing the < portion of the formula that I cut a paste!!!
Glad it helped
đ° Join the members only chat room: www.patreon.com/drawbridgefinance
đ Purchase Custom Spreadsheets: www.etsy.com/shop/drawbridgeprops
đ° All my links: www.linktr.ee/drawbridgefinance
Thank you so much man!!
Happy to help!
Thank your so much!!!!!
You're welcome!
This is great! Is there a way to add a link next to the tab name that will take the user to the tab?
@5:47 in my experience =INDIRECT(B4 & "!$E$13") would also work; no need to fiddle with the single quotes.
if you are just constructing a single cell reference or range, i don't think omitting the single quotes would make any difference, because the expression is still unambiguous.
maybe if u name your sheets like cell references, like A1, A1!A2 or R1C1, it might be misunderstood, but i just tried and this still works:
=INDIRECT("A1!A2!B1")
if i have a sheet called "A1!A2"
Thanks for that!
Is there a way to pull the names from a different Google Sheet?
this is exactly what I'm look for! Also, is there a way, let say to use an arrayformula to display the data so that I don't have to copy the same indirect formula into every cell? I tried to do it but it doesn't work...
I donât know of a way but you can just pull the formula down to copy it into every cell in a column.
This is EXACTLY what I was trying to do ... up to the 5:55 mark. In your demo, you "fix' the target cell. I am wanting to fix the sheetname.
Specifically, in my spreadsheet in cell C3, I have the "sheetname(2)", which puts the name of the 2nd tab in my worksheet.
Then down in cell A3, I have =indirect("'"&$C$1&"'!A3")
This perfectly pulls in the data from cell A3 of the tab named in cell C1.
Where this falls apart is when I try to copy A3 to cell A4. What I want in cell A4 is =indirect("'"&$C$1&"'!A4") . But what I am getting is *exactly* the same thing I put into C3.
In other words, it's not "advancing" the cell reference.
Is there an "opposite" to using the $ in an cell reference to FORCE it to be relative when copied?
Iâm not sure how to get the exact formula to change as you requested but you can use indirect with ranges to pull multiple cells which may help in your situation. =indirect(C1&â!A1:A2â) is a single formula that will fill two cells at once and the range could be expanded: A1:A10 or massive amounts of data with no ending row: A1:F
Is it possible to have sheet names displayed horizontally instead of vertically?
Yes. Watch the short videos I post.
This function does not appear for me, do I need an extension?
This is not an extension. I walk through The entire process. You may just have to watch the video again as you likely missed a step.
Hello. I've been using your script for about 5 months now. But for some unknown reason, it already stopped working. I'm getting an error message and couldn't seem to find out what's causing it. What could be possible reasons that the code stopped working all of a sudden? Thank you.
With no change it just stopped? I would try adding a blank line of code in the script so itâs changed without actually changing it. Save. Then run again to see if it works again.
Is it possible to get the values as clickable links to jump to other tabs/cells?
I wish that was possible also.
@@DrawbridgeFinance i have managed to do it but it was not straight forward đ
@@hxjdjdn6236are you able to elaborate?