copying between master spread sheet and sub sheets

Question

Excel newbie. I have a nominal roll sorted by staff number with staff qualifications on, how would I get this to auto copy to an other sheet in the same work book by just adding the staff number to a2? There would be 31 data points to copy over per staff member when added to the sub sheet, these change regularly depending on availability and demand . staff numbers are always in column A, master sheet and all sub sheet are set up identically and in the same order. TIA

Answer 1

Hi Cooper1982,

Thank you for reaching out to this forum. I'm Utkarsh and as a fellow Microsoft user, I'm here and ready to help you today. I am a user like you, this is a user forum and not Microsoft directly.

Do not worry, I will help you sort this issue, rest assured that I will do my best to assist you.

I'd be glad to help you with that! Here's a combined approach that addresses the potential issues and incorporates the best aspects of both responses A and B, along with additional improvements:

Method 1: Using a Dynamic Array Formula (Excel 365 and Excel 2021 with Dynamic Arrays)

1. Create a sub sheet for each staff member: Right-click on any sheet tab and select "Insert Sheet" to create new sheets for each staff member. Name them consistently based on their staff numbers (e.g., "12345", "54321", etc.).
2. Enter the staff number in cell A2 of the master sheet: This will trigger the dynamic array formula to automatically populate the corresponding sub sheet.
3. In cell B2 of the master sheet, enter the following formula:

=FILTERXML("<t><s>" & TEXTJOIN("</s><s", FILTERXML("<s r="" "="" &="" a2="" &="" """=""><s>" & TRANSPOSE(FILTERXML("<t><s>" & TRANSPOSE(INDIRECT(ADDRESS(ROW(1:31),MATCH(A2:$A,MasterList!$A:$A,0)) & ":" & ADDRESS(ROW(1:31),COUNT(MasterList!$A:$A)))) & "</s></t>", "//s")), "//s")) & "</s></s></t>", "//s")

Explanation:

-FILTERXML: Extracts specific data from an XML structure. -TEXTJOIN: Joins strings with a specified delimiter. -TRANSPOSE: Converts rows to columns and vice versa. -INDIRECT: Converts a text string into a cell reference. -MASTERLIST!$A:$A: References the staff number column in the master sheet. -ADDRESS: Creates cell references based on row and column numbers. -FILTER: Filters the data based on the staff number match.

Method 2: Using INDEX/MATCH (Works in all Excel versions)

1. Set up a named range for the master sheet data:

-Select the data range on the master sheet (excluding headers). -Go to the "Formulas" tab and click "Define Name." -Enter a name for the range, such as "MasterData" (without quotes). -Click "OK."

2. In cell B2 of the sub sheet, enter the following formula:

=INDEX(MasterData, MATCH(A2, MasterList!$A:$A, 0), 2:32)

Explanation:

-INDEX: Retrieves a value from a range based on row and column numbers. -MATCH: Finds the staff number match in the master sheet. -MasterData: The named range for the master sheet data. -2:32: The range of columns to copy (adjust as needed).

Additional Tips:

-Adjust the formulas if your data is not in columns A and B or if the number of data points changes. -If you have many sub sheets, consider using macros or VBA for more automation. -Test the formulas thoroughly to ensure they work as expected. -Consider using conditional formatting to highlight any errors or inconsistencies in the data. -Save your workbook regularly to avoid losing your work.

I hope this information helps! If I can be of help with anything else, please let me know. I will be glad to answer any other questions that you might have.

Give back to the Community. Help the next person who has this issue by indicating if this reply solved your problem. Click Yes or No below.

Warm Regards, Utkarsh