User Profile
heylookitsme
Iron Contributor
Joined Apr 07, 2020
User Widgets
Recent Discussions
Re: A Little Help from my Friends
I like this idea. How would new items each month be added and accounted for? I guess I could create a lookup of new items and then create a macro that automatically imports the new items to a new table row in the master file. Then once items are copied and pasted into each month the formulas in the master would import the data but then I would have to be sure to copy and paste formulas as values to close the actuals each month. Which then technically, I could just continue what I was was doing. Automatically pulling in the data via power query and then I have to copy and paste new items (which I could create a macro to do this for me). Each month have an xlookup to import the data and when actuals are ready just copy and paste formulas as values. Which I could also create a macro to handle this as well.142Views0likes1CommentA Little Help from my Friends
I am an advanced (expert) in Excel. I've used it for over 20 years everyday and have thousands of education hours under my belt. I would like to get some ideas on how some of you would approach this complex situation I have. I have already created three different methods but each have too many draw backs or failure points. With that said, I'll try to be brief but detailed enough for your thoughts. I have a file for which I'll call the Master-File it has a list of parts, suppliers, prices by month with Forecast and Actual. Current and Future months are forecast, previous months are actuals. I have 6 people that send me, what I'll refer to as Sub-Files. These subfiles contain the same headers as the Master-File. In the Master-File previous months data cannot change. (Ex. If reporting was done today 7/14 Actuals for June would be imported as Actuals and the remaining July thru December would be Forecast updated with the latest forecast data, which is already included in the sub-files). Months are listed left to right in columns not rows. Also, those sub-files can contain new parts that are required to be imported into the Master-File. My first solution was to use Power Query and import the Sub-files each month from a folder into the Master-file. Problem with that was, data from previous months would change in some of the Sub-Files and would over-write the data. (This data can't change once locked in). My second solution was to utilize a Unique ID for each record where I could easily identify new parts first and import that data by manually copying and pasting the data. Then sort the data using a match formula so the records in the Sub-file matched the Master-file and then manually copy and paste in the Actual and Forecast data. Some data was not correctly matched, so I started using XLOOKUP to import the data in the Actual and Forecast. Then, copy and paste as values. With 6 different files the table would at times over-write the data in the Actual forecast fields causing errors. Even after turning off auto table formula update option. Plus, this was very time consuming. Now I am starting over. I have some other ideas but if two heads are better than one then many heads are even better. I would love to hear your thoughts on how you would go about handling this process.331Views0likes6CommentsRe: Shapes Moving Even When set to Do Not Move
Providing resolution in case anyone else runs into this issue. Before creating a macro think about how many users may be in your workbook at a time and where the file will be stored (Network, Teams, SharePoint, Etc...). If it is a shared file with many users in it at any given time it would be best not to use macros. Everyone might not have macros enabled and it may be IT restricted. If at all possible, use simple solutions first. In this case using Hyperlinks to guide users to other worksheets in the workbook provided the best solution. A quick search of the web will even show you how to make them look like buttons using formatting tricks.208Views0likes0CommentsRe: Shapes Moving Even When set to Do Not Move
I wonder if I am using the correct method for madness? What the macro is doing because there are many sheets 10 I am simply unhiding and selecting a sheet when the user selects the button. Then there is a BeforeClose of worksheet hide all sheets except 2. Since this workbook is shared by many I fear this will not work as I suspect and that it may be better to have all sheets open but use hyperlinks instead. I can see the macro getting confused when multi users open the file.124Views0likes1CommentShapes Moving Even When set to Do Not Move
I use Excel 365 Enterprise 4 edition. I created a file that is stored on a Teams site that contains shapes I use as buttons for my macros. Every time I open the file the shapes have moved and/or resized and I can't figure out how to prevent this. I have the shapes set to Don't move or size with cells set on each one of them. Users are not moving them. No one is playing any pranks, I wish they were it be a lot easier to fix. Anyone have any ideas?Solved154Views0likes2CommentsRe: Power Query INDEX Column
Your response prompted me to re-check my Table and I found that Rows 6208-8300 were not formatted as part of the Table. So, I resized the table so it would capture those as well. Unfortunately, I just assumed my Table included all items. Rookie mistake on my part. Thanks for the swift response.96Views1like0CommentsPower Query INDEX Column
I added a column for Index using Power Queries built in Index Column function and I placed it as the last item in steps. When I run the query there are 8,300 rows in my table however, the INDEX column populates numbers 1-6207 and then the rest are blank. Maybe I am not using the right method for what I am needing to do. I am just needing a column at the end of the query to number the rows. Further up in the steps before the INDEX Column step takes place, I do have a filter that takes place based on a cell's value that is passed to a table. I wouldn't think that would prevent the numbers from populating all the way down but who knows? Anyone have any ideas on how to make this work or perhaps a better way to go about this?Solved188Views0likes2CommentsPower Query by Default Excludes First Blank Column
I have researched this down the rabbit hole long enough without any answers. In Excel I created a Power Query Get Data from Sharepoint Folders using the Contents method. There are about 10 files where the data is in the same format, columns match sheets are all the same name, etc.... FYI, None of them are setup as Tables for reasons I won't go into. They just can't. When I import the files 5 of the files have data in column A while 5 do not. When I import the data the files that do not have data in column A Power Query is automatically removing those columns from those sheets. Which then screws up the column order when the data is appended and loaded to the table. How can I force Power Query to bring in Column A even if some files column A are blank?Solved374Views0likes4CommentsPower Query Find Text and Delete Anything After
Excel Power Query what would be the M Code for finding the String "NUL" and then remove any Text after the "NUL" String. I have several serial numbers for example like 1287JLPO-NUL-00-000-00000 HLQA-00-NUL-000-0000 I just need to delete any text after NUL. As you can see, I can't just replace - or 0 because those could be found prior to NUL. Thanks in advanced for your help and I will be sure to mark your answer when complete.Solved1.2KViews0likes4CommentsRe: Slow Excel Sheet - Even after everything's deleted
Solved my problem. I'll post in case it helps another user out there. Check to ensure you do not have too many objects, comments or shapes in your sheets. This can be caused by copy and paste procedures inadvertently pasting them many times over without you realizing it. Simply, select the Home option in Excel ribbon and use the drop down under Find and Select. Select selection panel and you will see to the right if you have a lot of items showing that need to be deleted.8.1KViews0likes1CommentSlow Excel Sheet - Even after everything's deleted
Okay, this one blows my mind. I have a sheet with a table that acts like it contains volatile formulas. It's very laggy, filtering is super slow, manual inputs are slow. Even after I have deleted everything on the sheet!!! All the things I tried below are after I deleted all data in the sheet. File size is only 8MB. First thing I checked was VBA and it does have a macro that runs a worksheet change event. This was all commented out and still runs like this. I checked Formula Names to see if there were any external connected workbooks, and there was not. I checked Queries & Connections and there are none. One strange thing I noticed is when I select any other sheets in the workbook it selects the sheet instantly and I can work quickly or as normal in the sheet without any hesitation or lag. When I go back to the problem sheet and click on it, it is slow to react. Any cell within that sheet that I select (non edit mode or edit mode) it lags behind about 2-3 seconds before selecting it. I do not have any other workbooks open and little to no other programs open. I have a laptop with high specs that could run circles around most laptops. I don't know what else to check that would potentially fix this issue. Re-building this workbook will take way too long, potentially days. Oh, I use MS 365 Enterprise 4 with the latest updates and I am on Current Channel using Win 10.Solved5.6KViews0likes2CommentsExcel Memory & Performance Limitation
My previous laptop (which had some great specs) I noticed that when Excel Memory Usage rose above 1,800 MB that it would start behaving badly and do really random weird stuff (Example: Randomly freeze for short periods of time, click in a cell and it does not recognize that you clicked on it, but the data is in the formula bar, and you can change it. These are just two off the top of my head, there are many other random things as well). Now I have a new laptop with amazing specs, and I am using Microsoft Office 365 Enterprise 4 64-Bit version with the latest updates installed. I am still noticing that Excel is having issues when the Excel program memory goes above 1,800 MB. What is the deal? Does Excel have a limit on how much memory it uses? I did check my options and I'm using all threads with no limits listed. Is there a way to increase this limitation, if in fact one does exist? My Lap specs are; AMD Ryzen 7 PRO 7840U 8 Cores, 16 Threads Ram Memory 32 GB Microsoft Win 10 Pro Lenovo ThinkPad T16 Gen 222KViews0likes1CommentRe: Latest Version File Save Errors when File is in .xlsb format
Thanks for the advice as it hope will help others. As for me I had already did steps 1-3 previous to this post. However, how do I report the issue directly to Microsoft? As for work-a-rounds I mentioned one in my post and have been changing what files I can from .xlsb to .xlsm. However, most of my files require they be in .xlsb format. Makes for easier transfer of macro enabled files and to reduce file sizes.2.7KViews0likes0CommentsLatest Version File Save Errors when File is in .xlsb format
I am using MS Office 365 E4 Version 2401 (Build 17231.20236) Current Channel. Ever since I received this latest update I am having issues saving .xlsb files. I can open them but then I get the unable to save workbook is corrupted, blah, blah, blah. To fix the issue I have to copy my work to another workbook (so I don't lose it). Close the file with error out without saving. Then open the file back up under Open and Repair. The file is repaired and works just fine again. Not sure if this is an issue the MS team has identified and are working on in the next revision, but I hope it is because it is really annoying.Solved3.4KViews0likes2Comments
Recent Blog Articles
No content to show