Updating seperate excel sheets based on one master file

[JurieDT]

Hi

I would like to create two seperate excel files based on specific data linked to a master file, the two seperate files needs to update as I update the master file.

The two seperate files will be based on sales for two seperate customers, so it needs to be unique to them and only their sales needs to reflect on the file .

 

[Muhammad_Usman]

To update two linked Excel sheets from a main master sheet based on the values in column D, you can use Excel's built-in features such as VLOOKUP or INDEX/MATCH, combined with Excel's Data Validation to create linked cells. Here's a step-by-step guide:

Let's assume you have three sheets: "Master," "Sheet1," and "Sheet2." You want to update data in "Sheet1" and "Sheet2" based on the values in column D of the "Master" sheet.

Step 1: Set Up Data Validation (Master Sheet)

1. In the "Master" sheet, go to the cell where you want to input data that will be linked to "Sheet1" and "Sheet2" (e.g., cell E2).
2. Select that cell and go to the "Data" tab.
3. Click "Data Validation" in the "Data Tools" group.
4. In the "Data Validation" dialog box, under the "Settings" tab:
   • Choose "List" from the "Allow" dropdown.
   • In the "Source" field, enter the values you want to be selectable from column D in the "Master" sheet. You can do this manually or by referring to the list in column D using a formula like =$D$2:$D$100 if your data goes from D2 to D100.
   • Click "OK" to set up the data validation.

Step 2: Create Linked Cells (Sheet1 and Sheet2)

1. In "Sheet1" and "Sheet2," find the cells where you want to display the linked data (e.g., cell A2).
2. In cell A2 of "Sheet1," enter the following formula:

excelCopy code
=VLOOKUP($E$2, 'Master'!$D:$D, [Column Number], FALSE)

Replace [Column Number] with the number corresponding to the column you want to retrieve data from in the "Master" sheet. For example, if you want data from column E, use 5 as the column number.

1. In cell A2 of "Sheet2," enter a similar formula but adjust the column number if needed.

excelCopy code
=VLOOKUP($E$2, 'Master'!$D:$D, [Column Number], FALSE)

Step 3: Test the Linking

Now, when you select a value in cell E2 of the "Master" sheet (using the data validation), cells A2 in "Sheet1" and "Sheet2" will automatically display the corresponding data from the "Master" sheet's column E.

Repeat the process for other cells or sheets as needed. The key is to use Excel's data validation to ensure you select valid values from column D in the "Master" sheet and then use VLOOKUP or a similar function in the linked cells to retrieve the data from the "Master" sheet based on the selected value.

 

[JurieDT]

To update two linked Excel sheets from a main master sheet based on the values in column D, you can use Excel's built-in features such as VLOOKUP or INDEX/MATCH, combined with Excel's Data Validation to create linked cells. Here's a step-by-step guide:

Let's assume you have three sheets: "Master," "Sheet1," and "Sheet2." You want to update data in "Sheet1" and "Sheet2" based on the values in column D of the "Master" sheet.

Step 1: Set Up Data Validation (Master Sheet)

1. In the "Master" sheet, go to the cell where you want to input data that will be linked to "Sheet1" and "Sheet2" (e.g., cell E2).
2. Select that cell and go to the "Data" tab.
3. Click "Data Validation" in the "Data Tools" group.
4. In the "Data Validation" dialog box, under the "Settings" tab:
   • Choose "List" from the "Allow" dropdown.
   • In the "Source" field, enter the values you want to be selectable from column D in the "Master" sheet. You can do this manually or by referring to the list in column D using a formula like =$D$2:$D$100 if your data goes from D2 to D100.
   • Click "OK" to set up the data validation.

Step 2: Create Linked Cells (Sheet1 and Sheet2)

1. In "Sheet1" and "Sheet2," find the cells where you want to display the linked data (e.g., cell A2).
2. In cell A2 of "Sheet1," enter the following formula:

excelCopy code
=VLOOKUP($E$2, 'Master'!$D:$D, [Column Number], FALSE)

Replace [Column Number] with the number corresponding to the column you want to retrieve data from in the "Master" sheet. For example, if you want data from column E, use 5 as the column number.

1. In cell A2 of "Sheet2," enter a similar formula but adjust the column number if needed.

excelCopy code
=VLOOKUP($E$2, 'Master'!$D:$D, [Column Number], FALSE)

Step 3: Test the Linking

Now, when you select a value in cell E2 of the "Master" sheet (using the data validation), cells A2 in "Sheet1" and "Sheet2" will automatically display the corresponding data from the "Master" sheet's column E.

Repeat the process for other cells or sheets as needed. The key is to use Excel's data validation to ensure you select valid values from column D in the "Master" sheet and then use VLOOKUP or a similar function in the linked cells to retrieve the data from the "Master" sheet based on the selected value.

Thank you so much for the detailed explanation, really appreciated!

 

[Muhammad_Usman]

Thank you so much for the detailed explanation, really appreciated!

Hope your query is resolved. Mark your post as solved. Thanks