•2 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
An auto-updating sheet based on another sheet's data?
Our take
Moving can be a daunting task, especially when it comes to organizing your belongings. Imagine a streamlined system where your inventory automatically updates as you sort items into boxes. With an auto-updating sheet, you can have one master list (Sheet 1) that tracks your items and their designated boxes, while a second sheet (Sheet 2) dynamically displays each box’s contents. As you make changes, the items seamlessly shift between columns, keeping your organization intact. Ready to transform your moving process?
What I'm requesting is quite convoluted, I'll admit, but this is what I'd like to do for a spreadsheet of my belongings to organize during a big move:
I'd like to have one sheet (Sheet 1) that has a multiple columns with different lists of items (electronics, figures, etc), and next to these columns another column that dictates which box that item will be inside (box MG01, box CW01, etc). The second sheet (Sheet 2) will have all the boxes in columns, and each box column will look at Sheet 1 and copy down only the items that have the corresponding box.
If I update which box the item is in in Sheet 1, it just disappears from the previous box column in Sheet 2 and reappears in the column for the updated box. The box columns in Sheet 2 get rearranged so there's no randomly empty cells in them
Example sheet (in Google Sheets because it's easier to share but if the solution uses an Excel-only tool/feature I can switch to my Excel file)
I know I might be asking for a lot, but if there's a script or an extra sheet that needs to be created for organization purposes I'm ok with adding that step.
Thank you all!
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Change contents of cells based on a drop downSo, I'm trying to generate a spreadsheet to make the forms we use in the office to issue keys. When people are given an apartment, we have a sheet with all the key numbers on it, and then they sign for it. At the moment, we've got 22 different PDFs and Word documents, and it doesn't work. So, what I want to do is this. I've built the template in Excel and created a Reference sheet which will have all the numbers in it. What I am trying to do is set it up so that when you use the Apartment number from a dropdown, it populates with all the key numbers. This sounds like it should be easy to do, but my Excel brain is broken. Advice would be much appreciated! submitted by /u/Hot_Syrup_1774 [link] [comments]
- Keeping a running total of data from one sheet, in anotherHi all. I'm after some help, or pointers as I couldn't think of the correct search terms to find out what I want to do. I have an Excel (M365) spreadsheet (sheet 1) which currently has about 1500 lines of data on it, with another 100 or so being added every month. On each line, there are 30 different bits of data. Most of this data will never change once it has been added, but there are about 6 fields on each line which MUST be updated every 4 months due to expiry. Sadly, due to the data source there is no way to do this automatically or via an API, so I need to check it manually. I also need to keep a running tally of some of the data that is in the sheet. For each line, the main fields that I'm interested in are the manufacturer (1 of 67), the model number (unlimited to a certain extent), serial number, two different locations (both 1 of 48), and a few others where the criteria is effectively yes or no There will also be a not known option for each. Due to the data, there is also some duplication which is required and we don't want the previous record to be overwritten. Sometimes there will be two records relating to the same item, sometimes more. For instance, we will have a record from the first occasion an item is seen, and then duplicates for every other occasion. Most of the data on the duplicate records will be the same but the initial date of entry will be different, along with a reference number. On another sheet (sheet 2) within the same workbook, I have a summary of the data, all of which is manually calculated. It includes the total number of records, the number of unique records, the number of records which are duplicates (i.e. the serial number appears twice or more), the number of items that have a particular result in one of the fields (ie. field yes/no/unknown) I also have the manufacturer details and the number of records linked to them. I want to extend this to the location details and number of records linked to them, so that I can say there are this number of records for this manufacturer, or this number of records in that location. Calculating all the stuff on the summary page is time consuming. What would be the correct formulas to use, so that when I put a new record on sheet 1 (or update one of the records), it automatically updates sheet 2, or what functions should I be looking for? I haven't got a drop down/data validation box for the manufacturers, but I could do this as I can't imagine we are likely to add any options to them. Likewise for the locations, as there will never be more than the 48 locations. This was confusing enough for me to type out, so hopefully it makes sense to someone else. I can possibly create a sterile copy of the spreadsheet if it helps submitted by /u/d4nfe [link] [comments]
- One master list to auto update several other listsHello! I’ve been looking all over but haven’t found anything quite like what I need. I’m hoping you all can help. Let me try to summarize. Ideally I need to have a master sheet with all my data that automatically updates several external sheets with specific data from within the master. They also need to be able to add and delete automatically when the master is updated. For example I have a table of account numbers, their amounts, rates, related entities, etc on the master sheet. I’ll also need spreadsheets that only shows account information for Entity A, another for Entity B, and so on. Entity A and B may have some of the same account information, but not all. I know I can reference the master with a formula, but if new accounts are added, I’ll have to manually update each sheet. I’d like to avoid that. I looked into Power Query, but I don’t need it to combine multiple sheets or manipulate any data. Pivot tables also don’t seem to be working for this. TIA. submitted by /u/Spotted_Gorgonzola [link] [comments]
- Need some assistance easily organizing what is becoming a rather large spreadsheethttps://i.ibb.co/N6kN1bwz/sample.jpg This is an example of a worksheet I am building on Google Sheets. List of recipes I've made and liked in column A, all the ingredients used across them all in row 1 , the amounts of each ingredient that goes into a specific recipe in the cell that corresponds. This is more of a fun project that may be handy for me later, it's not a huge deal if I can't do what I want but it sure would make it easier if I can since this is becoming a pretty big table. What I would like to be able to do is click on a recipe name in column A, and have Excel or Google Sheets hide every cell that does not specify the ingredient needed and the amount needed for that recipe. Is this possible? Ideas how to accomplish this? This submitted by /u/Due-Editor3682 [link] [comments]