Is there any way to make a relational DB like thing in excel?
Our take
For context, my work involves maintaining a lot of excel trackers. Basically we maintain these to track project details for a client like project deliverables, project codes (for employee clock-ins), project milestones, project assigned to PMs or not, etc - all in different excel files. This might sound like simple info, but we capture a lot of details related to project in all those files - like the main tracker will have bascially columns for capturing info from every section of the contract signed with client. The clock-in codes tracker will have its name, parent account ID, clock-in category, project ID, and a few other columns. Just adding one project's details to all trackers takes about 30-50 mins right now (depending on complexity and category of the project). However, maintaining multiple files leads to a lot of duplication effort - basically you add name of the project, project ID, PM name etc so many times. Anyway this can be changed? Like we add all data in one sheet and maybe pull it into different views for different purposes? I have done some research with gpt and on youtube, but they suggest going the power apps/ power BI way, but I am not too well-versed with those. And I was thinking if there is another solution that can be done in excel itself? Or if power BI is the way, then maybe can you guide me to a starting point for that? Thanks in advance.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Looking for project tracking ideasHi, As the title suggests I’m trying to create an Excel sheet that tracks the progress of various projects. Essentially, I was given a messy excel document to look after. I don’t have particularly advanced skills and nor do I want to spend too much time on this. So… One sheet lists projects as rows and they all have a reference code. There is a column designated for “action updates” where people overwrite progress each month. Other columns exist for project status, dates etc. My idea was to create another sheet which also lists the projects but acts as an action/change history log. I attempted to have a drop down through grouped cells that would act as a historic list of all changes and action updates around each project. The row with the corresponding project number would act as a “live view”, so using an X look up to display this data on the original sheet. Is there a better way to create something like this? There must be loads of ways to do something like this, but I just can’t think which way to do it!! Thank you for any help!!! submitted by /u/Formal_Disk_3760 [link] [comments]
- Central Data Sheet efficiencyHoping that someone with some in depth technical knowledge of Excel can help me out with a query. We issue Finance Tracker spreadsheets to projects in our organisation, and then link them back to a central sheet that we use for monitoring and reporting. There are maybe 40-50 cells in each spreadsheet that we need to pull in to our central sheet, but they’re spread around the tracker and so are generally linked individually, and we’re now approaching 120 trackers that need to be linked, so the central sheet is starting to get quite slow to load and update. My question is this: When doing cross workbook cell look ups, do arrays work more efficiently than individual cell references, or is there no noticeable difference in runtime? My idea is to, in each project’s workbook, create an “export” tab that contains all the information that needs to be pulled out, and link the central sheet by looking at that array, rather than each individual cell. submitted by /u/Jamespg614 [link] [comments]
- Request for improved methodI work in accounts payable for a company and took over some additional duties a few months ago. One of those duties is keeping a tracker/log of all bills that come in. A tracker in excel was handed over to me. While I’ve improved many things with this tracker so far, I’m looking to make a major change but unsure how to go about it. This tracker has 110k rows of data and has columns with data up to column “FZ”. New rows of data are added daily. Old rows are “archived” as soon as possible. I’m no excel pro, but can hold my own and have learned along the way. Issue: large dataset presents challenges with excel freezing and/or crashing Disclaimer: I cannot remove any rows or columns. Question: is there a better way to handle this data? Ie. tools in excel, using something other than excel, etc? submitted by /u/Visible-Question-786 [link] [comments]
- Multiple Tab Project Tracking DashboardI have an extremely large project with about 50+ same/similar program workflows. Each workflow is about a dozen different steps (i.e. Research, Revise Documents, Create work-orders, etc.) with some steps having multiple iterations (i.e. Revise Document 1, Revise Document 2) within a program/work flow. Each Step is organized by Worksheet tabs (i.e. a master document revision tab, a master work-order tab) with a column indicating what work flow/program its associated with, along with columns for preparer, reviewer, due date, status, etc.. What is the simplest way to slice and dice the data into a dashboard? Regular Pivot Table doesn't pull from multiple tabs, I can't seem to wrestle power pivot with many to many relationships, we've tried POWER Bi, and MS Lists with no luck. Ideally, I want a dashboard to filter by program/workflow, and see all steps, and iterations with owner, due date, etc. Looking for Suggestions. Bonus points if it can take a another slice by owner (across workflow, programs, iteration, prepare/reviewer) to essentially be an individual's personal action/assignment list. submitted by /u/couchfrog [link] [comments]