วีดีโอ

Hi @m3s69ll, Thank you for your comment and I'm glad you found the video helpful! To clarify why we use COLUMN(B2) in the formula rather than COLUMN(C2): The COLUMN(B2) function returns the column number of cell B2, which is 2. This is used in the VLOOKUP formula to dynamically select the column index for the lookup value. In the screenshot and the explanation in the video, COLUMN(B2) is used in the VLOOKUP formula within cell K3 to automatically update the column index parameter. This allows the formula to remain flexible and automatically adjust if columns are added or deleted in the data range. If you want to fetch the "Revenue" data specifically, which is indeed in column C as you noted, we need to ensure that the formula uses COLUMN(C2). This would be COLUMN(C2)-COLUMN($B$2)+1 to correctly point to the third column where the Revenue data starts. Hope this clears up any confusion! Thanks again for the comment and feel free to leave any other comments, questions and also video suggestions. Cheers, Mehran

@ I see why you use B instead of C now. But does it matter if it’s B2 or B3 since they both return 2?

Thanks, @araparts! I'm really glad you found the video helpful. If there are any other Excel topics you'd like me to explore, feel free to drop your suggestions here! 👍

Thrilled to hear that, @bobharris2327! Thanks for watching, and I'm glad I could help make things easier for you. 😊

Thanks a lot, @quintenmellish4424! I'm glad you found the video helpful. To see how to do the same thing while also suppressing zeros, check out my latest video here: th-cam.com/video/4_ocZNEfZig/w-d-xo.htmlsi=Hse6MjVEEwzWTnL5 Hope it helps! 😊📊

Thanks, @MickeyLeeBukowski! Just trying to keep the legend alive, one VLOOKUP at a time! 😎"

Thrilled to hear it, @WhosThis-j7z! Glad the chart is holding strong-no zeros on my watch! 🚫📉

Hey @ianspencer787! Great question! To use a value from your workbook as the subject line in the email, you can modify the VBA code to read the cell value and set it as the subject. Here's how you can do it: .Subject = SourceWorksheet.Range("A2").Value Just replace the .Subject = "Subject Here" line in the provided code with the line above. This will set the subject of the email to whatever value is in cell A2 of your active worksheet. Keep rocking those Excel skills! 🚀📊✉

Hi @yuanyuan5319, I'm glad you found the video helpful! Yes, the custom format you mentioned is a great way to display percentages while hiding zeros. To set a custom format as the default in Excel, unfortunately, Excel doesn’t directly allow you to set a custom number format as the default for all new workbooks. However, you can create a template with the custom format already applied to cells, and then use this template for future workbooks. Here’s how you can do it: STEP 1] Open a new workbook and apply your custom format (like 0%;-0;;@) to the cells you need. STEP 2] Save this workbook as an Excel Template (*.xltx). STEP 3] When you need a new workbook, start by opening this template. This way, every new file created from the template will have your preferred formatting in place from the start. Hope this helps, and keep the questions coming! Cheers, Mehran

Hi @JosipMilošević, Thanks for reaching out! In this video, the only code used is #"", which is just three characters. I'm wondering if perhaps this comment was intended for a different video with more complex code? Also, I invite you to check out my most recent video on this topic, which you might find helpful: th-cam.com/video/EujOooztXRI/w-d-xo.html. Feel free to comment and ask more questions anytime! Cheers, Mehran

Hi @guyfalleyn4249, Thanks for your kind words and for watching the video! For summing the FTE values for the first occurrence of each worker ID using formulas only, you can utilize an array formula that combines SUMPRODUCT, UNIQUE, and FILTER. This can help you achieve what you're looking for without needing to refresh pivot tables or use VBA. Here’s a basic outline of how the formula might look: =SUMPRODUCT(--(A2:A100=UNIQUE(FILTER(A2:A100, B2:B100="Your Condition"))), C2:C100) This formula assumes your worker IDs are in A2:A100, your FTE% are in C2:C100, and you have a specific condition in B2:B100. Adjust the ranges and conditions to fit your data. I hope this helps! If you have more questions or need further clarification, feel free to ask. Cheers, Mehran

Hi @GiSquared2, Wow, that's fantastic to hear! I'm so glad the UNIQUE formula was exactly what you needed and that it worked out perfectly for you. It's great to know it's helping you keep track of your project tasks. If you have any more questions or need additional Excel tips, just let me know in the comments. Thanks for subscribing and for your wonderful feedback! Keep Excel-ing! 😄 Best, Mehran

Hi @MuhammadKashif-kd9vk, Thanks for reaching out! If the formula isn't working for you, you might want to check out another approach I've discussed in my latest video. Here’s the link for you: th-cam.com/video/u-dl_bJcqKk/w-d-xo.html It offers a different method that might just do the trick. If you have any other questions or need a more personalized consultation, feel free to book a paid consultation with me via Topmate.io. Let me know how it goes! Best, Mehran

Hello @tweedandbriar, first of all, thank you for your comment! You’re absolutely right; I did create a new dataset to demonstrate how to accomplish this task using a formula. Once the new data is generated, it is indeed possible to paste it over the old dataset, effectively replacing the original values (you might then choose to remove the formula column). This approach ensures precision in replacing only the desired characters without affecting other parts of the data, which might happen with the find and replace method that I opted not to use in this tutorial. I hope this clarifies the process. If you know of any alternative more effective methods or have further questions, please feel free to share them in the comments. Your insights are invaluable! Thanks again for watching and for your insightful comment.

Hey @basharyonan9191, your comment just made my day! I'm thrilled to hear that my video was exactly what you were looking for and helped you solve the problem. Thanks so much for letting me know-it really keeps me motivated to keep creating helpful content. If you have any more questions or there's another topic you'd like me to cover, feel free to drop a suggestion. Happy to help! 🌟

Hello @danrudd1521, It sounds like there might be a small issue with how the formula is interpreting the ROW() function in your sheet. The formula you are using should increment by one each row to generate the sequence AA, AB, AC, and so on. Make sure you start this formula in the first row of your Excel sheet (A1). If you start in a different row, you’ll need to adjust the ROW(A1) part of the formula accordingly. For example, if you start in A2, change it to ROW(A2), and so on. Double-check that the formula is copied correctly and fully. It should look like this: =LEFT(ADDRESS(1,ROW(A1),4,1), (ROW(A1)>26)+1) Ensure there are no extra spaces or characters. After entering the formula in the first cell, drag the fill handle (the small square at the bottom right of the cell) down to fill the cells below. This action should automatically adjust the formula for each row. If these steps still don't resolve the issue, it could be helpful to check if there are any settings or formats applied to your cells that might be affecting the output. I hope this helps! Let me know if you continue to have issues, and we can troubleshoot further.

Hi @inannibras391, thanks for your question! It sounds like you're looking to perform a left join using Fuzzy Lookup, where Table 1 with fewer companies is your left table and Table 2 is your right table. To do this, set up your Fuzzy Lookup to match the company names from both tables. Make sure to include the 'Opening Balance' column from Table 1 in the output columns, so it transfers to Table 2. For companies in Table 2 that don’t find a match in Table 1, the 'Opening Balance' column will remain blank, or you can set it to a default value like $0 or 'Not Available'. If you need more detailed steps or a tutorial on setting up the Fuzzy Lookup tool, let me know! Also, for detailed guidance on specific cases, consider booking a personalized session with me at topmate.io/mehran_vahedi. Looking forward to helping you further!" This version offers comprehensive help and directs the viewer to your booking page for further assistance.

Hi @DassHunters, definitely! In the video, I discussed using a dynamic range with the OFFSET and COUNTA functions to make charts update automatically as new data is added. Here’s a quick rundown: You set up a named range in Excel using the OFFSET formula like this: =OFFSET(Sheet1!A2,0,0,COUNTA(Sheet1!A:A)-1). This formula creates a range that starts from a specific cell (like A2), counts all non-empty cells in a column, and adjusts the range size accordingly. To use this in your chart, you link the chart's data series to the named range you defined. Now, each time you add new entries to your column, the chart recognizes this through the named range and automatically includes these new data points. This keeps your chart updated without manually changing the data range. It's a neat way to have your charts grow with your data!

Hi @meesterkoen1018, great question! Yes, it's definitely possible to use a second column with different values to rank numbers that have the same value in the first column, similar to how you might use a filter. In Excel, this isn't straightforward but can be achieved by creating a unique identifier that combines both ranking criteria. For example, you can create a helper column where you combine the primary rank values (let's say from sales quantities in column D) with a secondary metric (like dates or profit in column E). A simple way to do this could be by using a formula like this which you will put in column F : =FLOOR(D4, 1) + (E4/MAX(E$4:E$15)). This formula ensures that the primary column (D) significantly influences the rank, with the secondary column (E) only coming into play to break ties. After setting up your helper column, you can then rank these composite values using: =RANK(F4, F$4:F$15, 0). This setup will rank your primary metric and use the secondary metric to break any ties, ensuring a precise ranking that reflects both criteria. Hope this clears it up and helps you with your data analysis!

Hello @HanyAlfy, thank you so much for your kind words and for taking the time to comment! I really appreciate it. Please stay tuned for more helpful Excel tips and tricks-I have lots of great content coming soon that you won't want to miss!

Hi @echoesofhonor, great question! When a milestone is paid and the funds are moved outside of Upwork, they are no longer in Upwork's system. If there's a dispute and the client pays the arbitration fee but you don't, the client automatically wins the dispute. Upwork might then issue the refund to the client first and subsequently seek to recover the amount from you, potentially billing the payment method you have on file. This could include potential account restrictions or even suspension if you refuse to participate in the arbitration and also fail to refund the client. Conversely, if only you pay the arbitration fee and the client doesn’t, you'll automatically win the dispute. The arbitration fee will be returned to you, and you'll retain the amount claimed by the client. It's important to handle disputes thoughtfully as refusing arbitration could lead to further actions from Upwork, including potential account restrictions or financial liabilities. Always engage in the resolution process to find a fair outcome for both parties. Please note that I am not a legal expert, and this information is based on general experiences and understanding of Upwork's policies. It's a good idea to consult directly with the Upwork dispute team for specific advice. Also, feel free to ask more questions here-others in similar situations might benefit from the discussion as well. Thanks again for your comment, and I hope this helps!

Thanks a lot, @christopherfunakoshi1272! I’m really glad you enjoyed the video. Stay tuned for more content coming soon! 😊

@andreasmaier7013 Thank you so much for your kind words! I'm thrilled to hear you found the explanation helpful. Stay tuned for more cool Excel tips and tricks-I've got plenty more to share that I think you’ll find useful. Appreciate your support! 😊📊

Hey @cyberburnzy, thanks for sharing your approach! It's a great tip, and I'm excited to announce that I'll be creating a new video featuring it very soon. Keep an eye out! 😊👍

Hi @cyberburnzy, I wanted to personally thank you for your valuable suggestion on enhancing Excel charts by hiding zeroes while keeping crucial decimal points. Inspired by your tip, I’ve created a video that dives into this technique to help others in the community make their data look clean and professional. You can check out the video here: th-cam.com/video/EujOooztXRI/w-d-xo.html Thank you for sharing your insights with the community! Your engagement helps us create more helpful content and keep delivering high-quality Excel tips to our community. If you have any more thoughts or suggestions, please feel free to share them. Thanks again for your contribution, and keep excelling! Mehran

Hello @angelodeocampo3643, Thank you so much for your question! It's a great topic that many find challenging. I'm thrilled to share a solution that achieves exactly what you're asking for using an array formula in Excel for Office 365. Here's the formula we can use: =BYROW(A2:A17, LAMBDA(row, IF(COUNTIF(A$2:row, row) > 1, "", row))) Sure, here’s a crafted response for your TH-cam commenter: Hello @angelodeocampo3643, Thank you so much for your question! It's a great topic that many find challenging. I'm thrilled to share a solution that achieves exactly what you're asking for using an array formula in Excel for Office 365. Here's the formula we can use: =BYROW(A2:A17, LAMBDA(row, IF(COUNTIF(A$2:row, row) > 1, "", row))) This formula uses BYROW to process each row individually in the range A2 The LAMBDA function takes each row and checks how many times its value has appeared up to that point using COUNTIF. If a value appears more than once, it returns an empty string for that row, ensuring only the first occurrence is displayed and subsequent ones are left blank. This is an array formula, so just enter it in the first cell (e.g., C2), and it will automatically handle the entire column dynamically-no need to drag down! If you have any more questions or need further explanation, feel free to ask. Your question has inspired me to create a new video explaining this solution in more detail, so stay tuned for that! Thanks again for bringing up such a valuable topic. You're the motivation behind this upcoming video, and I'm excited to delve deeper into it! Best regards, Mehran

Hello @roxanachichi, Thank you for this very interesting question! I'm glad you brought this up. I've developed a formula that continues the sequence after "ZZ" and goes into "AAA", "AAB", etc. Here’s the formula you can use: =IF(ROW(A1)-1<26, CHAR(65+ROW(A1)-1), IF(ROW(A1)-1<702, CHAR(65+INT((ROW(A1)-1)/26)-1) & CHAR(65+MOD(ROW(A1)-1,26)), CHAR(65+INT((ROW(A1)-1)/676)-1) & CHAR(65+MOD(INT((ROW(A1)-1)/26)-1,26)) & CHAR(65+MOD(ROW(A1)-1,26)) ) ) Just place this in the first cell of a column in Excel and drag down to extend the sequence as needed. This setup will reset at "AAA" after "ZZ". I'm also planning to create another video on this topic to explore it further. Feel free to ask any more questions you might have, and stay tuned!

Hello @roxanachichi, As promised, I've gone ahead and created the video detailing how we can extend the alphabetical sequence beyond "ZZ" all the way to "ZZZ" and beyond! I hope this tutorial answers your question in depth and adds value to your Excel skills. 🌟 You can watch the video here: th-cam.com/video/e71IQnaaBV4/w-d-xo.htmlsi=CLM03HqnNY-a502a Thank you for sparking the idea for this video with your question. Your engagement truly helps shape our content. Feel free to suggest more topics or ask further questions. I'm here to help! Looking forward to hearing your thoughts on the video! #ExcelTips #DataSkills

Thanks so much for your kind words, Jeffrey! I’m thrilled to hear you enjoyed the video. Stick around for more tips and tutorials just like this one. And if there's a specific topic or challenge you're facing that you'd like me to cover, don't hesitate to let me know! Keep watching and learning with us! 🌟

Hi @jeffreyburnwood8084 , Thank you very much for reaching out and for the interesting question! I've crafted a formula that should precisely meet your requirements for finding the last value in a column right before any empty cells. You can use the INDEX MATCH formula below to get exactly what you are seeking. Here it is: =INDEX(B:B, MATCH(TRUE, INDEX(ISBLANK(B:B), 0, 1), 0) - 1) Please try this solution in your Excel sheet and let me know how it works for you. Your feedback helps improve the content I provide. Also, feel free to share any other questions or comments you might have. Looking forward to your response! Best regards, Mehran

@@realmehranvahedi hello! It worked flawlessly! Thank you very much! I was using Index too, but somehow it was always showing the previous entry, and if a name was added after the blank, it would show the name on the previous 3 cells and so on, lol. Again, thank you very much!

@@jeffreyburnwood8084 Hi, I'm so glad to hear that it worked flawlessly for you! 😊 Thank you for your kind words and for sharing your experience. It’s great to know that the solution helped you out. Your feedback and interesting questions have given me some great ideas for future content. I’m planning to create and publish a new video on this topic soon, so stay tuned for more detailed insights! Thanks again, and if you have any more questions or need further assistance, feel free to reach out! Best, Mehran

@@realmehranvahedi thank you so much for your kind words and for the time you took! I’ll be on the lookout for your content! Take care!

Hi @@jeffreyburnwood8084, as promised I have created and published the aforementioned video demonstrating how to use the INDEX MATCH formula in order to find the last value in a column before the first blank cell. Check it out via the link below and let me know what you think and as always feel free to post your questions and comments : th-cam.com/video/CnHDyEn6Bco/w-d-xo.htmlsi=qAx26wvMxsWmO8Jg

Hello @mudassirahmedi5617, Thanks for reaching out with your question! It sounds like you’re encountering a common issue where the copy and paste areas do not match. I've covered solutions to this problem in my recent videos. Particularly, in my latest video posted two weeks ago, I demonstrate how to handle this using VBA. You can find it here: th-cam.com/video/LuoBVYEnQGo/w-d-xo.html If you prefer not to use VBA, these two videos should help clarify and provide alternative solutions: Video 1: th-cam.com/video/hC1BQ5ZsKWw/w-d-xo.html Video 2: th-cam.com/video/D2xFIhaQ56w/w-d-xo.html I’m confident these videos will help resolve the error you're experiencing. Please let me know how it goes! And don’t hesitate to post any other questions or comments you might have, whether it's about this issue or any other topic. Your feedback is invaluable and helps improve our community. Cheers, Mehran

Hello @emilieguillon30, Thank you so much for your question and for watching the video! To specifically address your query about pasting "ABCD" instead of "ABGH" in specific lines, I recommend checking out these videos where I cover the exact topic you're interested in. These videos indeed provide the tips and steps you need: Video 1: th-cam.com/video/5_njoAVHqxg/w-d-xo.htmlsi=BZNdNTiqlrbOW8an Video 2: th-cam.com/video/hC1BQ5ZsKWw/w-d-xo.htmlsi=4KKuXgcIepJTNA2J Video 3: th-cam.com/video/LuoBVYEnQGo/w-d-xo.htmlsi=4H8lepmhg7XDzoNz I'm confident these will address the issue you’re facing. Please keep the comments coming and feel free to ask any more questions you might have. Your engagement is invaluable and inspires me to continue providing helpful content! Cheers, Mehran

@josnooy6844 Thanks for your comment! I'm glad you enjoyed the video. Feel free to post any further questions or comments you might have-I look forward to hearing more from you!

Hi @MichelleCrecelius , Thanks for reaching out with your question. It sounds like you might be experiencing an issue with how Excel handles the 'Paste Special' function when dealing with data copied from another file. Make sure that after you select "Visible cells only" using the "Go To Special" dialog, you're copying the cells you want to paste into. Sometimes, Excel can default to pasting only the first copied cell across all selected visible cells if not done correctly. Also, I recommend checking out my latest videos on this very same topic for more tips and detailed steps that might help resolve your issue: Video 1: th-cam.com/video/5_njoAVHqxg/w-d-xo.htmlsi=BZNdNTiqlrbOW8an Video 2: th-cam.com/video/hC1BQ5ZsKWw/w-d-xo.htmlsi=4KKuXgcIepJTNA2J Video 3: th-cam.com/video/LuoBVYEnQGo/w-d-xo.htmlsi=4H8lepmhg7XDzoNz These should give you more insight and possibly address the issue you're facing. Feel free to post any further questions you might have! Cheers, Mehran

Hi @TrongYangLim , Thank you so much for your kind words! I'm thrilled to hear that you found the video helpful. If you have any questions or need further clarification on any points, please don't hesitate to ask. Looking forward to hearing from you again!

@@realmehranvahedi 🥰

Hi @kubikrube784 Thank you so much for your kind words! It truly means a lot to me. I'm glad you found the sessions effective. Feel free to share any more thoughts or questions you have in future comments-I look forward to hearing from you!

There are strategies that could be put in place for solid gains regardless of economy situation, but such execution is usually carried out by an investment specialist

I've been in touch with a financial analyst ever since I started investing. Knowing today's culture The challenge is knowing when to purchase or sell when investing in trending stocks, which is pretty simple. On my portfolio, which has grown over $900k in a little over a year, my adviser chooses entry and exit orders

Mind if I ask you to recommend this particular coach you using their service? Seems you've figured it all out.

ANGELA LYNN SCHILLING' is her name. She is regarded as a genius in her area and works for Empower Financial Services. She’s quite known in her field, look-her up.

Thank you for this tip. It was easy to find your coach. Did my due diligence on her before scheduling a phone call with her. She seems proficient considering her resume.

Hello @raymondlew7 , I invite you to watch my most recent video on this same topic. I think it will answer your question. you can watch it via the link below. If you have any more questions after watching, please don't hesitate to ask! th-cam.com/video/LuoBVYEnQGo/w-d-xo.html

You're very welcome! I'm glad you found it useful!

Hi @suzannecadden9044, I’m glad to hear the video was helpful! For your scenario where you want to filter with multiple criteria using AND, and ensure the unique list does not spill across multiple columns, you’ve got the right idea, but let’s tweak your formula a bit to make sure it works as intended. You can simplify the logical tests by using the OR function inside your FILTER function. Here's how you can adjust it: =UNIQUE(FILTER(Projects!$A$1:$A$258, (Projects!$F$1:$F$258="WA") * (OR(Projects!$I$1:$I$258="1401", Projects!$J$1:$J$258="1401", Projects!$L$1:$L$258="1401", Projects!$N$1:$N$258="1401")))) This version uses OR to check if "1401" appears in any of the specified columns while still meeting the first criterion that the value in column F must be "WA". The UNIQUE function then ensures that only unique values from column A are returned based on these conditions. Feel free to try this out and let me know if it helps or if you need further assistance!

thank you so much but that didn't work. It gives me a list of all projects in WA but does not narrow that list by the ones that have 1401 in any of those columns referred to in the OR part of the formula. =UNIQUE(FILTER(Projects!$A$1:$A$258,(Projects!$F$1:$F$258="WA")*(OR(Projects!$I$1:$I$258="1401",Projects!$J$1:$J$258="1401",Projects!$L$1:$L$258="1401",Projects!$N$1:$N$258="1401"))))

Hi @suzannecadden9044, Thank you for trying out the formula and for your feedback. Let's address the issue you're facing. The OR function in Excel formulas doesn't work as expected within array operations like FILTER because it returns a single TRUE or FALSE rather than an array of TRUE/FALSE values needed here. We need to adjust the approach to use a combination that correctly evaluates each row individually. Try this revised version of the formula: =UNIQUE(FILTER(Projects!$A$1:$A$258, (Projects!$F$1:$F$258="WA") * ((Projects!$I$1:$I$258="1401") + (Projects!$J$1:$J$258="1401") + (Projects!$L$1:$L$258="1401") + (Projects!$N$1:$N$258="1401")>0))) In this formula, we replace OR with an addition of logical tests (condition1 + condition2 + ... > 0), where each condition checks for "1401" in the respective columns. This approach ensures that for each row, if any column contains "1401", it returns TRUE for that row. The final result is filtered by "WA" in column F and then unique values are extracted from column A. Please test this updated formula and let me know how it goes!

@@realmehranvahedi This formula is great! Thanks for sharing your knowledge on this

Hi @wellingtonpassos9510, I'm thrilled to hear that the formula worked well for you! If you have any more questions or need further assistance with Excel, feel free to reach out. Happy to help anytime! Cheers, @realmehranvahedi

Hi @rudranseattai8653, Thank you for watching the video! To generate sequences like AAAA, AAAB, etc., you can use a similar approach but extend the formula to handle four characters. Here’s a quick way to do it: STEP 1] In cell A1, enter the starting sequence "AAAA". STEP 2] In cell A2, use the following formula: =IF(RIGHT(A1,1)="Z", IF(RIGHT(A1,2)="ZZ", IF(RIGHT(A1,3)="ZZZ", CHAR(CODE(LEFT(A1,1))+1)&"AAA", LEFT(A1,1)&CHAR(CODE(MID(A1,2,1))+1)&"AA"), LEFT(A1,1)&MID(A1,2,2)&CHAR(CODE(RIGHT(A1,1))+1)), LEFT(A1,1)&MID(A1,2,2)&CHAR(CODE(RIGHT(A1,1))+1)) STEP 3] Drag the formula down to continue the sequence as far as you need. I hope this helps! If you need further assistance, feel free to ask. Thanks again for viewing! Best regards, Mehran