15 Spreadsheet Formulas Working Professionals Should Know!

[@JeffSu]

What's an "uncommon" Excel or Google Sheets formula you use all the time? 🔢 TIMESTAMPS 00:00 DETECTLANGUAGE 00:46 VLOOKUP mistakes 01:25 Wildcard Asterisk Character 01:50 TODAY 02:15 IMPORTRANGE 03:45 COUNTIF 04:08 COUNTA 04:48 SPLIT 05:43 LEFT 05:55 RIGHT 07:02 ISEMAIL and SUBSTITUTE 08:06 ISURL 08:31 ARRAYFORMULA 09:10 CONCATENATE 10:11 & 10:37 IF and SEARCH 11:49 IFERROR 12:22 SUMIF 13:45 TRIM, UPPER, LOWER, and PROPER

[@JeffSu]

@iPH oh wow that sounds interesting. 3 common formulas put together in an uncommon way 😂

[@markloughtonUK]

I have a Google spreadsheet that contains a price list. I then have various tabs that pull the data in but convert with a GOOGLEFINANCE formula and then I embed the whole workbook in a web page so the customer sees a real time price list and can click between currencies. I use the IMPORTRANGE to pull the base price list in to another workbook that converts to trade prices. All works a treat :)

[@JeffSu]

@@markloughtonUK Oh wow! That actually made a lot of sense over text 😂, I can imagine those tabs in my head right now. That's awesome Mark!

[@RandolphAbelardo]

Query(). Works like ImportRange(), but with added versatility in manipulating data.

[@JeffSu]

@@RandolphAbelardo oh nice! I haven’t used that one to be honest I should check it out

[@prio2483]

24 seconds in the video and Jeff has already me feel special. You are the sweetest Jeff! And I appreciate you too!

[@JeffSu]

Awwww anytime 😉

[@aprudencio]

A couple of tips: Instead of using the left/right formulas, use the month(), year() formulas respectively. Also, Google Sheets accepts rages like A1:A or A:A so use =counta(A1:A) so that way you do not need to update the range if more data is added in that range.

[@JeffSu]

That makes a lot of sense!! Thanks for sharing!

[@garrytreymendeziii5650]

Plus, wouldn’t using the LEFT function include every entry from January, October, November and December in the count since the leftmost character for dates in all four of those months is 1?

[@dakshbhatnagar]

@@garrytreymendeziii5650 Months could be 10,11,12. There the formula would also break. A better way he can do is using =REGEXEXTRACT(cell_reference, "\d{1,2}")*1 to fetch the month and =REGEXEXTRACT(cell_reference, "\d{4}")*1 for the year values and these will be numerical since we would multiply the result by 1

[@tskali9686]

this man is a legend, thank you Jeff

[@JeffSu]

Woohoo thank you Tskali!

[@kevtiew2989]

Hi Jeff, I was impressed by your presentation skill and fell in love after I watched the first video from you very recently. I am a new fan of your channel! I hope to catch up all with all your past videos very soon. 😁 By the way, instead of using left() and right() function to split the month and year, I find the formulas below useful when dealing with dates. =month(date) ➡️ To convert date into month number, eg. Jan = 1, Feb = 2, Mar = 3... =year(date) ➡️ To convert date into year, eg. 2020, 2021 =text(date,"format") ➡️ to convert into your preferred format, eg. text(date,"yyyy") for 2021, text(date,"mmm yyyy") for Jan 2021, text(date,"yyyymmdd") for 20210101, text(date,"mmm") for Jan, text(date,"mmmm") for January Take care and wishing a great day ahead!

[@alisonnjackson9120]

Thanks for adding the text. Super helpful.

[@JeffSu]

Oh that's a GREAT point, I didn't know that Kev! Thank you so much for sharing this is great!!

[@TheOfficialABHS]

also using a length of 1 means it only works for part of the year, it won't work for October through December

[@milianmilianmilian]

@@TheOfficialABHS Great solutioon. Would've been my question seeing the video at 6:00. =month(date) makes sense then

[@DataProfessor]

Lovin’ those Excel formulas, didn’t know a lot of these functions and they’re extremely helpful for quickly processing data. And congrats on 100K Jeff! 😊

[@JeffSu]

Thank you my friend!! Appreciate it!

[@iamkodenusi]

I love the cyber/infosec tip at 9:42. Always great to see and learn ways of protecting customer data! Big W, Jeff. BIG W. Would love a cheatsheet.

[@JeffSu]

Thank you Kolawole! I'm a big proponent of data privacy, thank you 😁

[@mikeolsen_me]

Jeff, recently found your channel. Just a thank you for your content, style and editing. Your info and presentations are EXTREMELY helpful. Thanks again.

[@JeffSu]

Happy to hear it Mike, thanks so much for the kind comment 😁

[@missysmithy25]

I appreciate this video so much, Jeff! I’m going to dedicate some time perfecting this. I want to be a spreadsheet nerd, too!

[@JeffSu]

Wooohooo welcome to the club :)

[@jjy9319]

Just discovered your channel. Super useful tips for a speadsheet newbie, love your tone and positivity. I will share this with so many people ! Thanks a lot

[@JeffSu]

Woohooo thanks Jonathan 😁

[@attorneydoy]

I decided to watch this randomly earlier. And right after i watched it I received a work email that immediately put the sumif function to use! Haha such a time saver. If didnt watched this i would have done it manually more than 700 data! To answer your question, i track my expenses using google sheets, after awhile I randomly realized that it would be nice if i can automatically sum up cells having same colors, for example all red would signify all unpaid bills and if colored it green it will be considered paid. so i made some research and was able to accomplish it. Not sure if i can do it again haha ive been using it for close to 3 years. it has been my favorite custom sheet function. i labeled it “sumcoloredcells”.

[@JeffSu]

Nice!! That's great to hear, thanks for sharing!

[@yokichuchen]

Can't thank you enough!!! Wish your channel will soon grow into 1 mil!!

[@JeffSu]

Why stop there 😉

[@lillogadget]

That intro was 🔥awesome stuff Jeff!

[@JeffSu]

Thank you Billie, hope you found it entertaining 😂

[@ThiruMahal]

Great video! Thanks for highlighting the most common functions!

[@JeffSu]

Thank you Thiru!!

[@leatherneck7778]

I managed to 90% automate an order sheet by stringing together like 6 IF functions. The order sheet had a few formulas running before i started but it was flawed. Basically id have to manually uncheck anything that we still had in stock but didnt want more of, change the quantity for items that were ordered in bulk, highlight items that had been out of stock for a considerable time as those were priority, and a heap of other things. This assortment of functions automated all of that and turned an easy 90 minute job into about 10 minutes of actual manual work.

[@JeffSu]

That's awesome!

[@onetwothree7625]

The accent of "started" 😆! This is such an amazing video

[@JeffSu]

Haha thank you!

[@Kytreeswerving]

Dude I'm Salesforce Consultant and your videos are everything I need in my life.

[@JeffSu]

Glad to hear it Arthur 😂

[@casadesapa]

Really enjoyed this ❤ thank youuuu!!!! I was engaged the whole time and learned a lot

[@JeffSu]

Glad to hear it!!

[@willrodriguez1224]

Great video! I love your energy and enthusiasm for the subject matter.

[@JeffSu]

Thank you so much Will! 😁

[@sandychong3037]

" //chuckles, You are a bold one" But seriously thank you for another amazing video Jeff!!

[@JeffSu]

Hahaha glad to hear it Sandy!

[@kanavgupta4930]

jeff is legend , soo much amazing formulas to ease our work

[@JeffSu]

Wooohooo! Love to hear that, thanks Kanav!

[@markloughtonUK]

I had a CSV given to me yesterday with names and addresses in ready to import into a new system except it wasn't because some names had extra spaces and lower case letters where they should be capitalised and I combined some of these formulas to tidy it right up. Lovely Jubley :)

[@JeffSu]

Nice! That's a perfect example of a real-world usecase!

[@evag3232]

I have been waiting for this soooooooo long !!! Thanks Jeff

[@JeffSu]

Glad you don't have to wait any longer 😁

[@DemetriPanici]

*Once I figured out what CONCAT was at my job. Oh boy did my life change for the better 😂*

[@JeffSu]

Hahahaha I think I felt the same way about Pivot Tables at first 😅

[@cassioalexan]

Thanks for sharing those tips, some of them I haven't used (nor known) before. Your channel is always helpful.

[@JeffSu]

Glad to hear it Cassio! Thank you 😁

[@calmontoya]

Great video, man... I like how we use the same kind of "setting" to manage the sheets, as in 'reduce the size of the first and last column' (so the content has "margins" or some space - for me, it helps a little bit while designing some formats and stuff), and also, I usually delete every row and column that I don't need, so the sheets are always clean and tidy!... now for the way you use the formulas, you taught me a couple of tricks I didn't now, so a big thank you man! Greetings from Colombia.

[@JeffSu]

Glad to hear I'm not the only one who does that! Thanks so much Carlos 😁

[@davidjohnsmith5417]

Thanks Jeff, Love your videos and your sense of humour...keep it up they are really helpful!

[@JeffSu]

Glad to hear it David! Will do 😁

[@sumanjdomala9671]

New to your channel. Still going through all the videos. But boy this was a good video. Specially the split formula is a life saver for me. Thanks Jeff🙏🏻

[@JeffSu]

Welcome to the fam Sumanj!! Happy to have you here 😁

[@legenderekgo]

I actually bookmarked your video in my work laptop. Super helpful stuff!

[@JeffSu]

Awwwww, not your personal laptop?

[@NormanVsNorman]

Damn! Like, I want to use all the formulas even if I don't need them all! Super cool tips! Great content, and very well explained!

[@JeffSu]

Hahaha that's great to hear! I feel the same sometimes when I come across a new formula!

[@phasomyr]

This was unbelievably helpful. Thank you!

[@JeffSu]

You're very welcome!

[@specserge]

Live examples are the best! Thanks man! You're cool 😎

[@JeffSu]

Glad to hear it Sergey!! Thank you 😁

[@beatrizgonzalez5247]

Thank you for that language translator tip. I do work with information from different countries and need to consolidate them in English

[@JeffSu]

That's great to hear! Glad you found a useful tip from this video 😁

[@SherilynHanson]

yay! thank you! i finally found the exact formula i was looking for!

[@JeffSu]

That's awesome to hear Sherilyn! 😁

[@TheRedGoldfish]

IMPORTRANGE is probably one of the most powerful and creepy formulas I use a work. Someone will show me a spreadsheet and I'll be like "Oooh, what's the url? I want to pull data from it into my sheet" I get a 👁👄👁 look

[@JeffSu]

LOL

[@marcobendistinto1931]

This is the first video I have seen from you and I immediately liked it and subscribed. After the video I went to search in your channel if you had more videos like this one. I hope you do more. Btw if you do a video like this one on add ons you use it will be super useful. Thank you for this video I'll be using all of these formulas.

[@JeffSu]

Glad to hear it Marco! And will do!

[@Mr_Versano]

Awesome as always. Thanks Jeff!

[@JeffSu]

Thanks Kobi!! Glad to hear it!

[@yasminebouziane8843]

Great things to share, and I loved the way you explained them! I use most of them at my work, probably a few that I don't use a lot, but it was a good reminder to me, and the DETECTLANGUAGE one was new to me because I don't use it at all. I wish I knew something uncommon, I would be very happy to share. But I guess most formulas that I use are common in my field.

[@JeffSu]

No worries Yasmine! Thanks for letting me know 😁

[@benchia]

This is some S tier content!

[@JeffSu]

Can't argue with you there Ben 😉

[@Virak_mao]

Thank you so much for your creative sharing brother.

[@JeffSu]

You're very very welcome!

[@rytis314]

great, mate

[@JeffSu]

Thanks 😁

[@TechnoBoomer]

Hi Jeff and many thanks for this very useful video. On my side, I usually use regex but I have to admit G Sheet seems very powerful. For sure, this is one of your best videos ! Cheers. François

[@JeffSu]

Hey François! Did you change your channel name? Or is this a new channel? I love the new name regardless! Thank you as usual!!

[@TechnoBoomer]

@@JeffSu I changed my channel name because I am working on a new video project ;-)

[@JeffSu]

@@TechnoBoomer nice! Best of luck!!

[@kakalkairuchi495]

Very cool video! Liked your way of teaching...

[@JeffSu]

You're very welcome! Glad you think so 😁

[@Ousman2ray]

Jeff, I’m here again man. Call me O. I called you my favorite KZbinr today. And your intros- legendary! 👊🏾

[@JeffSu]

Hi O! Thank you my friend!! 😁

[@andrea_dong]

Watching this video has made me realize just how much of an Excel noob I am 😂 Thanks for the tips, Jeff! I’ll definitely refer back to this video if I find it applicable ^-^

[@JeffSu]

Hahaha we've all been there Andrea!

[@kambleji]

Hi jeff, loved your video. Might be useful for me in the near future. Keep posting such videos. I'd be delighted if you post video on some tips to get a desired job for freshers or people with no experience.

[@JeffSu]

Will do, thank you my friend :) For people with absolutely no experience, my networking videos are definitely very relevant!

[@kambleji]

@@JeffSu Sure, I'll check them out. Thanks btw

[@JeffSu]

@@kambleji np

[@eguchiyuuki]

wonderful!

[@JeffSu]

Thank you!! 😁

[@yanranchen1390]

Hi Jeff, I am a new subscriber of your channel, and found some of your interview tips super useful! I am currently looking to change my job and was asked by a headhunter about my current salary and expected salary range etc, I do not want to disclose my current salary as I think this will put me in a more vulnerable position, How should I reply back focusing on the expected salary instead of the current salary? Sorry to ask an irrelevant question to this video!

[@JeffSu]

Hi Yanran - No problem. Do some research (for example on glassdoor) for your new job's salary range. Choose the top 75% and focus on that instead. For example in your reply "I did some research and noticed that this role generally pays $123,000, I'd like to start there and talk about benefits as well..." etc. best of luck!

[@adarshlawhare7771]

I really loved ur dressing sense 👔 It's really awesome ✨

[@JeffSu]

Thank you Adarsh! I have my fashionable friends to thank for that 😂

[@adarshlawhare7771]

@@JeffSu Thanks for the reply Sir. We are also waiting for the Vlog ✨

[@loklok9603]

Thanks Jeff, this is really cool

[@JeffSu]

Glad to hear it! Thank you lok lok!

[@gustavocampos805]

your content is amazing! Thanks

[@JeffSu]

Thank you Gustavo! My viewers are amazing as well 😁

[@Landnar_Van_Gogh]

How can I quick change small parts of formula, like if I have a lot of A2:10 in my formula and I want to change all the 4 to C2:C10 , there's a way of doing it? I pretty sure python has something like this and I wonder if Sheets also have.

[@JeffSu]

Hm..interesting. Off the top of my head I don't know the answer since I would just edit it manually 😂

[@eightfoldsg]

Thanks Jeff!!

[@JeffSu]

You're welcome Kelvin!

[@irfanilyeas7059]

Heyy Jeff Thanks for cool vid But maybe a special request is that if you could share the sheets file/link in the description So your viewers could practice with you I personally learn better when I’m doing these practices with the instructor :D

[@JeffSu]

That's actually a really really good point, I'll bear that in mind next time!

[@frx2v]

The speaking pace is a bit too fast for non native English speakers, but it’s very instructive and useful. Nice video !

[@JeffSu]

Thanks for the feedback 😁

[@properpropertiesmanagement7910]

I love your channel so much. Keep up the great work 👍🏼

[@JeffSu]

Thank you!! Will do 😁

[@increator1]

=D Have a great one. You really come across like Guy from the movie Free Guy. Thank you for the video. I really got some value out of this. I find some of the formulas really useful for my budget analysis.

[@JeffSu]

Thanks for the compliment Henri 😁

[@seanwright2819]

Spreadsheets often need a column with an incrementing number and rather than typing out "1, 2, 3, etc." and/or dragging these numbers down, there is a formula that can number rows & adjust the numbering automatically when new items are added. Assume the list starts at B5, you can use the following: "=ROW()-ROW($B$5)+1" Paste this in B5 and drag it down the column and it will number the items in each row. When you insert a new row in the middle it will automatically update all the numbering. A simple formula but useful. I use this a lot when linking a sheet as an exhibit in a memo as I can go into detail about each item in a numbered list and it is clear which item I am discussing since the exhibit is numbered as well.

[@JeffSu]

Oh this is awesome, thanks for sharing Sean!!

[@seanwright2819]

@@JeffSu I realized after posting this and working in the spreadsheet a bit more that it can be improved. My original method will update existing numbers but does not automatically fill in the number on the inserted row. So my new method is to create a formula in the header that fills all of that in with an array. Assuming your list of items is in C5:C17 use the following formula in the header of your number column: ={"No.";SEQUENCE(COUNTA(C5:C18)-1)} You can replace "No." with whatever you want your number column to be labeled. Notice that the range in the formula extends one row past the range of actual items. This is so you can insert new items in the last row and it will still automatically number. Also, the reason for using the header row to make the array is that you can insert items in the first row and it will still number automatically. So all possible locations where you can insert new items will be automatically numbered. Note that you will have to fill in the item string before the numbers will correct themselves. As a bonus, I noticed we've had errors related to SUM functions not updating their range when items are added, so I came up with the following to fix that. Assuming your list of numbers to be summed starts in D5 and continues down the column to just above the sum: =SUM(INDIRECT("$D$5:D"&ROW()-1)) Now new numbers can be inserted and summed without having to fix the SUM formula every time.

[@JeffSu]

@@seanwright2819 Ok wow you're amazing Sean!!!

[@seanwright2819]

@@JeffSu Thanks! I love spreadsheets, they're like a big puzzle!

[@jakefairhall3832]

Love it Jeff !

[@JeffSu]

Thank you Jake!! :)

[@lukev730]

Amazing work

[@JeffSu]

Thanks 😁

[@dhrushilzaveri]

=int(cell name), but this is only for excel. It can remove the ' that sometime comes. Also the meaning is give back the raw form of the data. Hope this is useful

[@JeffSu]

Got it, thanks Dhrushil!!

[@mcastillo-rama8031]

Incredible video, thanks ! Jeff, I keep asking this question and no-one can answer it. Regarding importrange. If I have a "total or summary" sheet where I've importranged from other sheets, I can't seem to edit this sheet (for example if I want to let one person know I have completed an item in a specific field). Whenever I write something the data disappears. Is there any way around this ? Thanks in advance for your time

[@JeffSu]

Add a new column not related to the importrange :) 😁

[@Wolfie_Desu]

One that I usually use would be the combinations of the IFS and AND functions for a value inside a rage for example since the IFS function works like this IFS(condition 1, value 1, condition 2, value 2), I could do IFS(A1>75%, "above 75%", AND(A150%), "Above 50%") for it to give a range. Although its longer than the nested if formula of IF(A150%), "Above 50%", " "), you would not need to write in order and its also easier to make since you can copy and pate the AND formula and make small edits.

[@JeffSu]

Great tip! Thanks for sharing!!

[@akshaynitinvaidya]

Goddd very helpful! thank you so much!

[@JeffSu]

You're very welcome 😁

[@zagfat]

Well presented video. Im interested in google sheet, but yet to understand why people use it for work. It is so basic.

[@JeffSu]

Some people (like myself) likes to keep things basic 😁

[@denisamokhvalov]

Jeff, I have a question. Is there a way to use conditional formatting for isemail and isurl functions? If false, then the cell is to be painted red.

[@JeffSu]

Yup, you would just need to add conditional formatting on top of the column/rows!

[@denisamokhvalov]

@@JeffSu Thanks, it works. We can even use this formulas directly without creating additional TRUE/FALSE cells. For example: Select range we want to check > Conditional formatting > Format rules: Custom formula is: ISEMAIL(firstcellinthelist).

[@JeffSu]

@@denisamokhvalov oh nice!! Thanks for letting us know Denis!

[@johnnyhooker4098]

As a nonprofit, we build a lot of budgets using foreign currency. However, it would be awesome if I could, by selecting the currency I wanted to view the budget in, to look at the same page recalculating the budget in USD. Our work around right now is to build a copy of the budget on a second tab and use a formula to recalculate the foreign currency budget in USD. Is there a simpler, perhaps more savvy way?

[@JeffSu]

I honestly feel like there's a way, for example change the formula to show the new currency depending on the currency symbol you input in a cell. But I don't know the formula off the top of my head 😅. Sorry!

[@tobias2081]

Look at Jeff responding to every single comment 🔥

[@JeffSu]

Oh heckkkk yes 😁

[@wowwoahwee]

oh no...I most certainly got to this video too early. lol! be back when at the least i know how to press enter without kicking myself out of typing mode , or if thats even possible. lmao good stuff. even i can follow . its just not my main focus atm. 5 STARS to Jeff! Make that 14 now buddy. lol

[@JeffSu]

Thank you Rory! 😁

[@amitshreechandan]

please make a video on Notion Workspace Setup for beginners

[@JeffSu]

Thanks for the idea Amit!

[@josephhetherwick715]

“Hello there.” was general kenobi’s line. ButGeneralGrievisesaidAhh,General Kenobi. Delimiter separate that. Ps. I love your videos. The practical content is worth my time, the humor keeps it enjoyable.- Sales Department. Lol, But no really.

[@JeffSu]

Hahahahahahahahahahaha good one, thank you Joseph!

[@alex-coelho]

why can't I like this twice!?

[@JeffSu]

You can show your support by liking all the other videos 😂

[@hanaazqazi6261]

banger of an intro

[@JeffSu]

Thank you Hanaaz!! :)

[@rudrajaiswara9851]

Hi jeff I just love your video I want to say that can u make a video on Microsoft Edge because recently i started noticing that my Google Chrome is crashing and I have planned to use Edge as my primary Browser thank u

[@JeffSu]

Oh you just reminded me I DO NEED to make a video on Edge! Thanks for the reminder!

[@rudrajaiswara9851]

Your welcome

[@hashedalnasef6593]

Thanks Jeff

[@JeffSu]

You're very welcome 😁

[@natancmacedo]

The functions: query and import html. And I sometimes use Google script as well

[@JeffSu]

Nice! thanks for sharing Natan! I (embarrassingly) do not know how to use Google script

[@natancmacedo]

@@JeffSu I'm a programmer, so... :-p we use a lot in the company I work on

[@JeffSu]

@@natancmacedo Time for me to check it out 😅

[@ruchapande892]

@jeff su jeffyyyyyy perfectly timed video.. I needed to learn these formulea.. Yet to watch the video but couldn't resist commenting 😅

[@JeffSu]

Hahaha well I hope you still liked it after watching it!

[@nadchallenge]

this is so cool video . Thanks..

[@JeffSu]

Glad to hear it!! Thank you!

[@nadchallenge]

@@JeffSu I am very excited to create a video like yours.. best quality and better presentation style..

[@AlexRamsbey]

Hey Jeff, Could you create a video on how I could import multiple ranges into a single list? This would be between the same sheet but append each range. (the goal here is to have a master list of tasks and each time something is added on one of the referenced ranges, it shows up in this master list.

[@JeffSu]

Not sure I understand the question Alex....

[@AlexRamsbey]

​@@JeffSu My goal is to combine several ranges into a master range, if that makes sense. This would be something like in SQL a JOIN statement (but I'm looking to join rows, not columns). I'm creating an audit document within Excel, and any recommended changes I have a range that I'm using for that information. So, ideally, at the end of all the sheets, I want to combine all the recommended changes into a master check list. Hopefully that makes sense in context?

[@akirakongouji]

Randomly stumpled upon this video, but two formulas I used jointly a LOT at work is query/importrange (probably more uncommon), and index/match/match for a matrix lookup.

[@JeffSu]

Nice! Thanks for sharing Tom! Well for what it's worth I'm glad you stumbled onto this video haha

[@dark-eye4727]

Amazing .. thank’s a Lot 😍

[@JeffSu]

You're very very welcome 😁

[@jorge-tutor]

Great examples! Is there any way to define a formula per column so if I add a new row in the middle automatically inherits the formulas?

[@JeffSu]

Hm.....that's a good question. I don't know to be honest. I usually just copy paste that formula down....

[@lpanebr]

If the column with date is an actual date it's better to use the month() and yeah() functions as they work regardless of the date format.

[@JeffSu]

That's a great point Luciano, thanks for sharing!

[@ZestyVA]

Off topic for your channel... what do you all do about antivirus for your windows computers? I'm a small business owner and I'm struggling with who to trust. @Jeff Su, I trust you! Do you have any recommendations?

[@JeffSu]

I believe Windows Defender does a pretty decent job now :)

[@ZestyVA]

@@JeffSu Thank you! You make my life so much easier! Many a video I have passed onto my contractors for professional development :()

[@astudio777]

How can I convert a quantity ordered into rows in google sheets? Ex: A2 = Item being ordered and B2 equals the quantity ordered I want to take the quantity in B2 and add that many rows.

[@JeffSu]

Not sure what you mean there... 😅

[@astudio777]

@@JeffSu Say I have an order come in for an item and they want quantity of (10). I want Google Sheets to automatically populate (10) lines(rows) with that same item that was ordered. Ex. Cell A2 is the item or product being ordered. B2 is going to be my quantity cell. When the quantity wanted is input into cell B2, I want it to automatically populate that many lines(rows).

[@chansterray]

Thanks Jeff!

[@JeffSu]

You're welcome Ray!

[@vaskard905]

excellent !

[@JeffSu]

Thanks Vaskar 😁

[@arpanmajumdar617]

Jeff is just one step short of using Google Sheets as a full-blown programming language.

[@JeffSu]

😂 I'll take that as a compliment!

[@arpanmajumdar617]

@@JeffSu Woahh didn't know I will actually get a reply. Big fan of you.

[@JeffSu]

@@arpanmajumdar617 Of course Arpan :)

[@billblanchette3604]

You did 1 digit for the splitting the month out, what happens with October or December?

[@JeffSu]

Yup I only realized that after the fact. Other viewers have already corrected me in the comments! 😁

[@Bolly811]

Thnx for the video!

[@JeffSu]

You're very welcome Bolly 😁

[@collingriff]

Great video! One that I find that I use a lot in my media job is the Query function to aggregate and sort different cuts of data. I find that it works particularly well in combination with and Importrange as I am typically working with large sets of data that includes more information that just what me and my team work on. For example, using some of the inputs you had in this video it could be a Query(Importrange(),"Select * Where Col1 contains 'ORGANIC'").

[@JeffSu]

Oh nice! I haven't really used QUERY() before so thank you for bringing that up! I should definitely experiment with it :)

[@rosidahbintiabdulrashidkpm5761]

is there a formula to write a sequence of list containing number with alphabets, eg 1A, 1B,1C ?

[@JeffSu]

Probably need to combine two columns/rows, one with 1,2,3,4 etc and another with A,B,C,D etc

[@lophamdac8377]

That great!

[@JeffSu]

Thank you 😁😁

[@willfs88]

Does ARRAYFORMULA make easier to loading a page full of formula?

[@JeffSu]

Technically it does but it depends on the use case! What are you trying to achieve?

[@collator]

It’s physically impossible to make a mistake but you can make a #VALUE!

[@JeffSu]

That's a good point! 😂, but at least (in my opinion), that's better than making a calculation error and NOT knowing haha

[@eschool9854]

HI man, can use a importrange funtion to each file? what i mean is when i write something to imported file's cell that should be appear in first file's cell. I ve already used "query and importrange functions but when i wrote something the formula stopped working and it wasnt shown in first file can u make a video to solve this problem?

[@JeffSu]

Sorry what do you mean by "import range function to each file"?

[@tjandrabtg]

how to make drop down link with hyperlink, for example , we have 4 sheets, Jan, feb, march, april, I want drop down clik, for example april click, then tab " April " is Open

[@JeffSu]

Don't think that's possible right now haha

[@dayashankarsuresh57]

Hey, I was wondering if you would make a skillshare class on excel 🤔🤔🤔

[@JeffSu]

That's actually a pretty good idea, thanks for sharing Dayashankar!

[@gnawlej9163]

I know it's a teaching example, but using LEFT with 1 character length to extract month won't work for October-December. Sheets does have a MONTH formula that would work though.

[@JeffSu]

Good point Drew!! Thank you so much for pointing that out!

[@jwalbanker]

Some help please!: I use google sheets to record daily sales. Sheets are manually renamed as dates and each sheet has columns of name, service, amount in cash, amount by card and total. So I make such 30 sheets each month. Is there a way/ formula/ script to compile all data to a master sheet which I can access at the end of each month? I am having a hard time doing this manually! Any help is much appreciated :)

[@JeffSu]

Hi Jwal - Without actually seeing what you're trying to do it's really hard for me to give you an answer. If my guess is correct though yes you should be able to run a macro, but I'm not sure what you're trying to achieve 😅

[@jwalbanker]

@@JeffSu Thanks for the quick reply! Is there a way where I can send a screenshot or a short vid?

[@projectmusichealsus]

I think I had to do a similar thing with compiling multiple constantly updated sheets from different Google forms. I needed a constantly updated aggregated Google sheet that was organized by date. Since the updates were happening contemporaneously, I couldn’t just use multiple import ranges. I ended up being able to use a mix of query and import range, which allowed me to query multiple importranges together. Happy to walk you through if you’re still looking. 😄

[@jwalbanker]

@@projectmusichealsus hey! I would love to know how you managed to do this! This would be saving me a lot of time and effort! Thanks

[@einarparker]

@@jwalbanker do you still need help?