How To Get Column A & B Data From Tab 1 To Auto-Fill Into Subsequent Tabs

%3CLINGO-SUB%20id%3D%22lingo-sub-1481471%22%20slang%3D%22en-US%22%3EHow%20To%20Get%20Column%20A%20%26amp%3B%20B%20Data%20From%20Tab%201%20To%20Auto-Fill%20Into%20Subsequent%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1481471%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I%20am%20using%20Excel%20as%20a%20Database%20for%20Project%20Management%20and%20I%20am%20trying%20to%20categorize%20my%20data%20over%20(6)%20tabs%3A%20Project%20Setup%2C%20Client%2FLocation%20Demographics%2C%20Project%20Status%2FStaffing%2C%20Financial%20Data%20and%20Post-Project%20Marketing%20Data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20Column%20A%20%26amp%3B%20B%20on%20all%20tabs%20to%20have%20the%20same%20data%2C%20without%20having%20to%20re-enter%20it%20on%20each%20tab.%20For%20example%2C%20on%20the%20first%20tab%20labeled%20%22Project%20Setup%22%20I%20have%20%22Project%20Number%22%20and%20%22Project%20Name%22%20in%20Column%20A%20%26amp%3B%20B%2C%20respectively%20(see%20attachment).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20imagine%20being%20able%20to%20type%20the%20new%20Project%20Number%20and%20Name%20into%20the%20Project%20Setup%20tab%2C%20then%20click%20over%20to%20any%20subsequent%20tab%20and%20see%20it%20auto-populated%20for%20me%2C%20so%20all%20I%20have%20to%20fill%20in%20is%20the%20remaining%20variable%20data%20columns.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20like%20to%20format%20the%20data%20as%20a%20table%20for%20sorting%2Ffiltering%20and%20creating%20pivot%20tables%20(I%20am%20also%20dipping%20into%20Power%20BI)%2C%20so%20I%20must%20be%20able%20to%20sort%20and%20filter%20on%20each%20tab%20without%20it%20breaking.%20Is%20this%20possible%2C%20or%20do%20I%20just%20need%20to%20accept%20the%20fact%20that%20I'll%20need%20to%20copy%2Fpaste%20the%20Project%20Number%20and%20Name%20manually%20on%20each%20tab%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1481471%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1481607%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Get%20Column%20A%20%26amp%3B%20B%20Data%20From%20Tab%201%20To%20Auto-Fill%20Into%20Subsequent%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1481607%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F706945%22%20target%3D%22_blank%22%3E%40TALemieux%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3ESee%20attached%20workbook.%20If%20you%20modify%20column%20A%20or%20B%2C%20in%26nbsp%3B%20%22Project%20Setup%22%2C%20it%20will%20reflect%20on%20the%20other%20sheets.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBen%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1482005%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Get%20Column%20A%20%26amp%3B%20B%20Data%20From%20Tab%201%20To%20Auto-Fill%20Into%20Subsequent%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1482005%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20Ben!%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703724%22%20target%3D%22_blank%22%3E%40Bennadeau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20quick%20reply!%20I%20entered%20the%20formula%20you%20provided%2C%20and%20it%20pulled%20the%20existing%20project%20numbers%20and%20names%20into%20the%20field%20perfectly.%20However%2C%20it%20doesn't%20seem%20to%20update%20the%20subsequent%20tabs%20when%20I%20add%20a%20new%20row%2Frecord%20to%20the%20table%20in%20the%20%22Project%20Setup%22%20tab%2C%20unless%20I%20manually%20expand%20the%20table%20in%20the%20subsequent%20tab.%20It%20also%20isn't%20allowing%20me%20to%20sort%20the%20tables%20on%20any%20tab%20other%20than%20%22Project%20Setup%22%20tab%20(aka%20Tab%201).%20On%20Tab%202%20%22Client%20%2B%20Location%20Demographics%22%20I%20entered%20some%20dummy%20data%20to%20the%20%22Community%22%20column%20to%20test%20sorting%2C%20but%20the%20data%20disconnected%20from%20its%20original%20row%20(see%20attachment).%20Column%20A%20%26amp%3B%20B%20stay%20rigid%20but%20the%20rest%20of%20the%20data%20from%20the%20table%20moves.%20This%20won't%20work%20because%20I'll%20have%20mismatched%20data%20if%20someone%20comes%20in%20and%20tries%20to%20sort%20it%20another%20way.%20Any%20other%20ideas%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1482047%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Get%20Column%20A%20%26amp%3B%20B%20Data%20From%20Tab%201%20To%20Auto-Fill%20Into%20Subsequent%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1482047%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F706945%22%20target%3D%22_blank%22%3E%40TALemieux%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYeah%2C%20I%20was%20afraid%20of%20that...%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20seems%20like%20you%20need%20an%20Access%20Database%20rather%20than%20Excel.%20Sorry%2C%20can't%20help%20you%20more%20with%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBen%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1482178%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Get%20Column%20A%20%26amp%3B%20B%20Data%20From%20Tab%201%20To%20Auto-Fill%20Into%20Subsequent%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1482178%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%20Ben%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F703724%22%20target%3D%22_blank%22%3E%40Bennadeau%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20briefly%20look%20at%20Access.%20It%20might%20be%20worth%20making%20the%20switch%20-%20thanks!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1482531%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Get%20Column%20A%20%26amp%3B%20B%20Data%20From%20Tab%201%20To%20Auto-Fill%20Into%20Subsequent%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1482531%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F706945%22%20target%3D%22_blank%22%3E%40TALemieux%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20attaching%20a%20file.%26nbsp%3B%20It%20does%20what%20you%20want%2C%20it%20is%20somewhat%20annoying%20to%20deal%20with%20if%20you%20accidentally%20click%20the%20A%20column%20on%20any%20of%20the%20sheets%20except%20Project%20Setup%20and%20are%20not%20trying%20to%20add%20all%20the%20projects%20from%20project%20setup.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1482739%22%20slang%3D%22en-US%22%3ERe%3A%20How%20To%20Get%20Column%20A%20%26amp%3B%20B%20Data%20From%20Tab%201%20To%20Auto-Fill%20Into%20Subsequent%20Tabs%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1482739%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fstackoverflow.com%2Fquestions%2F59251979%2Fwhen-a-row-is-added-to-one-sheet-automatically-add-a-row-to-another-sheet%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F59251979%2Fwhen-a-row-is-added-to-one-sheet-automatically-add-a-row-to-another-sheet%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESir%20please%20visit%20this%20link%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F706945%22%20target%3D%22_blank%22%3E%40TALemieux%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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?

6 Replies
Highlighted

Hi @TALemieux ,

See attached workbook. If you modify column A or B, in  "Project Setup", it will reflect on the other sheets.

 

Ben

 

Highlighted

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?

Highlighted

@TALemieux 

 

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.

 

Ben

Highlighted

Thanks, Ben @Bennadeau 

 

I did briefly look at Access. It might be worth making the switch - thanks!

Highlighted

@TALemieux 

 

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.