How do I auto-populate information from one tab to another in sheets?

%3CLINGO-SUB%20id%3D%22lingo-sub-1095948%22%20slang%3D%22en-US%22%3EHow%20do%20I%20auto-populate%20information%20from%20one%20tab%20to%20another%20in%20sheets%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1095948%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EI%20have%20an%20excel%20sheet%20with%20several%20tabs.%20The%20first%20tab%20is%20the%20Master%20tab%20used%20to%20track%20all%20tabs.%20I%20would%20like%20all%20new%20tabs%20to%20auto-populate%20information%20into%20columns%20on%20the%20master%20tab.%20For%20example%2C%20I%20have%20multiple%20fields%20in%20my%20form%20I%20would%20like%20information%20from%20these%20fields%20to%20auto-populate%20into%20the%20master%20spreadsheet.%20I%20would%20like%20all%20new%20forms%2Ftabs%20to%20automatically%20update%20the%20master%20spreadsheet.%20I%20am%20currently%20entering%20everything%20manually.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1095948%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1103286%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20auto-populate%20information%20from%20one%20tab%20to%20another%20in%20sheets%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1103286%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F509930%22%20target%3D%22_blank%22%3E%40jhm0054%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3EAre%20you%20willing%20to%20manually%20enter%20the%20name%20of%20each%20new%20tab%20into%20a%20cell%20on%20the%20%22Master%22%20sheet%3F%3C%2FLI%3E%3CLI%3EIs%20all%20the%20information%20on%20each%20individual%20sheet%20organized%20in%20exactly%20the%20same%20way%3F%20Are%20they%20all%20laid%20out%20identically%3F%3C%2FLI%3E%3CLI%3EFinally%2C%20could%20you%20upload%20a%20sample%20of%20the%20actual%20master%20and%20backup%20sheets%3F%3C%2FLI%3E%3C%2FOL%3E%3CP%3EIf%20the%20answers%20to%20%231%20and%20%232%20are%20both%20%22Yes%2C%22%20then%20it%20should%20be%20fairly%20easy%20to%20write%20a%20formula%20or%20two%20that%20would%20retrieve%20the%20information%20from%20each%20new%20sheet%20and%20populate%20the%20master%20sheet%20with%20the%20data.%20But%20an%20actual%20example%20of%20your%20workbook%20(devoid%20of%20any%20confidential%20or%20personal%20information)%20would%20be%20most%20helpful.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1793787%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20auto-populate%20information%20from%20one%20tab%20to%20another%20in%20sheets%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1793787%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3BHad%20the%20same%20question%20as%20OP%2C%20but%20disappointed%20there%20were%20only%20follow%20questions%20versus%20and%20explanation%20of%20an%20actual%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20may%20case%2C%20YES%2C%20Everything%20would%20be%20the%20same%20from%20one%20Tab%20to%20the%20Next%20as%20it's%20the%20same%20form%3B%20and%20YES%2C%20I%20would%20manually%20rename%20each%20tab%20as%20they%20are%20Invoice%20%23's%20per%20each%20form.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEdit%3A%20The%20new%20Data%20would%20drop%20into%20the%20Master%20List%20of%20Data%20from%20the%20Tabs%2C%20to%20which%20I%20already%20have%20a%20chart%20set%20up%20to%20incorporate%20that%20data%20for%20my%20needs.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1795877%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20auto-populate%20information%20from%20one%20tab%20to%20another%20in%20sheets%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1795877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F294485%22%20target%3D%22_blank%22%3E%40CADDmanDH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EHad%20the%20same%20question%20as%20OP%2C%20but%20disappointed%20there%20were%20only%20follow%20questions%20versus%20and%20explanation%20of%20an%20actual%20answer.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou've%20never%20heard%20that%20sometimes%20you%20need%20to%20answer%20a%20question%20with%20a%20question%20(or%20two)%3F!%20As%20in%20sometimes%2C%20the%20situation%20needs%20a%20bit%20more%20definition.%20As%20in%20sometimes%20a%20quick%20%22answer%22%20is%20less%20helpful%20than%20a%20bit%20more%20back-and-forth%20first%20to%20make%20sure%20all%20parties%20are%20on%20the%20same%20page%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENot%20to%20be%20defensive%2C%20but%20I%20would%20point%20out%20your%20opening%20comment%20doesn't%20serve%20to%20endear%20me%20to%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EIn%20may%20case%2C%20YES%2C%20Everything%20would%20be%20the%20same%20from%20one%20Tab%20to%20the%20Next%20as%20it's%20the%20same%20form%3B%20and%20YES%2C%20I%20would%20manually%20rename%20each%20tab%20as%20they%20are%20Invoice%20%23's%20per%20each%20form.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EEdit%3A%20The%20new%20Data%20would%20drop%20into%20the%20Master%20List%20of%20Data%20from%20the%20Tabs%2C%20to%20which%20I%20already%20have%20a%20chart%20set%20up%20to%20incorporate%20that%20data%20for%20my%20needs.%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENevertheless%2C%20without%20any%20further%20ado%2C%20what%20I'm%20guessing%20(in%20the%20absence%20of%20further%26nbsp%3B%20definition%20of%20the%20situation)%20is%20that%20the%20%3CSTRONG%3EINDIRECT%3C%2FSTRONG%3E%20function%20would%20work%20in%20the%20OP's%20case%2C%20and%20maybe%20even%20in%20yours%3B%20you'll%20have%20to%20find%20that%20out.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20%3CSTRONG%3EINDIRECT%3C%2FSTRONG%3E%2C%20as%20I've%20used%20it%20in%20my%20own%20situation%20(which%20may%20or%20may%20not%20mirror%20yours)%2C%20I've%20entered%2C%20in%20a%20single%20column%20of%20the%20master%20or%20summary%20sheet%2C%20the%20word%2Ftext%20that%20serves%20also%20as%20the%20label%20on%20a%20tab%20from%20which%20I%20want%20to%20extract%20date.%20Then%2C%20across%20the%20top%20of%20the%20master%20sheet%2C%20I've%20entered%20into%20a%20singe%20row%20the%20specific%20cell%20references%20in%20those%20various%20sheets%20from%20which%20I%20want%20to%20extract%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20%3CSTRONG%3EINDIRECT%3C%2FSTRONG%3E%20does%20is%20construct%20a%20complete%20reference%20to%20another%20spot%20in%20the%20workbook.%20In%20this%20case%2C%20it%20is%20to%20a%20specific%20cell%20(or%20series%20of%20them)%20in%20another%20sheet%20in%20the%20same%20workbook%2C%20which%20I%20believe%20is%20what%20you're%20trying%20to%20accomplish.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20INDIRECT%20formula%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDIRECT(%24D6%26amp%3B%22!%22%26amp%3BC%241)%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3Ewhere%20%3CSTRONG%3E%24D6%3C%2FSTRONG%3E%20is%20the%20cell%20containing%20the%20sheet's%20name%20(the%20absolute%20reference%20to%20column%20D%20keeps%20that%20constant%20as%20it's%20copied%20across%20other%20columns%3B%20the%20relative%20reference%20to%20row%206%20allows%20it%20to%20change%20as%20it's%20copied%20down)%3C%2FLI%3E%3CLI%3EThe%20%3CSTRONG%3E%26amp%3B%22!%22%20%3C%2FSTRONG%3Edesignates%20the%20foregoing%20as%20a%20reference%20to%20a%20sheet%20in%20the%20same%20workbook%20(I'm%20assuming%20you%20know%20the%20concatenation%20symbol%20%3CSTRONG%3E%26amp%3B%3C%2FSTRONG%3E)%3C%2FLI%3E%3CLI%3EThe%26nbsp%3B%20final%20reference%20to%20%3CSTRONG%3EC%241%3C%2FSTRONG%3E%20is%20to%20the%20cell%20in%20which%20there%20is%20a%20cell%20address%20to%20the%20cell(s)%20in%20the%20various%20detail%20sheets%20containing%20the%20data%20elements%20you%20want%20in%20the%20summary%20sheet.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELet%20me%20know%20if%20that%20works.%20If%20not%2C%20come%20back%20with%20a%20description%20of%20what%20does%20happen%2C%20and%20maybe%20a%20sample%20of%20your%20actual%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2235320%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20auto-populate%20information%20from%20one%20tab%20to%20another%20in%20sheets%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2235320%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20I%20have%20a%20similar%20question.%20I%20have%20uploaded%20a%20spreadsheet%20that%20has%203%20tabs.%20Some%20of%20the%20information%20on%20Project%20Info%2FControl%20sheet%20I%20would%20like%20to%20repeat%20on%20the%20Budget%20Sheet%20and%20Proposal%20Sheet%20to%20reduce%20duplication%20of%20effort.%20I%20can%20get%20it%20to%20'behave'%20on%20the%20Budget%20Sheet%20(green%20circle%20in%20screen%20shot)%2C%20but%20not%20on%20all%20cells%20of%20the%20Proposal%20Sheet%20(red%20circles).%20It%20just%20shows%20the%20formula%20referencing%20the%20original%20cell%20instead%20of%20the%20data.%20I%20have%20tried%20inserting%20new%20rows%20on%20Project%20sheet%20and%20re-referencing%20to%20see%20if%20something%20is%20corrupt%20but%20I%20get%20the%20same%20problem.%20I%20saw%20your%20INDIRECT%20solution%20but%20I%20don't%20think%20my%20need%20is%20that%20complicated.%20Any%20thoughts%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have an excel sheet with several tabs. The first tab is the Master tab used to track all tabs. I would like all new tabs to auto-populate information into columns on the master tab. For example, I have multiple fields in my form I would like information from these fields to auto-populate into the master spreadsheet. I would like all new forms/tabs to automatically update the master spreadsheet. I am currently entering everything manually.

10 Replies

@jhm0054 

 

  1. Are you willing to manually enter the name of each new tab into a cell on the "Master" sheet?
  2. Is all the information on each individual sheet organized in exactly the same way? Are they all laid out identically?
  3. Finally, could you upload a sample of the actual master and backup sheets?

If the answers to #1 and #2 are both "Yes," then it should be fairly easy to write a formula or two that would retrieve the information from each new sheet and populate the master sheet with the data. But an actual example of your workbook (devoid of any confidential or personal information) would be most helpful.

 

@mathetes Had the same question as OP, but disappointed there were only follow questions versus and explanation of an actual answer.

 

In may case, YES, Everything would be the same from one Tab to the Next as it's the same form; and YES, I would manually rename each tab as they are Invoice #'s per each form.

 

Edit: The new Data would drop into the Master List of Data from the Tabs, to which I already have a chart set up to incorporate that data for my needs.

@CADDmanDH 

 

Had the same question as OP, but disappointed there were only follow questions versus and explanation of an actual answer.

 

You've never heard that sometimes you need to answer a question with a question (or two)?! As in sometimes, the situation needs a bit more definition. As in sometimes a quick "answer" is less helpful than a bit more back-and-forth first to make sure all parties are on the same page?

 

Not to be defensive, but I would point out your opening comment doesn't serve to endear me to you.

 

In may case, YES, Everything would be the same from one Tab to the Next as it's the same form; and YES, I would manually rename each tab as they are Invoice #'s per each form.

 

Edit: The new Data would drop into the Master List of Data from the Tabs, to which I already have a chart set up to incorporate that data for my needs.

 

Nevertheless, without any further ado, what I'm guessing (in the absence of further  definition of the situation) is that the INDIRECT function would work in the OP's case, and maybe even in yours; you'll have to find that out.

 

With INDIRECT, as I've used it in my own situation (which may or may not mirror yours), I've entered, in a single column of the master or summary sheet, the word/text that serves also as the label on a tab from which I want to extract date. Then, across the top of the master sheet, I've entered into a singe row the specific cell references in those various sheets from which I want to extract data.

 

What INDIRECT does is construct a complete reference to another spot in the workbook. In this case, it is to a specific cell (or series of them) in another sheet in the same workbook, which I believe is what you're trying to accomplish.

 

The INDIRECT formula looks like this:

=INDIRECT($D6&"!"&C$1)

  • where $D6 is the cell containing the sheet's name (the absolute reference to column D keeps that constant as it's copied across other columns; the relative reference to row 6 allows it to change as it's copied down)
  • The &"!" designates the foregoing as a reference to a sheet in the same workbook (I'm assuming you know the concatenation symbol &)
  • The  final reference to C$1 is to the cell in which there is a cell address to the cell(s) in the various detail sheets containing the data elements you want in the summary sheet.

 

Let me know if that works. If not, come back with a description of what does happen, and maybe a sample of your actual workbook.

 

@mathetes 

I think I have a similar question. I have uploaded a spreadsheet that has 3 tabs. Some of the information on Project Info/Control sheet I would like to repeat on the Budget Sheet and Proposal Sheet to reduce duplication of effort. I can get it to 'behave' on the Budget Sheet (green circle in screen shot), but not on all cells of the Proposal Sheet (red circles). It just shows the formula referencing the original cell instead of the data. I have tried inserting new rows on Project sheet and re-referencing to see if something is corrupt but I get the same problem. I saw your INDIRECT solution but I don't think my need is that complicated. Any thoughts would be appreciated.

Much more simple. You have those cells formatted as "Text"...change them to "General" and reenter the formula.

@mathetes 

Thank you for posting this solution, but I'm having difficulty getting it to work correctly. Using your example =INDIRECT($A5&"!"&C$8) where cell A5 on the current/active worksheet contains the tab/worksheet name on which the cell C8 contains the data I want to show up in this cell.  I received an error. Then, I tried to put the cell name from the "back up" worksheets in a row across the top of the master worksheet and instead of referring in the indirect formula to the cell on the other worksheet, I referred to the one on this one that had the address, which I think is what you described. I still received an error. Is it a problem that my tab names are numbers like this "21-001"?  It obviously works if I manually identify the worksheet in the formula (i.e., =21-001!$C$8). but then I'll have to change the formula for each cell across and down as I copy the formulas down the worksheet. Did I misunderstand the parameters in the formula? What am I doing incorrectly?

You're coming in to the middle of a conversation which already had TWO other people asking the question. It took me a while to realize you weren't just the prior person asking a follow-up question.

It's generally better to start a new thread. In this case, it would also be helpful if you posted a copy of the workbook you're working with.

@mathetes - my apologies for the confusion. I'll attach the document I'm working on - data listed is fictitious to see if it the functions in there so far are working. I'm trying to get the "high level" data from each project detail sheet to auto-populate into the summary log so that it can be reviewed "at a glance", but without having to edit the formula in each cell to change the worksheet name from which it is pulling. It seems like the "indirect" function should work, but I must have syntax incorrect or something.  Thanks in advance for your assistance.

@npopa1248 

 

The tab names were the problem, not the formula. I changed them to text and they worked. I used the people's initials which is most likely not what you want, but you can change it and it'll work.