Видео

Hi @jtflypegasus, great question! Thanks for pointing this out. Here's how you can stay in the same cell even when the sheet is protected: Workaround Methods: 1] Press F2: While in the cell, press F2 to enter edit mode, make your changes, and then press ENTER. This will keep you in the same cell. 2] Use Ctrl+Enter: If you want to stay in the same cell while applying your changes, press Ctrl+Enter instead of just ENTER. 3] Adjust Protection Settings (if you have the password): Unprotect the sheet temporarily and then reapply protection with the 'Select locked cells' option unchecked. This will restrict movement to other cells. VBA Solution (Advanced Option as requested): You can add a simple VBA code like this: vba Copy code Private Sub Worksheet_Change(ByVal Target As Range) Application.OnKey "~", "StayInSameCell" End Sub Sub StayInSameCell() ActiveCell.Select End Sub Save the workbook as a macro-enabled file (.xlsm) and enable macros when using it. These should solve your issue! Also, I’ll be creating a video on this topic very soon and will notify you once it’s published. Thanks for the challenge-I’m excited to dive deeper into this! Stay tuned! 😊

@@realmehranvahedi hi again. first of all, i appreciate your responses. thank you. the suggestion [3] works, but, i want to be able to keep the locked cells as they are so they cannot be inadvertently messed up by the user. I did try your suggested VBAs but it did not work for me. I think this is an Excel bug, in all honesty. Simply because it is not staying true from the "FILE/OPTION/ADVANCED uncheck the cursor movement". That's really too bad. This is an excel bug and it should be pointed out to the MSOffice people. Thank you. Because you cared in my comment it deserves a subscription to your channel. It's all good.

You're very welcome! 😊 Merry Christmas and best wishes to you and your loved ones this holiday season. 🎄 I'm so glad you found the video helpful-feel free to reach out if you have any questions or ideas for future topics. Have a great day! 🎁✨

Hi @JackSchlaack-i4o, Thank you for watching my video, and I’m glad you found it helpful! 😊 Regarding the error you're encountering: The issue might be related to permissions or file-sharing restrictions that occur when working with a shared file. Here's how you can troubleshoot and potentially resolve the problem: 1] File Path Permissions: Ensure that the temporary file path (TempFilePath) is valid and accessible. If you're working in a shared environment, the Environ$("temp") directory might not be suitable. Instead, you can specify a different temporary directory, such as one on your local drive (e.g., C:\Temp\). 2] File Name Conflicts: In shared files, it's possible that another user has already created a file with the same name in the same location. To avoid this, append a timestamp to the file name: TempFilePath = Environ$("temp") & "\" & SourceWorksheet.Name & "_" & Format(Now, "yyyyMMdd_HHmmss") & ".xlsx" 3 ] File Sharing Settings: If the file is being used in a shared mode, Excel may restrict certain operations like saving. To resolve this, you could temporarily turn off shared mode or ensure the workbook isn’t locked by another user during the process. Try these steps and let me know if they help! If you're still facing issues, feel free to share more details, and I'll do my best to assist further. 😊 Cheers, Mehran

@@realmehranvahedi Thanks so much for your advice!

@@JackSchlaack-i4o You're very welcome. feel free to leave any other comments or questions you have. Wishing you a happy new year!

Hi @ArslanShakir-w1f, thanks for your comment! Are you sure you’re following all the steps as shown in the video? Specifically, make sure that in step 2, you’re selecting and highlighting the destination range properly before applying the Ctrl + Enter command. If you’ve done everything as instructed, you should get the same results. If you’re working with a very large dataset, I recommend trying it first on a smaller dataset to help diagnose the issue. Additionally, you might find my latest video on solving this problem with VBA helpful: ruclips.net/video/LuoBVYEnQGo/видео.html. It’s a great alternative for handling such scenarios. Feel free to let me know if this resolves the issue or if you have any further questions. I’d love to hear if you get it working!

You're very welcome, @maritimegardening4887! I'm so glad the video helped solve your issue 😊. If you have any other Excel challenges or topics you'd like me to cover, feel free to leave a comment and share your thoughts. Thanks for watching and supporting the channel!

Glad you found it useful. Keep watching for even more Excel Tips and tricks!

Hi @ShahinAlom-u4t, Thank you so much for the love! I'm really glad you enjoyed the video. Stay tuned for more! 😊

You're very welcome. I'm glad you found the video useful.Thanks for watching and let me know if you have any questions or video suggestions!

Thank you so much, @rickyvalent2742! I'm really glad to hear the video helped with your thesis data. Best of luck with your final submission, and if you have any more questions, feel free to ask. God bless you too!

Hello @rodgerskithi8600, thanks for reaching out! I've checked the audio levels, and they seem to be quite clear on various devices. You might find adjusting the volume on your device helpful. Feel free to post any other questions and comments related to the video. I appreciate your engagement!

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?

Hi @m3s69ll, Great question! You’re correct that both B2 and B3 would return the same column number, 2, because they are both in the second column. The choice of B2 over B3 in this context doesn't impact the formula’s functionality, as either will correctly return the column number needed for the COLUMN function within our formula setup. The specific cell reference (B2 vs. B3) is often chosen for consistency or based on the starting point of the data range in examples. If there's a specific range or header row highlighted as the reference point in explanations or setups, that's generally the one used to maintain clarity. Thanks for your keen observation, and please keep the questions coming if you have more! Cheers, Mehran

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: ruclips.net/video/4_ocZNEfZig/видео.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 @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: ruclips.net/video/u-dl_bJcqKk/видео.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: ruclips.net/video/EujOooztXRI/видео.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))) 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: ruclips.net/video/e71IQnaaBV4/видео.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 : ruclips.net/video/CnHDyEn6Bco/видео.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: ruclips.net/video/LuoBVYEnQGo/видео.html If you prefer not to use VBA, these two videos should help clarify and provide alternative solutions: Video 1: ruclips.net/video/hC1BQ5ZsKWw/видео.html Video 2: ruclips.net/video/D2xFIhaQ56w/видео.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: ruclips.net/video/5_njoAVHqxg/видео.htmlsi=BZNdNTiqlrbOW8an Video 2: ruclips.net/video/hC1BQ5ZsKWw/видео.htmlsi=4KKuXgcIepJTNA2J Video 3: ruclips.net/video/LuoBVYEnQGo/видео.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: ruclips.net/video/5_njoAVHqxg/видео.htmlsi=BZNdNTiqlrbOW8an Video 2: ruclips.net/video/hC1BQ5ZsKWw/видео.htmlsi=4KKuXgcIepJTNA2J Video 3: ruclips.net/video/LuoBVYEnQGo/видео.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!