Creating a Excel Sheet that auto populates based on data entered on a previous sheet

[lewisjustind]

Hey all,

So I trying to figure something out for work. We use an excel workbook for our final reports. One of the sheets is called "snapshot" of the audit that we performed and it includes a location that we type in (but I am okay if we have to use a drop down to select). A second sheet that we use is our "Distribution List" which is who we are sending the report to.

Based on the location entered in the location section on the "Snapshot" sheet, we would like the "Distribution List" to auto populate based on the location (if it will auto format, that is a plus but I can work around that). See example below.

"Snapshot"
Location - ABC

"Distribution List"

John Doe - Director
John Doe 1 - Compliance Manager
John Doe 3 - Managing Director

vs.

"Snapshot"
Location - DEF

"Distribution List"
CC: Jane Doe - Director
Jane Doe 1 - Compliance Manager
Jane Doe 2 - Managing Director
Jane Doe 3 - Vice President

[RChad]

Please attach sample workbook with what you expect thanks.

[lewisjustind]

I added an attachment

[lewisjustind]

Please attach sample workbook with what you expect thanks.

I added the attachment. Sorry about that.

[RChad]

Your entered data has no connection to be located in the 2nd sheet. I suggest you enter data in the first sheet and manually enter that data in the 2nd sheet. Highlight the area in the 2nd sheet where you want the formulas that will accomplish your request.

Thanks

[lewisjustind]

I think I just uploaded what you were asking. Can you confirm?

[RChad]

The information that auto populates has to come from somewhere. For instance you have the data on a third sheet that has the matching cell from the first sheet. When you enter a value in the cell the sheet fills with a formula.

See the attached workbook. On the 2nd sheet if you fill in a name the formulas pull their information from the 1st sheet.

[lewisjustind]

The information that auto populates has to come from somewhere. For instance you have the data on a third sheet that has the matching cell from the first sheet. When you enter a value in the cell the sheet fills with a formula.

See the attached workbook. On the 2nd sheet if you fill in a name the formulas pull their information from the 1st sheet.

I really appreciate this. I am having a little difficulty understanding the formula. I am uploading a new document "scenario 2.0" to see if your solution could help me with what I am looking for.

Justin

[JeteMc]

Perhaps the following will help.
1. Populate C2:C8 using the column headers from Sheet 1
2. Populate B2:B8 using: =INDEX(Sheet1!B$2:H$8,MATCH('Audit Snapshot'!D$4,Sheet1!A$2:A$8,0),MATCH(C2,Sheet1!B$1:H$1,0))
Let us know if you have any questions.