06-22-2020 10:07 AM
06-22-2020 10:07 AM
Hi, I am using Excel as a Database for Project Management and I am trying to categorize my data over (6) tabs: Project Setup, Client/Location Demographics, Project Status/Staffing, Financial Data and Post-Project Marketing Data.
I would like Column A & B on all tabs to have the same data, without having to re-enter it on each tab. For example, on the first tab labeled "Project Setup" I have "Project Number" and "Project Name" in Column A & B, respectively (see attachment).
I imagine being able to type the new Project Number and Name into the Project Setup tab, then click over to any subsequent tab and see it auto-populated for me, so all I have to fill in is the remaining variable data columns.
I like to format the data as a table for sorting/filtering and creating pivot tables (I am also dipping into Power BI), so I must be able to sort and filter on each tab without it breaking. Is this possible, or do I just need to accept the fact that I'll need to copy/paste the Project Number and Name manually on each tab?
06-22-2020 10:51 AM
Hi @TALemieux ,
See attached workbook. If you modify column A or B, in "Project Setup", it will reflect on the other sheets.
06-22-2020 01:28 PM
Hi, Ben! @Bennadeau
Thank you for your quick reply! I entered the formula you provided, and it pulled the existing project numbers and names into the field perfectly. However, it doesn't seem to update the subsequent tabs when I add a new row/record to the table in the "Project Setup" tab, unless I manually expand the table in the subsequent tab. It also isn't allowing me to sort the tables on any tab other than "Project Setup" tab (aka Tab 1). On Tab 2 "Client + Location Demographics" I entered some dummy data to the "Community" column to test sorting, but the data disconnected from its original row (see attachment). Column A & B stay rigid but the rest of the data from the table moves. This won't work because I'll have mismatched data if someone comes in and tries to sort it another way. Any other ideas?
06-22-2020 01:50 PM
Yeah, I was afraid of that...
It seems like you need an Access Database rather than Excel. Sorry, can't help you more with this.
06-22-2020 03:18 PM
Thanks, Ben @Bennadeau
I did briefly look at Access. It might be worth making the switch - thanks!
06-22-2020 06:54 PM - edited 06-22-2020 06:54 PM
I am attaching a file. It does what you want, it is somewhat annoying to deal with if you accidentally click the A column on any of the sheets except Project Setup and are not trying to add all the projects from project setup.
06-22-2020 10:30 PM