Update Sheet depending on Main Sheet Data

surah79
surah79 used Ask the Experts™
on
Hi guys

I am trying to make a template in excel need help on that.
If I add data on the main sheet it will update the data on other sheet depends on the Team Name.
please find attached the sample template . The other sheets should be updated when we add or edit any data in the Main Sheet. It should updated according by team name in Column F in the main sheet.

thanks

surah
New-Template.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Excel VBA Developer
Top Expert 2014
Commented:
Without using VBA, I thought of a couple of methods.

1) Create sheets that display duplicates of the Main sheet data only if the team name is the same for that sheet and then filter out the blanks.  For example, the "SY" sheet would have formulas testing if the same row on Main had "SY" in column F, and display the related value on Main, otherwise leave the cell blank.  So on the SY sheet:
A2: =IF(Main!$F2="SY",Main!A2,"")
B2: =IF(Main!$F2="SY",Main!B2,"")
...and so on to column L

You'd want to copy these formulas well down the sheet.  Then you'd pick any column and filter out the blanks (team name would be ideal). The disadvantage is that you would have to refresh the filter on that sheet to pick up any changes on Main.

2) You could create an array function (in a hidden, helper column) that would return the largest position values of the team name for that sheet, then use INDEX functions to display in the table.

In this example (again for team SY), you'd add a helper column in M with this formula in cell M2 (using [Ctrl]+[Shift]+[Enter]):
{=LARGE(IF(Main!$F$2:$F$500="SY",ROW($F$2:$F$500)-1,0),ROW()-1)}

Then the table formulas would look like this:
A2: =IF($M2>0,INDEX(Main!A$2:A$500,$M2),"")
B2: =IF($M2>0,INDEX(Main!B$2:B$500,$M2),"")
...and so on to column L.

The advantage here is that no refreshing is required.  However, the items appear in reverse order than on the main sheet (bottom-most items appear first).

See the attached workbook for both types.

Regards,
-Glenn
EE-New-Template.xlsm
Please find attached your file, click on MACRO button to get the desired output.
New-Template.xlsm
Glenn RayExcel VBA Developer
Top Expert 2014

Commented:
^The VBA option is good, but you'll need to ensure that your Team values are EXACTLY the same as the sheet names they are to be copied to.  

In the original source file, "AE" is followed by two spaces (value = "AE  ") and the macro will halt because it doesn't match the sheet name.

The fix is easy:
1) Clean up your source data
2) Modify this line of the VBA code
DestinationSheet = ActiveCell.Value

Open in new window

with this
DestinationSheet = Trim(ActiveCell.Value)

Open in new window


Regards,
Glenn

Author

Commented:
thanks a lot for this.
You welcome Surah.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial